adjacent cell values added to string

[mzza]

Hi all,

been away from VBA coding for a VERY long time, so I'm sure there's something very obvious that I'm missing here - probably LOTS of things. Here's what I'm trying to do:

sheet "usage" has 8 columns. Column B has a list of countries, which may grow and shrink over time. Column E shows an "x" by those countries which the user has selected. I want the code to go through the entire of Column E looking for "x"s - where it finds an "x", I need it to build a text string based on the contents of columns B and H for the rows with "x"s in.

Here was my first stab, which isn't behaving at all but will hopefully help clarify...

Sub Countries()

Dim iString As String
Dim c As Range

For Each c In Range("E2", Range("E" & Rows.Count).End(xlUp))
    If c.Value = "x" Then
        iString = c.Offset(-3, 0).Value & " - " & c.Offset(2, 0).Value
    End If
Next c

MsgBox (iString)

End Sub

... the MsgBox is only there for testing purposes to demonstrate the contents of the string.

Any advice would be much appreciated!

TIA

mzza

 

[njimack]

Something like this perhaps...

Sub Countries()
Dim Last_Row As Long
Dim i As Long
Dim iString As String

With Sheets("Usage")
    Last_Row = .Range("E" & Rows.Count).End(xlUp).Row
    
    For i = 2 To Last_Row
        If .Cells(i, 5) = "x" Then iString = iString & "," & Cells(i, 2).Value & "-" & Cells(i, 8).Value
    Next i
End With
    
    MsgBox iString
End Sub

 

[mzza]

Absolutely perfect, thank you!