Finding and updating cells in an Excel worksheet

[GraeagleBill]

Just starting with a new module to work with an Excel worksheet. The following code fails since the "Match" function IS NOT native to Access VBA. Does anyone know what the statement should be to replace the erroneous "Match" attempt seen in the code?

Option Compare Database
Option Explicit
Public Sub UpdateExcelPC()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' Open the PerCapita test file and find the string "Luckey" in
' column "L".
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*

Dim xlsApp As Object
Set xlsApp = CreateObject("Excel.Application")

xlsApp.Visible = True

xlsApp.workbooks.Open "c:\eRep\PerCapTestFile.xlsx", True, False
'MsgBox MATCH("Luckey", "L:L", 0) 'Find Luckey in column "L"

xlsApp.workbooks.Close
xlsApp.Quit
Set xlsApp = Nothing

End Sub

[ranman256]

you may have to use: xlsapp.Columns.Find

[SodaPop]

You could run through the column as a recordset starting at row 1 and count each row as it looks. The old I = I +1 .

Your if = if your value and the column value are not equal keep looking else mycell = "L"& i

From there you can pass or handle mycell however you would like.

Xlsapp.Cell.find might work too.

Sent from my SM-G950U using Tapatalk

[ssanfu]

The following code fails since the "Match" function IS NOT native to Access VBA.

Both Access and Excel have the "Quit" command, but you specifiec the Excel command by using "xlsApp.Quit" instead of "Quit".

"workbooks.Open" is also NOT a native Access command, yet you used it to open the workbook.
I have used Access to automate Excel and never had any problems with Excel commands (well, a few but I had the syntax wrong).
Did you try (I did not):

MsgBox xlsApp.MATCH("Luckey", "L:L", 0) 'Find Luckey in column "L"

or

Dim tmp as Integer

tmp = xlsApp.MATCH("Luckey", "L:L", 0) 'Find Luckey in column "L"
MsgBox tmp

??

[GraeagleBill]

The code below executes okay but fails to find the text "Baldwin" in column "L". SodaPop suggested looping through the rows, but my understanding of the "Match" function is that the loop is implied?

Option Compare Database
Option Explicit

Public Sub UpdateExcelPC()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'NOTE: THIS CODE IS PURELY EDUCATIONAL TESTING
'
'Open the PerCapita test file and find the string "Baldwin" in
' column "L".
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim MyRow As Variant
Dim xlsApp As Object
Set xlsApp = CreateObject("Excel.Application")

xlsApp.Visible = True

xlsApp.workbooks.Open "c:\eRep\PerCapTestFile.xlsx", True, False

MyRow = xlsApp.MATCH("Baldwin", "L:L", 0) 'Find Baldwin in column "L"
MsgBox IsError(MyRow)

xlsApp.workbooks.Close
xlsApp.Quit
Set xlsApp = Nothing

End Sub

Clearly the text "Baldwin" is in row 2 of Column L:

[ssanfu]

You **really** warped my brain on this one! I haven't delved so deep into Excel in years.

The trick (syntax) is to use "WorksheetFunction.MATCH".
The Match function is a worksheet function, meaning that it is used in worksheets, but is (apparently) not available in/to VBA.
Also the range argument that you used is invalid - cannot use L:L.

I added error handling to try and troubleshoot the errors. You might need a more detailed error handler....

Code:

Public Sub UpdateExcelPC()
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    'NOTE: THIS CODE IS PURELY EDUCATIONAL TESTING
    '
    'Open the PerCapita test file and find the string "Baldwin" in
    ' column "L".
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    Dim MyRow As Variant
    Dim xlsApp As Object
    Dim errMsg As String
    Dim SearchTerm As String

    Set xlsApp = CreateObject("Excel.Application")

    On Error GoTo HandleErr

    xlsApp.Visible = True
    xlsApp.workbooks.Open "c:\eRep\PerCapTestFile.xlsx", True, False

    SearchTerm = "Baldwin"
    '    SearchTerm = "jones"
    '    SearchTerm = "amy"    ' i used amy to create an error

    MyRow = xlsApp.WorksheetFunction.Match(SearchTerm, xlsApp.Range("L:L"), 0)    'Find Baldwin in column "L"
    MsgBox MyRow

HandleErr_exit:
    'clean up
    xlsApp.workbooks.Close
    xlsApp.Quit
    Set xlsApp = Nothing
    Exit Sub

