.Find function

[mfizz]

I am trying got use .find to search for something that has two elements



With ActiveSheet.Range("B:B")
Set C = .Find(What:="bc", LookIn:=xlValues, LookAt:=xlPart)

I want it to be able to identify a cell that has for example this as its contents.

"bc abc total"

so when i set a search of "bc" and "Total" , this should pop up

 

[Rick Rothstein]

I am trying got use .find to search for something that has two elements

With ActiveSheet.Range("B:B")
Set C = .Find(What:="bc", LookIn:=xlValues, LookAt:=xlPart)

I want it to be able to identify a cell that has for example this as its contents.

"bc abc total"

so when i set a search of "bc" and "Total" , this should pop up

If you know "bc" will always be before "total", you can do this...

With ActiveSheet.Range("B:B")
    Set C = .Find(What:="bc*total", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)

otherwise this should work...

With ActiveSheet.Range("B:B")
Set C = .Find(What:="bc*total", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If C Is Nothing Then Set C = .Find(What:="total*bc", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)

 

[mfizz]

smashing its like you read my mind BC will always be before total. Thank you it worked

 

[mfizz]

can i nest the find function inside a for loop? I want the for loop to create and store variables that it should find from the find function.

I'll show you my code.

With ActiveSheet.Range("B:B")   Set C = .Find(What:="z02*total", LookIn:=xlValues, LookAt:=xlPart)
    
        
    If Not C Is Nothing Then
        firstAddress = C.Address
        Do
        C.Activate
            z2c = z2c + 1
            z2t = z2t + C.Offset(0, 10).Value
                        
            Set C = .FindNext(C)
        Loop While Not C Is Nothing And C.Address <> firstAddress
    End If
End With

the changing variable would be z variable. i.e.

z01t
z02t
z03t
z04t
z05t

 

[Rick Rothstein]

can i nest the find function inside a for loop? I want the for loop to create and store variables that it should find from the find function.

I'll show you my code.

With ActiveSheet.Range("B:B")   Set C = .Find(What:="z02*total", LookIn:=xlValues, LookAt:=xlPart)
    
        
    If Not C Is Nothing Then
        firstAddress = C.Address
        Do
        C.Activate
            z2c = z2c + 1
            z2t = z2t + C.Offset(0, 10).Value
                        
            Set C = .FindNext(C)
        Loop While Not C Is Nothing And C.Address <> firstAddress
    End If
End With

the changing variable would be z variable. i.e.

z01t
z02t
z03t
z04t
z05t

Your question is not clear. I do not see what you want to change... you have z02 (no "t" after it) in the What argument to the Find method and z2t (no "0" after the "z") inside the loop, but no "z02t". What exactly did you want to change?

 

[mfizz]

sorry i should have been clearer

Sub counttotal()


Dim i As Long, cz0 As Long, tz0 As Long


With ActiveSheet.Range("B:B")
   
   Set C = .Find(What:="z02*total", LookIn:=xlValues, LookAt:=xlPart)
 
    If Not C Is Nothing Then
        firstAddress = C.Address
        Do
            z02c = z02c + 1
            z02t = z02t + C.Offset(0, 10).Value
                      
            Set C = .FindNext(C)
        Loop While Not C Is Nothing And C.Address <> firstAddress
    End If
End With

I want to create a loop which would cycle from 1 to 9 so it stores the sum of the cells and the count of a cell as a variable to be inserted into a new workbook later on.

so i want it to automatically cycle through so at the end of it it searches and finds "z02*total", "z03*total", "z04*total", "z05*total" etc. and stores them as z02c, z02t, z03c, z03t, z04c, z04t.

The variable name can be changed but it must have some sort of identifier on it that it is a count or a total.

Thank You

 

[RoryA]

Untested:

Sub counttotal()
   Dim n                           As Long
   Dim cz(1 To 9)                  As Long
   Dim tz(1 To 9)                  As Long
   Dim C                           As Range
   Dim firstAddress As String

   For n = 1 To 9
      firstAddress = ""
      With ActiveSheet.Range("B:B")

         Set C = .Find(What:="z0" & n & "*total", LookIn:=xlValues, LookAt:=xlPart)

         If Not C Is Nothing Then
            firstAddress = C.Address
            Do
               cz(n) = cz(n) + 1
               tz(n) = tz(n) + C.Offset(0, 10).Value

               Set C = .FindNext(C)
            Loop While C.Address <> firstAddress
         End If
      End With
   Next n
End Sub

 

[mfizz]

you are wonderful.

quick question why cant i output its contents to a cell. which is in a workbook that is opened AFTER the variables are stored.

i am using

Range("C8").Value = cz2

the code doesn't give me an error but it doesn't put anything in the cell either. msgbox shows that the variable exists prior to the workbook opening and blank after the new workbook opens

 

[RoryA]

Two possibilities:
1. cz2 is not a variable in that code. It would be cz(2)
2. The variables are declared within the find routine and so they lose their values when the routine ends. If you open the workbook in a separate routine (or manually), again, the variables have no value.

 

[mfizz]

my mistake it works. Thanks.