Excel VBA - Search for string in worksheet

Question

I've got this Excel Workbook which I want to search for a specific string in Sheet1. If it finds it, enter a new value. But, the sheet is pretty huge, so I don't think it would be very efficient to loop through each cell. So, can I search through a range and find it that way?

This is what I've got so far:

Dim rngSearchRange as range

    With ThisWorkbook.Sheets(1)
      Set rngSearchRange = .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row)
    End With

The string I'm looking for is the first value in an array of three values. So, how can I search this range for that specific string, find the position and then enter a new value?

I was thinking something along this:

Dim rngFindRange as range
Set rngFindRange = rngSearchRange.Find(var(0), LookIn:=xlValues, lookat:=xlWhole)

The var(0) is the value in the array I'm after. But this line doesn't really give me any results. Am I way off here?

brettdj · Accepted Answer · 2011-10-03 10:26:53Z

3

something like this

Sub FindMe()
    Dim ws As Worksheet
    Dim rngSearchRange As Range
    Dim rngFindRange As Range
    Dim Var
    Dim elemVar
    Var = Array("apples", "oranges", "fruit")
    Set ws = ThisWorkbook.Sheets(1)
    Set rngSearchRange = ws.Range(ws.[a2], ws.Cells(Rows.Count, "A").End(xlUp))
    For Each elemVar In Var
        Set rngFindRange = rngSearchRange.Find(elemVar, LookIn:=xlValues, lookat:=xlWhole)
        If Not rngFindRange Is Nothing Then
            MsgBox elemVar & " found in " & rngFindRange.Address(0, 0)
        Else
            MsgBox elemVar & " not found in:"
        End If
    Next elemVar
End Sub

answered Oct 3, 2011 at 10:26

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Kenny Bones Over a year ago

This seems logical. But I still don't get any results from the rngFindRange. Even though I can add the rngSearchRange as a watch and then look at the Cell.Formula(1) and see that the exact value there is the very same as in var(0).

Kenny Bones Over a year ago

Ok, just tried your code alone now. And it works find :) So, I need to look through my own code to see why this doesn't work. Edit. Seems as though it doesn't find anything if the string has a space in it? Edit2: that works as well. Hmm, I'll get back to you soon

brettdj Over a year ago

What value are you searching for - an actual formula or the result of a formula? If the result of a formula have you checked for any whitespaces in the values?

Kenny Bones Over a year ago

Yes, there aren't any white spaces at all. Strange enough, I can reassign the value of Val(0) with the exact same string as it already has. And it can find that! I do get the original string using an ADODB connection to a separate workbook though. Then I pull the values I wan't into the array. But that shouldn't matter should it?

Kenny Bones Over a year ago

Just to clear up that last bit. It worked if I Dim a temp variable, assign that to the rs.GetRows. And then assign the real variable with t(0,0)

|

Collectives™ on Stack Overflow

Excel VBA - Search for string in worksheet

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related