CODEWITHSUNDEEP
vbaexcel
user2446928
user2446928

Reputation: 37

VBA Excel find the first empty cell next to values

Could use some help on a project. My table looks something like this:

WEEK1 TEST1 VALUE
WEEK1 TEST1 [blank]
WEEK1 TEST2 [blank]
WEEK2 TEST1 VALUE
WEEK2 TEST2 [blank]
WEEK2 TEST1 VALUE

About 800 rows of these in different variations.

Now I need to find the first empty cell in C that has WEEK2 and TEST2 next to it. How would one go about doing this? Purpose is to enter a value in that cell that comes from a userform that defines A and B.

Upvotes: 1

Views: 217

Answers (3)

Vasily
Vasily

Reputation: 5782

Sub test()
    Dim x As Range, i&: i = [C:C].Find("*", , , , , xlPrevious).Row
    For Each x In Range("A1:A" & i)
        If UCase(x.Value2 & x.Offset(, 1).Value2 & _
                 x.Offset(, 2).Value2) = "WEEK2TEST2" Then
            MsgBox x.Offset(, 2).Address(0, 0):  Exit For
        End If
    Next x
End Sub

Upvotes: 0

Sixthsense
Sixthsense

Reputation: 1975

Sub FindMatch()
Dim sTxt1 As String, sTxt2 As String, vMatch As Variant

sTxt1 = """Week2"""
sTxt2 = """Test2"""

sformula = "MATCH(1,(A:A=" & sTxt1 & ")*(B:B=" & sTxt2 & "),0)"
vMatch = Evaluate(sformula)
If IsNumeric(vMatch) Then MsgBox Range("C" & vMatch).Address

End Sub

Added another condition to check whether Column-C is blank? Replace the below line of code to verify the column-C part also.

sformula = "MATCH(1,(A:A=" & sTxt1 & ")*(B:B=" & sTxt2 & ")*(C:C=""""),0)"

Upvotes: 1

Brendan Gooden
Brendan Gooden

Reputation: 1561

Try this code.

Sub CheckRows()
Dim RowNo As Long
RowNo = 1
    With ActiveWorkbook.Sheets(1)
        Do While .Cells(RowNo, 1).Value <> ""
                If UCase(.Cells(RowNo, 1).Value) = "WEEK2" And _
                UCase(.Cells(RowNo, 2).Value) = "TEST2" And _
                .Cells(RowNo, 3).Value = "" Then
                MsgBox "Found at Row Number " & RowNo
                Exit Sub
            Else
            RowNo = RowNo + 1
            End If
        Loop
    End With
End Sub

Upvotes: 0

Related Questions