Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-03-2018, 06:43 AM
klutch klutch is offline Find last non empty cell (moving horizontally), then move one cell to the right Windows 7 64bit Find last non empty cell (moving horizontally), then move one cell to the right Office 2016
Advanced Beginner
Find last non empty cell (moving horizontally), then move one cell to the right
 
Join Date: Jun 2018
Posts: 31
klutch is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 07-03-2018, 11:07 AM
slaycock slaycock is offline Find last non empty cell (moving horizontally), then move one cell to the right Windows 7 64bit Find last non empty cell (moving horizontally), then move one cell to the right Office 2016
Expert
 
Join Date: Sep 2013
Posts: 256
slaycock is on a distinguished road
Default

Use the .cells property of the table range.
Reply With Quote
  #3  
Old 07-05-2018, 06:06 AM
klutch klutch is offline Find last non empty cell (moving horizontally), then move one cell to the right Windows 7 64bit Find last non empty cell (moving horizontally), then move one cell to the right Office 2016
Advanced Beginner
Find last non empty cell (moving horizontally), then move one cell to the right
 
Join Date: Jun 2018
Posts: 31
klutch is on a distinguished road
Default

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?
Reply With Quote
  #4  
Old 07-05-2018, 06:47 AM
slaycock slaycock is offline Find last non empty cell (moving horizontally), then move one cell to the right Windows 7 64bit Find last non empty cell (moving horizontally), then move one cell to the right Office 2016
Expert
 
Join Date: Sep 2013
Posts: 256
slaycock is on a distinguished road
Default

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
Reply With Quote
  #5  
Old 07-05-2018, 07:13 AM
klutch klutch is offline Find last non empty cell (moving horizontally), then move one cell to the right Windows 7 64bit Find last non empty cell (moving horizontally), then move one cell to the right Office 2016
Advanced Beginner
Find last non empty cell (moving horizontally), then move one cell to the right
 
Join Date: Jun 2018
Posts: 31
klutch is on a distinguished road
Default

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
This is how I added that into my code and got an error on the line
Code:
 For my_index = 1 To this_range.Cells.Count
I have found a way to select the column that I want to go to with
Code:
wDoc.Tables(8).Columns(3).Select
but how do I select the first empty cell?
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)
but it gives me error as well and only selects the entire column opposed to the first empty cell
Reply With Quote
  #6  
Old 07-05-2018, 09:09 AM
klutch klutch is offline Find last non empty cell (moving horizontally), then move one cell to the right Windows 7 64bit Find last non empty cell (moving horizontally), then move one cell to the right Office 2016
Advanced Beginner
Find last non empty cell (moving horizontally), then move one cell to the right
 
Join Date: Jun 2018
Posts: 31
klutch is on a distinguished road
Default

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
I am looking for something like this, but this does not have the right object property
Reply With Quote
  #7  
Old 07-05-2018, 09:56 AM
slaycock slaycock is offline Find last non empty cell (moving horizontally), then move one cell to the right Windows 7 64bit Find last non empty cell (moving horizontally), then move one cell to the right Office 2016
Expert
 
Join Date: Sep 2013
Posts: 256
slaycock is on a distinguished road
Default

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,
Declares myFile as a VARIANT variable and wdApp as a as Word.Applocation object. Is that what you intended.

In the line

Code:
myfile = Application.GetOpenFilename(, , "Browse for Document")
I don't know where 'GetOpenFilename' comes from as its not a Method of the Word.Application object.

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
Reply With Quote
  #8  
Old 07-05-2018, 10:14 AM
klutch klutch is offline Find last non empty cell (moving horizontally), then move one cell to the right Windows 7 64bit Find last non empty cell (moving horizontally), then move one cell to the right Office 2016
Advanced Beginner
Find last non empty cell (moving horizontally), then move one cell to the right
 
Join Date: Jun 2018
Posts: 31
klutch is on a distinguished road
Default

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
Reply With Quote
  #9  
Old 07-05-2018, 04:22 PM
slaycock slaycock is offline Find last non empty cell (moving horizontally), then move one cell to the right Windows 7 64bit Find last non empty cell (moving horizontally), then move one cell to the right Office 2016
Expert
 
Join Date: Sep 2013
Posts: 256
slaycock is on a distinguished road
Default

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.
Reply With Quote
  #10  
Old 07-09-2018, 06:16 PM
macropod's Avatar
macropod macropod is online now Find last non empty cell (moving horizontally), then move one cell to the right Windows 7 64bit Find last non empty cell (moving horizontally), then move one cell to the right Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,597
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
Reply

Thread Tools
Display Modes


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
Find last non empty cell (moving horizontally), then move one cell to the right 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

Other Forums: Access Forums

All times are GMT -7. The time now is 08:21 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft