Loop through records to concatenate

[eed]

Okay, I'm pretty comfortable using VBA in Access, but I have only a little experience applying it to Excel, so I would appreciate some assistance in translating my goal into proper Excel code (which, by the way, I intend to run from Access... heh...)

Each record has a long piece of text which has been split into 6 columns (to prevent Access truncating it during the export to Excel). Now that it's in Excel, I'd like to concatenate those 6 columns back into one. SO, basically I want to:

- Start at row 4, the first record under all my headers.
- Loop through all records until EOF
- For each record where # = row number, cell F# should = G#.Value & H#.Value & I#.Value & J#.Value & K#.Value & L#.Value

Any assistance in forming proper Excel syntax would be deeply appreciated. Thanks!!!!!

[Zack Barresse]

Hi!

A little clarification, you have record numbers in column A? And where your record number matches the row number, concatenate thevlues in F:L? Loop this as long as there is data in A4:A(last row)?

[eed]

No, simpler than that: for every record, I want the values of cells G-L to be concatenated into one string in cell F. Sorry for the "muddy" first explanation. There will always be data in column A, so I guess an empty cell in column A could be used to determine the end of the records, or whatever.

[Zack Barresse]

Ok, so any record in column A? The value doesn't have to match the row number?

[eed]

No, no, not at all. In any given record at all, cell F should equal G & H & I & J & K & L.

[eed]

I'm guessing that I can use R1C1 reference style... so if I know I have to start with F4, I could use something like the following... but what I'm not sure of is how to set up a loop so that it will do this on every row...

F4.FormulaR1C1 = RC[+1].Value & RC[+2].Value & RC[+3].Value _
& RC[+4].Value & RC[+5].Value & RC[+6].Value

[eed]

Wait, can I do something like this?? And does anyone know how I can use this in Access VBA? I mean, I already added an object prefix (ExcelWS.Range...), but Access doesn't like the brackets... any suggestions?

Range("F4:F" & FinalRow).FormulaR1C1 = RC[+1].Value & RC[+2].Value _
& RC[+3].Value & RC[+4].Value & RC[+5].Value & RC[+6].Value

Oops, I got my relative reference in the wrong place... I meant something more like:

R[+1]C.Value & ... & R[+6]C.Value

... or something similar.

[Zack Barresse]

Probably not the best option (can't do Access for ya)...

Sub concateColA()
Dim start As Long
    Dim lastRow As Long
    start = 4
    lastRow = [A65536].End(xlUp).Row
    Do
        Range("F" & start).Value = _
            Range("G" & start).Value & Range("H" & start).Value & _
            Range("I" & start).Value & Range("J" & start).Value & _
            Range("K" & start).Value & Range("L" & start).Value
        start = start + 1
    Loop Until start > lastRow
End Sub

[eed]

Oh. My. Gosh. You are so good. That worked beautifully. I just added the right references onto the front to let it run in Access, so for anyone else who might be interested, I ended up with:

Dim start As Long, lastRow As Long 
start = 4
lastRow = ExcelWS1.Range("A65536").End(xlUp).Row
Do
ExcelWS1.Range("F" & start).Value = _
ExcelWS1.Range("G" & start).Value & ExcelWS1.Range("H" & start).Value & _
ExcelWS1.Range("I" & start).Value & ExcelWS1.Range("J" & start).Value & _
ExcelWS1.Range("K" & start).Value & ExcelWS1.Range("L" & start).Value
start = start + 1
Loop Until start > lastRow

Thank you, Zack! Now I can wrap this procedure up today and not think about it all weekend... thanks again!!!

[Zack Barresse]

Well I'm glad you got it sorted! Glad to help!!