Loop through records to concatenate

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!!!!! :kiss

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)?

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.

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

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

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...

Code:

---

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

---

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?

Code:

---

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:

Code:

---

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

---

... or something similar.

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

Code:

---

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

---

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:

Code:

---

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!!!

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