CODEWITHSUNDEEP
vbaexcel
Cielo Salas
Cielo Salas

Reputation: 95

vba Find cell data through columns and if nothing then look at the next column

This is in connection to Find data in a column from a cell reference in another worksheet then copy some data to another worksheet I need to find a class name if they graduate by a certain month. The month to be used for the find reference is in another sheet. graduation months should be searched in column G first, and if search finds nothing then to search in the next column. This is what i have so far, but this searches one column only. i am not sure how to go about searching the other columns yet. search should stop the first instance that the value is found.

Sub Hierarchy()

Dim shOUT As Worksheet
Dim nfo As Worksheet
Dim Month As Range

Dim a As Long
Dim thisvalue As String

Set nfo = Worksheets("Info")
Set shOUT = Worksheets("Output")
Set Month = nfo.Range("A2")

With shOUT
        ' Loop through each row
        For a = 2 To 10
            thisvalue = .Cells(a, 7).Value
            If thisvalue Like Month.Value Then ' <-- check the names

            nfo.Range("A5").Value = .Cells(a, 2).Value
            nfo.Range("A6").Value = .Cells(1, 7).Value

            End If
        Next a
End With

End Sub

Upvotes: 1

Views: 302

Answers (1)

Steve Lovell
Steve Lovell

Reputation: 2564

I reckon an extra loop, plus some minor edits might suffice:

For b = 7 To 8
    For a = 2 To 10
        thisvalue = .Cells(a, b).Value
        If thisvalue Like Month.Value Then ' <-- check the names
            nfo.Range("A5").Value = .Cells(a, 2).Value
            nfo.Range("A6").Value = .Cells(1, b).Value
            Finished = True
            Exit For '<-- skip the rest, we're done
        End If
    Next a
    If Finished = True Then Exit For
Next b

It might need some tweaks (as I'm not too sure about the updates to A5 and A6), but the concept is sound.

I've added a couple of variables (b, and Finished) that you'll need to declare.

Upvotes: 1

Related Questions