excel vba loop through range and if statements

Question

I need help looping through a given range followed by an if statement. My Range is:

| A    | B     | C    |
-----------------------
|TPA   | C:\   | 1.doc|

|TPA   | C:\   | 2.doc|

|LAX   | D:\   | 3.doc|

I want to loop through A1:C3 and if Column A1:A3 = "TPA" Then take the data of B1 and C1.

Dim test1 As Variant
Dim cell As Range
Dim cell1 As Range
cell = Worksheets("Sheet2").Range ("A1:C3")
cell1 = Worksheets("Sheet2").Range ("A1:A3")
For Each cell1 in cell
If test1 = "TPA" Then 
'MsgBox B1 and C1 But I need to MsgBox B2 and C2 as well
End If
Next

Ultimately, I need to msgbox B1 + C1, and B2 + C2.

Nimantha · Accepted Answer · 2021-11-13 03:07:43Z

1

Dim test1 As String, c As Range, myRng As Range
Dim Result as string

set myRng = Worksheets("Sheet2").Range("A1:A3")
For Each c in myRng
    If test1 = "TPA" Then 
        Result = Result & c & c.offset(0,1) & vbCrLf
    End If
Next c
MsgBox Result

edited Nov 13, 2021 at 3:07

Nimantha

6,5476 gold badges32 silver badges78 bronze badges

answered Dec 11, 2014 at 16:03

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

excel vba loop through range and if statements

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related