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

SnyperBunny

New Member
Joined
Jan 21, 2013
Messages
23
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....
Code:
   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:
Code:
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...

Code:
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
 
Last edited:

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

Active Member
Joined
Nov 19, 2012
Messages
344
You need to follow this format:

Code:
Sub Test()

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

End Sub

Change the "" pieces to your needs.
 

SnyperBunny

New Member
Joined
Jan 21, 2013
Messages
23
You people are GENIUSES!!!! Cookies all around!!! :biggrin:


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

Active Member
Joined
Nov 19, 2012
Messages
344

ADVERTISEMENT

Like this:
Code:
....
Set i = etc....
If Not i is Nothing Then
Msgbox i.Addres
End If
.....
 

SnyperBunny

New Member
Joined
Jan 21, 2013
Messages
23
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:

Code:
 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.
 
Last edited:

GRTorres

Active Member
Joined
Nov 19, 2012
Messages
344
You can use it multiple ways for instance:

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

In multi-row or column:
Code:
Set i = Sheets("Sheet1").Range("A1:B10").Find(....

Or in a Loop with unknown rows
Code:
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

New Member
Joined
Jan 21, 2013
Messages
23
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?

Code:
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,737
Messages
5,597,826
Members
414,180
Latest member
Sir Khaya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top