.Find not working - returning the string I was searching for, not the location it found it in...

[SnyperBunny]

I am looking for a varietey of data, based on what files are opened to begin wtih.
The data (workbookA.sheet1.columnA) is saved to (set as the value of) a variable "resource_name" which should then search for it in the other sheet (workbookB.sheet1) to see if it even exists (in this case I want a true/false type of result from .find, or some other funtion... I'm attempting to use .find to do this and us an if-then-else statement).

Then, if it does NOT exist, it can discard the data (not do anything further) and then continue to the next iteration of the loop which should jump to the next row down (WorkbookA.sheet.1.columnA) with a new string value for "resource_name" and search for it in the other workbook (workbookB.sheet1) again.

If it DOES exist in workbookB.sheet1 however, then it must pull more data from WorkbookA.sheet1, and save string information to variables 'year', 'month', and 'generation'. Then in WorkbookC.sheet1, it must search for 'year' across row1, and save which column it is found is so that it can then start at that column and search row2 for 'month' - and then save that column as the final_col.

Finally it must search down columnA (still workbookC.sheet1) for "resource_name" and, if it DOES find it, use that row and then final_col (that it found the correct month on) to put the string contained in the "generation" variable into the cell determined by this row/column.

If it does NOT however, find the "resource_name" in ColA, it must look for the first (hightest) row with no data in colA (= "") and then input "resource_name" to colA as well as "generation" to the coordinates of this row and final_col (from searching for the month)

There are a grand total of 3 workbooks, and 3 worksheets that are applicable to this part of my code (bookA.sheet1, bookB.sheet1, bookC.sheet1).
Its is being saved as a macro-enabled-template (.xltm) if that makes any difference....

   Application.ScreenUpdating = False
   s = 2
 
[COLOR=#008000]  'do until it reaches the bottom or hits an empty cell
[/COLOR]  Do While s <= 2000 And Cells(s, 1) <> ""     
    
[COLOR=#008000]  'For s = 2 To 65536 Step 1 And Cells(s, 1) <> "" 'for top to absolute bottom, until the colA row s is equal to nothing
[/COLOR]              
       resource_name = Workbooks(StaMoPerf).Worksheets(1).Cells(s, 3) [COLOR=#008000]'pull a string from the cell in sheet StaMoPerf
[/COLOR]           
        [COLOR=#008000]'determine if the resource_name already exists on 'MonthlyPlanningEnergy' sheet for reference
[/COLOR]      find_it = rFound(resource_name, MonthlyPlanningEnergy, Worksheets(1), 2, 4, xlByColumns)
       
       If find_it Is Nothing Then [COLOR=#008000]'if it does not exist on monthly planning energy sheet, 'then do nothing (discard data/go to next iteration)

[/COLOR]          [COLOR=#008000]'do nothing
[/COLOR]
        Else
            month = Workbooks(StaMoPerf).Worksheets(1).Cells(s, 2).Value        [COLOR=#008000]'save data (string value) to a variable 'month'
[/COLOR]          year = Workbooks(StaMoPerf).Worksheets(1).Cells(s, 1).Value         [COLOR=#008000]'save data (string value) to a variable 'year'
[/COLOR]          generation = Workbooks(StaMoPerf).Worksheets(1).Cells(s, 5).Value   [COLOR=#008000]'save data (integer value) to a variable 'generation'
[/COLOR]          
           [COLOR=#008000]'search through Row 1 of thisworkbook to find the year, and save the column location
[/COLOR]          year_col.Column = rFound(year, ThisWorkbook, Worksheets(1), 1, 1, xlByRows)
            
           [COLOR=#008000]'starting at the column the year was found at, search for the month in Row 2, and save the column location where it was found
[/COLOR]          final_col.Column = rFound(month, ThisWorkbook, Worksheets(1), 2, year_col.Column, xlByRows)
            
            [COLOR=#008000]'look at the final sheet and see if the resource_name is already in column A somewhere (save Row location)
[/COLOR]          existing_row.Row = rFound(resource_name, ThisWorkbook, Worksheets(1), 1, 1, xlByColumns)
            
            If existing_row = False Then
                [COLOR=#008000]'look for first (top) blank row and write it in
                'I'll write this code later
[/COLOR]          Else
                [COLOR=#008000]'if the resource_name can be found in col A, then use that row # and the col # from finding the month to write
                'generation to the coordinates[/COLOR].
                ThisWorkbook.Worksheets(1).Cells(existing_row.Row, final_col.Column).Value = generation
            End If
        
        End If
        
        s = s + 1      [COLOR=#008000]'increment s to continue the while loop
[/COLOR]  'Next
    Loop
    
    Application.ScreenUpdating = True
    MsgBox ("Finished!")

Originally I was trying to do it with this rFound function I found online:

Function rFound(lost, book, sheet, row_start, col_start, search_direction_xlByRows_or_xlByColumns) As Range   
[COLOR=#008000]'Dim book As Workbook    
    'Dim sheet As Worksheet    
    'Dim col_start As Integer    
    'Dim row_start As Integer[/COLOR][COLOR=#008000]   
    'Dim rFound As Range    
    'Before I commented the Dim's out it was telling me that it was over dimentioned or something....[/COLOR]   

    On Error Resume Next 
    '[COLOR=#008000]I would like it to return a 'false'  value or otherwise be able to catch the error... but I don't know how....[/COLOR]      
    With book.sheet        
         Set rFound = .Columns(1).Find(What:=lost, After:=.Cells(row_start, col_start), LookIn:=xlValues, LookAt:= _        
         xlWhole, SearchOrder:=search_direction_xlByRows_or_xlByColumns, SearchDirection:=xlNext, MatchCase:=False _        
         , SearchFormat:=False)        
         '[COLOR=#008000]more error handling that I dont know how to modify....[/COLOR]   
         On Error GoTo 0        
        If Not rFound Is Nothing Then Application.Goto rFound, True     

    End With
End Function

However... I'm wondering if a simple in-line call if .Find would work just as well...
Thing is, I got .Find to work in a test book, and it just returned the value I was looking for and then found ("2020") it did not return the location of it.

Except now its not working and I have no idea why It was... and then I changed something (can't remember what) and not its not working... and no matter what I try to change back its giving me errors...

Sub test()
    
    Dim i As Range
    
    [B][COLOR=#daa520]Set i = Worksheets(1).Find("2020", Range("A1"), xlValues, xlWhole, xlByRows, xlNext).Row
     [/COLOR][/B][COLOR=#800000][Runtime error 438: Object doesn't support this porperty or method]
[/COLOR]
    MsgBox (i)
    
End Sub

I know there is a lot of text and code here to go through... but I am totally totally stumped and have NO idea how to get this finding thing working short of using VLOOKUP, which I was told takes AGES to get through large numbers of rows.... any help would be greatly appreciated and will be rewarded with yummy imaginary cookies :D

 

[JLGWhiz]

Since i, in this case, is declared as a Range, it requires a range object to search (Cells). It cannot be a range and have the final attribute in the search code defined as a row. Creates a compile conflict since row is normally an integer value. So, this modified version should work.

Code:
Sub test()
Dim i As Range
Set i = Worksheets(1).Cells.Find("2020", Range("A1"), xlValues, xlWhole, xlByRows, xlNext)
MsgBox i.Address
End Sub
Code:

 

[GRTorres]

You need to follow this format:

Sub Test()

Set i = Sheets("Sheetname").Range("Rangeofcells").Find("searchforvalue", LookIn:=xlValues)
MsgBox i.Row

End Sub

Change the "" pieces to your needs.

 

[SnyperBunny]

You people are GENIUSES!!!! Cookies all around!!!


Oh! and just a quick follow up regarding this.... If the search cannot find the string (because it doesnt exist on the page) what would it return? How would I catch this to use in a If-Then-Else statement?

 

[GRTorres]

Like this:

....
Set i = etc....
If Not i is Nothing Then
Msgbox i.Addres
End If
.....

 

[SnyperBunny]

Awesome! That worked perfectly!!! I now also have yet another follow-up question :P

That Range() before the .Find, does that mean that it ONLY searches for the string within that range? Or is that Range("A1") (for example) the row and column it should start searching at from where it continues? I tried to do this:

 Sub test()
    Dim alpha As Range
    Set alpha = rfind(2018, ThisWorkbook, Worksheets(1), 1, A, xlByRows)
    MsgBox (alpha.Column)
End Sub

Function rfind(searchee, book, sheet, row_start, col_start, search_direction)
    
    Dim i As Range
    
    [B][COLOR=#daa520]Set i = book.sheet.Cells.Find(searchee, Range(row_start, col_start), xlValues, xlWhole, search_direction, xlNext)
[/COLOR][/B][COLOR=#800000]  [Runtime error 1004: Method 'Range' of object '_Global' has failed][/COLOR]
    MsgBox (i.Row & i.Address)
    
End Function

Except its throwing an error - I detailed the error in the code box above.

 

[GRTorres]

You can use it multiple ways for instance:

down one column:

Set i = Sheets("Sheet1").Range("A1:A10").Find("searchvalue", LookIn:=xlvalues)

In multi-row or column:

Set i = Sheets("Sheet1").Range("A1:B10").Find(....

Or in a Loop with unknown rows

For x = 1 to Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Set i = Sheets("Sheet1").Range("A" & i).Find(....
Msgbox i.Address
next x

 

[SnyperBunny]

Ahhh!! that makes sense now!
and what about if I'm feeling lazy and dont want to type the whole line out every time - can I put it into its own function?

Sub test()
    Dim alpha As Range
    Set alpha = rfind(Jan, ThisWorkbook, Worksheets(1), 2, "AZ", xlByRows)
    'MsgBox (alpha.Column)
End Sub

Function rfind(searchee, book, sheet, row_start, col_start, search_direction)
    
    Dim i As Range
    
    [B][COLOR=#daa520]Set i = book.sheet.Cells.Find(searchee, Range(col_start, row_start), xlValues, xlWhole, search_direction, xlNext)
[/COLOR][/B][COLOR=#800000]  [Runtime error 438: Object does not suport this property or method][/COLOR]
    If Not i Is Nothing Then
         MsgBox (searchee & " is located here: " & i.Address)
    Else
         MsgBox ("oops, nothing found")
         i = "False"
    End If
   
    
End Function

I tried this ^ but its giving me an error....

Also, I'm not sure if this would error as it hasnt tried to compile it yet as it is stuck on the .find line, but within that function, can I say that if 'i' is not nothing, then it returns the address, however, can I also say that else i="False"? 'i' is dim'ed as a Range... so it can't be a string then can it? How else though would I return "False" to alpha?