|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Find last non empty cell (moving horizontally), then move one cell to the right
Hello,
I have a table in a word document, wdoc.tables(8) and I am trying to find either the last non empty cell and then move one cell to the right, or the first empty cell and select it for my macro to insert data. The table will be filled from left to right, not up&down so I would like to have it read the table by rows opposed to columns -columns 1 through 7 will be filled for row one before any columns in row 2 are filled |
#2
|
|||
|
|||
Use the .cells property of the table range.
|
#3
|
|||
|
|||
Could you be a little more informative? I am very new to vba!
There are 7 columns and 11 rows that I have in the table. the cell I want to select will be in a row partially filled with data, meaning columns 1,2 of that row will have text in them. I want to select either that last cell with text in it (in column 2) OR select the first blank cell of that row. How would i use the .cells property of the table range to do this? |
#4
|
|||
|
|||
Will you be selecting the row in the table before you run the macro or will you just expect the macro to process Table(8).
Providing more information goes both ways you know. Code:
Public Function find_last_empty_cell_in_range(this_range As Word.Range) As Long ' Searches through the cells in the table and returns the index of the first empty cell. ' The input parameter is a word range so the function is generic for a table ' or a selection within a table ' ' This function will work with non uniform tables ' Scanning a table using the range.cell(x,y) method will fail with non-uniforma tables ' ' We test if the length of the text in the cell is 2 or less characters to find an empty cell. ' This is because Cells contain hidden characters which mark the end of the cell ' and end of the text (I think) Dim my_cell As Word.Cell Dim my_index As Long For my_index = 1 To this_range.Cells.Count With this_range If Len(.Cells(my_index).Range.Text) <= 2 Then find_last_empty_cell_in_range = my_index Exit Function End If End With Next End Function Sub find_cell() Dim my_range As Word.Range Set my_range = ActiveDocument.Tables(8).Range Debug.Print find_last_empty_cell_in_range(my_range) End Sub |
#5
|
|||
|
|||
Code:
Sub CopyAndPaste() Dim myfile, wdApp As New Word.Application, wDoc As Word.Document 'select truck report file ChDrive "E:\" ChDir "E:\WG\TVAL\" myfile = Application.GetOpenFilename(, , "Browse for Document") Dim i As Integer 'searches for row with "avg" then selects column E(avg of temperature mean) of that row. i = Application.Match("Avg", Sheet1.Range("A1:A20"), 0) 'makes the file appear wdApp.Visible = True Set wDoc = wdApp.Documents.Open(myfile) With wDoc Dim my_cell As Word.Cell Dim my_index As Long For my_index = 1 To this_range.Cells.Count With this_range If Len(.Cells(my_index).Range.Text) <= 2 Then find_last_empty_cell_in_range = my_index Exit Sub End If End With Next End With Dim my_range As Word.Range Set my_range = ActiveDocument.Tables(8).Range Debug.Print find_last_empty_cell_in_range(my_range) End Sub Code:
For my_index = 1 To this_range.Cells.Count Code:
wDoc.Tables(8).Columns(3).Select I found this online Code:
Dim x As Long Dim crange As Range With Selection For x = 1 To .Cells.Count Set crange = .Cells(x).Range With crange If Len(.Text) <> 0 Then .Select Exit For End If End With Next x End With Selection.Cells(1).Select Selection.TypeText Text:=Range("e" & i) |
#6
|
|||
|
|||
Code:
With wDoc Dim ocolumn As Column Dim ocell As Cell For Each ocell In wDoc.Tables(8).Columns(3) If Len(ocell.Range) = 2 Then ocell.Select Selection.PasteSpecial End If Next ocell End With |
#7
|
|||
|
|||
That's not the way to use the code I posted.
I provided a function to find the first empty cell in a table which you seem not to know how to use. I provided an example of how to call the function, which you haven't understood. You are writing a macro in Word VBA and yet you also seem to be trying to get data from an excel worksheet without properly opening the Excel Workbook That's what I get from Code:
'searches for row with "avg" then selects column E(avg of temperature mean) of that row. i = Application.Match("Avg", Sheet1.Range("A1:A20"), 0) This code Code:
Dim myfile, wdApp As New Word.Application, In the line Code:
myfile = Application.GetOpenFilename(, , "Browse for Document") Have a look at this Code:
Sub CopyAndPaste() Dim my_file As String Dim my_average As Long Dim my_cell_index As Long Dim my_Doc As Word.Document my_file = get_filename my_average = get_excel_average(my_file, "A1:A20") ' The excel bit may be aa red herring ' Now open the word document. We can use the DOcuments method as we are already in word. ' Do you really wanto to open an excel file as w Word document? 'makes the file appear ' wdApp.Visible = True ' Set wDoc = wdApp.Documents.Open(myfile) Set my_Doc = Documents.Open("filename", True) 'Dim myfile, wdApp As New Word.Application, wDoc As Word.Document my_Cell_index = find_last_empty_cell_in_range(my_Doc.Tables(8).Range) ' put the average into the first empty cell we find in Table 8 my_Doc.Tables(8).Range.Cells(my_Cell_index).Range.Text = CStr(my_average) End Sub Public Function get_filename() As String Dim my_dialog As Office.FileDialog 'select truck report file ' ChDrive "E:\" ' ChDir "E:\WG\TVAL\" ' myfile = Application.GetOpenFilename(, , "Browse for Document") Set my_dialog = Application.FileDialog(msoFileDialogFilePicker) my_dialog.Filters.Clear If my_dialog.Show Then get_filename = my_dialog.SelectedItems(1) Else Exit Function End If End Function Public Function get_excel_average(this_filename As String, this_cell_range As String) As Long ' Its not clear what you want to do here ' so this code is an educated guess ' We are using the Create object approach because its unlikey you understand enough ' to have added the reference to excel to allow ' Dim my_XLApp as Excel.APplication' Dim my_XLApp As Object Dim my_XLWorkbook As Object Dim my_XLWorksheet As Object Set my_XLApp = CreateObject("Excel.Application") my_XLApp.Visible = True Set my_XLWorkbook = my_XLApp.Workbooks.Open(this_filename) Set my_XLWorksheet = my_XLWorkbook.worksheets(1) ' Dim i As Integer 'searches for row with "avg" then selects column E(avg of temperature mean) of that row. ' i = Application.Match("Avg", Sheet1.Range("A1:A20"), 0) ' I suspect that what you wqant is more complicated than this ' as it just gets the average of the cells in A1 to A20 get_excel_average = my_XLWorksheet.worksheetfunction.average(this_cell_range) End Function Public Function find_last_empty_cell_in_range(this_range As Word.Range) As Long ' Searches through the cells in the table and returns the index of the first empty cell. ' The input parameter is a word range so the function is generic for a table ' or a selection within a table ' ' This function will work with non uniform tables ' Scanning a table using the range.cell(x,y) method will fail with non-uniforma tables ' ' We test if the length of the text in the cell is 2 or less characters to find an empty cell. ' This is because Cells contain hidden characters which mark the end of the cell ' and end of the text (I think) Dim my_cell As Word.Cell Dim my_index As Long For my_index = 1 To this_range.Cells.Count With this_range If Len(.Cells(my_index).Range.Text) <= 2 Then find_last_empty_cell_in_range = my_index Exit Function End If End With Next End Function |
#8
|
|||
|
|||
slaycock I believe I have led you off path. I am writing an excel macro to work within word, not a word macro to work through excel.
The macro will be ran from excel, here is what it does. 1. user selects file to open 2. searches column A of excel to find cell with text "avg" in it and then matches column e with that row and selects the appropriate cell 3. copy the cell 4.open word document Now what I want to do is paste the copied cell into the first blank cell of column 3 in my table in the word document. Does this make sense? I am writing the macro from excel, not from word |
#9
|
|||
|
|||
I don't have any need to get involved in Excel VBA.
I AM UNUSUAL IN THAT I spend most of my time in sword VBA, hence my unfamiliarity with the code you are using. You need to move this thread to Excel programming and then give a lot more consideration to how you explain your situation. You can still look at my code and learn. There is hope though. I was once in the same place you are. So keep reading and learning. Good luck with your endeavour. |
#10
|
||||
|
||||
Cross-posted at: https://stackoverflow.com/questions/...-cell-in-a-row
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
extract time from a cell and move it to front of cell before text | ewso | Excel | 20 | 03-19-2017 11:34 AM |
Move the data at bottom cell to right side of the cell | kcyag91 | Excel | 1 | 01-28-2016 12:28 AM |
Data validation,force cell to be filed with number if respective cell is not empty | nicholes | Excel Programming | 0 | 08-01-2015 09:08 AM |
If id cell range is empty then should not allow to fill any other cell | ubns | Excel Programming | 2 | 04-12-2015 06:31 AM |
Move data from 1 cell to another cell | Catalin.B | Excel | 1 | 06-25-2011 12:51 PM |