Finding specific text in a worksheet

[RastaBananaa]

Hi All,

I have a problem which I have been stuck on.

I need to create a pivot table but these tables would include different information depending on what the sheet contains. For example, if it contains journal usage, journals pivot created, if contains database usage, database pivot created and so on.

So I have created the 3 separate macros to run the separate reports but I want to have the one macro to find a specific text/value in the sheet and depending on the found value, would create the according sheet.

The problem I am having is how to create a code which follows the below logic:

Look for "text1" when found do something

if not found

Look for "text2" when found do something

if not found

look for "text3" when found do something

So I been using the:

Cells.Find(what:="HTML", LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=True).Activate

But this does not seem to work as it looks for the specific text, in this case HTML and if it does not find it, just throws an error.

I tried If cell.find..... then... but nothing. I used the find tool in Excel to see what code is needed for VBA but I can see I am going about it the wrong way.

Also, I would need the macro to look in the entire sheet and I cannot define a sheet range as it can change. I mean I can define it because maximum columns would be AB or something but I would like to avoid this.

I really hope someone can help me out. been stuck on this for months...

Thanks,
RastaBananaa

 

[Fluff]

Hi & welcome to the board.
How about something like

Sub chk()
   Dim Fnd As Range
   Dim FndTxt As Variant
   Dim i As Long
   
   FndTxt = Array("text1", "text2", "text3")
   For i = 0 To 2
      Set Fnd = Cells.Find(what:=FndTxt(i), lookIn:=xlFormulas, _
         LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
         MatchCase:=True)
      If Not Fnd Is Nothing Then
         Select Case i
            Case 0
               'do something
            Case 1
               ' do something else
            Case 2
               ' do something completely different
         End Select
      End If
   Next i
End Sub

 

[RastaBananaa]

Hi & welcome to the board.
How about something like

Sub chk()
   Dim Fnd As Range
   Dim FndTxt As Variant
   Dim i As Long
   
   FndTxt = Array("text1", "text2", "text3")
   For i = 0 To 2
      Set Fnd = Cells.Find(what:=FndTxt(i), lookIn:=xlFormulas, _
         LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
         MatchCase:=True)
      If Not Fnd Is Nothing Then
         Select Case i
            Case 0
               'do something
            Case 1
               ' do something else
            Case 2
               ' do something completely different
         End Select
      End If
   Next i
End Sub

OMG! Thank you so much!

I was stuck on this for months and to be honest I did not go about messaging someone as I was very busy with work. I googled for long time but unfortunately I could not find what I was after

This worked perfectly!!!!!

Now just need to figure out how to make Excel decide where the data is for pivot table instead of having it set, but I am sure I can figure this out!

Thanks,
RastaBananaa

 

[Fluff]

Glad to help & thanks for the feedback