CODEWITHSUNDEEP
excelvbaloopslookupuserform
Raghav Chamadiya
Raghav Chamadiya

Reputation: 240

Loop through a column till last non empty row to find matching data through a userform VBA

I am stuck on this small problem. This is how my Excel data looks like:
enter image description here

I have a UserForm which asks for the "Hue", "Munsell Value" and "Munsell Chroma" values, and it should give the value in column D (the color name) as output in a Message Box. I am trying to loop until the last non-empty row and check A, B, C columns for matching data and then give out the corresponding 4th column as output.

This is my VBA code:

Sub Result()

Dim r As Range
Dim LastRow As Long

SheetName = frmForm.cmbSheet
Hue = frmForm.txtHue
Value2 = frmForm.txtValue
Chroma = frmForm.txtChroma

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(SheetName)

LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
    For Each r In ws.Range("A2:A" & LastRow)
        If r = Hue & r.Offset(0, 1) = Value2 & r.Offset(0, 2) = Chroma Then
        MsgBox r.Offset(0, 3).Value
        Exit Sub
        End If
    Next r

MsgBox ("No color found for these Munsell color values!")

End Sub

This is the basic UserForm: enter image description here

Every time I run the code, I get the message "No color found...". Even though I put the exact matching values in the UserForm.

Upvotes: 1

Views: 236

Answers (1)

ZygD
ZygD

Reputation: 24386

Change

If r = Hue & r.Offset(0, 1) = Value2 & r.Offset(0, 2) = Chroma Then

with

If r = Hue and r.Offset(0, 1) = Value2 and r.Offset(0, 2) = Chroma Then

Upvotes: 1

Related Questions