For loop with multiple variables

Question

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

L42 · Accepted Answer · 2014-12-03 01:14:40Z

5

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

edited Dec 3, 2014 at 1:14

L42

19.8k11 gold badges47 silver badges70 bronze badges

answered Dec 3, 2014 at 0:12

peege

2,4771 gold badge13 silver badges24 bronze badges

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

4 Comments

phillipsK Over a year ago

How could I append tempValue = Sheets("Sheet1").Cells(iRow, "G").Text to tempValue = Sheets("Sheet1").Cells(MID(Irow,1,4), "G").Text I am only looking to slice values in column G.

peege Over a year ago

your Mid(iRow,1,4) isn't going to do what you think. Can you slice the record in G in a previous step, then use the result, instead of combining it all in one formula? Just grab whatever you are looking to pull... What are you trying to get out of G?

phillipsK Over a year ago

I was able to slice in a preceding line. I am always interested in precise code and I was curious if there was another way to slice in the fewest statements possible, thank you. Where could I find information of VB properties and methods, I constantly find myself attempting to Sheets.range.value.cell.text

peege Over a year ago

Lately I've been a fan of MicrosoftVirtualAcademy.com . As far as getting your code into the fewest statements possible, it's not any easier on the processing. It still has to do every bit of math, and in some cases a lot more (using Worksheet Functions like VLookup). To me, the best code is written efficiently and so that others can easily make sense of it, without having to look through huge sets of "(this(that(thisthat(morethis.that())that()))". Break up your code into smaller testable subroutines and make it easy for yourself and everyone you work with. My two cents, anyway.

Collectives™ on Stack Overflow

For loop with multiple variables

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related