CODEWITHSUNDEEP
vbaexcel
phillipsK
phillipsK

Reputation: 1516

For loop with multiple variables

Here is a excel vba sub procedure example. I have two columns of data, range v and range c - How could I concatenate each cell rows' value with the parallel row call value.

Ideally, what I am trying to do would be this

For Each c,b In v,bb
 ...
next c,b

Pleas let me further explain: cell G2 value is only related to J2, and G3 with J3

G2 value = Blue
J2 value = Spaghetti

I am trying to return "Blue Spaghetti" with one for loop?

G2 value = Red
J2 value = Noodles

I am trying to return "Red Noodles" with one for loop?

Dim c As Variant
Dim b As Variant
Dim v As Range
Dim bb As Range
Dim brow As Long
Dim vrow as long

Set v = ActiveSheet.Range("G:G")
vrow = v(v.Cells.Count).End(xlUp).Row
Set v = Range(v(2), v(brow))

Set bb = ActiveSheet.Range("J:J")
brow = bb(bb.Cells.Count).End(xlUp).Row
Set bb = Range(bb(2), bb(brow))    

For Each c In v
    c = Mid(c, 1, 4)
    msgbox c
Next c

For each b in bb   
    msgbox b    
next b

Upvotes: 1

Views: 7314

Answers (1)

peege
peege

Reputation: 2477

Looking at your original post, I'm going to say I'm confused with all the extra stuff. Look at what goes on here, and comment with questions. I think you are over complicating what you are attempting.

Sub ConcatCols()    
    Dim lastRow As Long
    Dim tempValue As String

    lastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).row

    For iRow = 2 to lastRow
        tempValue = Sheets("Sheet1").Cells(iRow, "G").Text & " " & _
            Sheets("Sheet1").Cells(iRow, "J").Text
        MsgBox tempValue   
    Next iRow    
End Sub

Upvotes: 5

Related Questions