HandleErr:
    If Err.Number <> 0 Then
        Select Case Err.Number
            Case 1004  'Excel error when Match term is not found
                MsgBox "No Match for " & SearchTerm
            Case Else
                errMsg = "Error number: " & Str(Err.Number) & vbNewLine & _
                         "Source: " & Err.Source & vbNewLine & _
                         "Description: " & Err.Description
                Debug.Print errMsg
        End Select
        Err.Clear
    End If

    Resume HandleErr_exit

End Sub

[GraeagleBill]

I don't know where you're located Steve but I owe you a lunch!

The following two statements were updated in the originally posted code: (The name "Luckey" was found in row 22 of Column "L")

MyRow = xlsApp.WorksheetFunction.Match("Luckey", xlsApp.Range("L:L"), 0)
If Not IsError(MyRow) Then MsgBox MyRow

My next challenge is to set values in a couple of cells in the obtained row. I will soon discover if I know how to do that.

[ssanfu]

My location is at the right of my post.

If Not IsError(MyRow) Then MsgBox MyRow

If you changed the code to this (above), you will have errors. Try searching for a name that is not in column L. Even with "MyRow" declared as a variant, a run time error occurs and the IsError() function is skipped - hence the error handler code.

Change "Luckey" to "Red_Banana". What happens??
I used "SearchTerm = "Baldwin"" so I could specify the search term.... Maybe you don't need to know, but.....

[GraeagleBill]

Ancharage! Then you must know Walt Bridges?

I understood the added code to trap the 1004 errors with the "not found" condition, I just wanted to see if the returned row was correct. You can be sure the OnError code will be included as I move forward with this brain twister that was recently laid at my feet. And yes, searching for "Red Banana" produced the "desired" 1004 error.

Thanks,
Bill
(PS) At this juncture, I'm going to mark the thread solved

[GraeagleBill]

It might be of general interest to view Post #21 in the Excel Forum where Alan offers an extensive discussion about Application.Match versus WorksheetFunction.Match as it relates to how errors are handled.

https://www.excelforum.com/excel-pro...ext-color.html

Cheers,
Bill

[ssanfu]

I looked at the link you provided, but the code was unavailable.
I modified the code and took out the error handling for testing (I would still have the EH).

Public Sub UpdateExce1PC()
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    'NOTE: THIS CODE IS PURELY EDUCATIONAL TESTING
    '
    'Open the PerCapita test file and find the string "Baldwin" in
    ' column "L".
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    Dim MyRow As Variant
    Dim xlsApp As Object
    Dim errMsg As String
    Dim SearchTerm As String

    '    SearchTerm = "jones"
    SearchTerm = "Baldwin"
    '    SearchTerm = "amy"

    Set xlsApp = CreateObject("Excel.Application")

    With xlsApp
        .Visible = True

        .Workbooks.Open "c:\eRep\PerCapTestFile.xlsx", True, False

        MyRow = .Application.Match(SearchTerm, xlsApp.Range("L:L"), 0) 
        If IsError(MyRow) Then
            MsgBox IsError(MyRow) & " - " & CStr(MyRow)
            ' apparently Excel error "Error 2042" is the error "#N/A"
        Else
            MsgBox MyRow
        End If
    End With

    xlsApp.Workbooks.Close
    xlsApp.Quit
    Set xlsApp = Nothing
    Exit Sub

End Sub

[GraeagleBill]

Hey Steve,
I don't know why the code would be unavailable. I do know that for some reason the webpage seemed corrupted in that the "Thread Tools" drop-down and containing banner were missing so that I couldn't mark the thread as [SOLVED]. The moderator changed the thread title and perhaps that got things a bit out of sync?

I think in the implementation of the find function I'll create that I'll use the Application.Match as suggested in the Excel Forum. However, as with most of my code I'll have an error handler to catch any other foul balls that are thrown. At the moment, I'm envisioning the function to return a variant, either a row number or Null. "(AppObjName As Object, strSearchFor As String, StrColToSearch As String) as Variant" where the Application object is required to be already Set and the Excel sheet already Activated.

Bill

[ssanfu]

Hey Steve,
I don't know why the code would be unavailable.

Just me. I didn't want to register (on **another** site) as it is an Excel site and I rarely program in Excel anymore.


In Post #11 code, I replaced "WorksheetFunction" with "Application". Note that you still need "xlsapp" to specify WHICH application to use.


Good luck with your project.......