Excel VBA - Range(Find().Adress).Row

Question

I have googled and struggled with this for hours now.

I have a Control workbook, that pulls data from a varied amount of other workbooks (the Control workbook also creates the other workbooks and saves the names and dir of said workbooks so that they can be called later)

This piece of code is the problem.

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Declare_Sheets

Dim SearchresultROW
Dim Searchresult As String
Dim complexrow As Integer
Dim CurrSheet As Worksheet
Dim Stype As String

Dim startROW As Integer
Dim endROW As Integer, SearchCOL As Integer, OffROW As Integer
Dim PDATArange As Range, CDATArange As Range
Dim Dateyear, Datemonth, datetest As String

Stype = WSRD.Range("B11")

'Find complex to work with
complexrow = WSSS.Range("F7")
WSSS.Activate
SearchresultROW = Range(Cells(7, 15), Cells(complexrow, 15).Find(Callsheet).Address).Row
Searchresult = WSSS.Cells(SearchresultROW, 15).Offset(0, 1)


Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

The below code is the problem extract

complexrow = WSSS.Range("F7")
WSSS.Activate
SearchresultROW = Range(Cells(7, 15), Cells(complexrow, 15).Find(Callsheet).Address).Row
Searchresult = WSSS.Cells(SearchresultROW, 15).Offset(0, 1)

1st problem I cant get the find() to work without activating worksheet - WSSS Declare_Sheets gets run at the start which declares WSSS, this works everywhere else in my code, but not with this find().

2nd problem The code below compiles and finishes, BUT - It does not return the correct data.

This code calls starts the macro

Cancel = True
Dim Calsheet As String
If Target.Column <> 1 Then Exit Sub
Calsheet = Target.Value
Call Call_Readings(Calsheet)
End Sub

There are currently 2 possibilities

I double click on Casper Tcomp 4. Callsheet = "Casper Tcomp 4" - Which is correct (target of the double click)

Complexrow = "9" - Which is correct (this will increment as new sheets are added)

SearchresultROW = "7" - This is wrong, it should be 8

I have tried adding LookAt:=xlWhole and LookIn:-xlValues, doesnt change a thing

Answer 1

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Declare_Sheets

Dim SearchresultROW
Dim Searchresult As String
Dim complexrow As Integer
Dim CurrSheet As Worksheet
Dim Stype As String
Dim FindResult As Range

Dim startROW As Integer
Dim endROW As Integer, SearchCOL As Integer, OffROW As Integer
Dim PDATArange As Range, CDATArange As Range
Dim Dateyear, Datemonth, datetest As String

Stype = WSRD.Range("B11")

'Find complex to work with
complexrow = WSSS.Range("F7")
On Error Resume Next 'next line will error if nothing is found
Set FindResult = WSSS.Range(WSSS.Cells(7, 15), WSSS.Cells(complexrow, 15)).Find(What:=Callsheet, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchByte:=False)
On Error GoTo 0 'always re-activate error reporting!

If Not FindResult Is Nothing Then  'check if find was successful
    SearchresultROW = FindResult.Row
    Searchresult = WSSS.Cells(SearchresultROW, 15).Offset(0, 1)
Else  'if nothing was found show message
    MsgBox "NO WB FOUND.", vbCritical
End If

This solved the problem, thanks for the assistance Pᴇʜ

Answer 2

Your code without .Activate would look something like below. Note that every Range, Cells, Rows or Columns object needs to be referenced with the correct Workbook/Worksheet:

complexrow = WSSS.Range("F7")

'try to find something
Dim FindResult As Range
On Error Resume Next 'next line will error if nothing is found
Set FindResult = WSSS.Cells(complexrow, 15).Find(What:=Callsheet, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchByte:=False)
On Error GoTo 0 'always re-activate error reporting!


If Not FindResult Is Nothing Then  'check if find was successful
    SearchresultROW = WSSS.Range(WSSS.Cells(7, 15), FindResult).Row
    Searchresult = WSSS.Cells(SearchresultROW, 15).Offset(0, 1)
Else  'if nothing was found show message
    MsgBox "nothing found.", vbCritical
End If

Note that if using the Range.Find method you need to check if something was found before you can use the result of Find. Otherwise it will throw an error. Also note that the documentation of Find says that …

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method.

So if you don't define them each time using Find it will use whatever was used last by either VBA or the user interface. Since you have no control about what was used last by the user interface I highly recomment to define them everytime using Find or you will get random results.

Also note that Callsheet is not defined in your code yet, so check that.