Use FIND in VBA for complex search

**rpinxt** · 10-30-2012, 05:03 AM

Hi All,

Just a quick scenario:
Say in column C I have following list:
1LAA80
1LAA90
1LAB60
1LAC10
1LC010
1LC280
1LC380
1LC710
4AA110
4AA110

My first problem then is that the macro should only look for the cells which start with 1LC.
The second problem then would be that on the lines where he finds 1LC it should look in column F if there is an amount/value.
Third step then would be that if there is an amount it should go to column H of that line and there put some formula (or word, does not matter that I can do just need to get the macro there)

Since this is smaller part of the list you understand it has to be a variable "loop" of some sort which goes thru column C in search for codes which start with 1LC.

Hope I explained good enough and hope the experts in this forum can help me

Thanks in advance.

**mike7952** · 10-30-2012, 05:14 AM

This should do it

Sub FindLC()
 Dim FoundCell As Range
 Dim LastCell As Range
 Dim FirstAddr As String
 
    With Range("C:C")
        Set LastCell = .Cells(.Cells.Count)
    End With
    Set FoundCell = Range("C:C").Find(What:="1LC*", After:=LastCell)
    
    If Not FoundCell Is Nothing Then
        FirstAddr = FoundCell.Address
    End If
    
    Do Until FoundCell Is Nothing
        If FoundCell.Offset(, 3) <> "" Then
            FoundCell.Offset(, 5) = "Found value in column F"
        End If
        Set FoundCell = Range("C:C").FindNext(After:=FoundCell)
        If FoundCell.Address = FirstAddr Then
            Exit Do
        End If
    Loop
    
End Sub

**arlu1201** · 10-30-2012, 05:15 AM

Try this code - copy it into a standard module -

Option Explicit

Sub check_1LC()
Dim i As Long, lrow As Long

With Worksheets("Sheet1")
    lrow = .Range("C" & .Rows.Count).End(xlUp).Row
    For i = 2 To lrow
        If .Range("C" & i).Value Like "1LC*" Then
            If .Range("F" & i).Value <> "" Then
                .Range("H" & i).Value = "Correct"
            End If
        End If
    Next i
End With
End Sub

**rpinxt** · 10-30-2012, 05:22 AM

Thanks Mike!
Had to do a small correction as I had not mentioned to you that column F would not be empty but 0 (so <> 0 instead of <>"").
But works great and its a big help

Arlette also a big thanks. Your method (with same small adjustment) also worked like a charme