PDA

View Full Version : Help w/ Loop



YellowLabPro
12-06-2007, 01:37 PM
I am trying to loop through a range on Sheet1 get the values from each row and deliver them on Sheet2, with, certain ones joined together down row 1

For instance the value below is A1:G1
32 305 Snowboard Boots Black/WhiteReg.$274.99Discount50%Now:$137.49
It would look like this on Sheet2, A1:A4
32 305 Snowboard Boots Black/WhiteReg. $274.99Discount 50%Now: $137.49

I would skip a row and do this again.
What I am struggling w/ is how to icrement the row to begin the next record. I am very rusty, I have not written any code for a couple months now. Uggh.

Sub mConcatenate3()
Dim c As Range, rng As Range
Dim Lrow As Long
Lrow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Sheets("Sheet1").Range("A1:A" & Lrow)
For Each c In rng
ActiveCell.FormulaR1C1 = "=Sheet1!RC"
Next c
End Sub

Norie
12-06-2007, 02:31 PM
Doug

Why are you using ActiveCell instead of the loop control variable c?

YellowLabPro
12-06-2007, 02:44 PM
Only b/c the macro recorder gave it to me. That is what I am looking for help w/, or at least one of the things.

Bob Phillips
12-06-2007, 03:06 PM
Struggling to see what is in what cell Doug, it all looks like a single string.

YellowLabPro
12-06-2007, 03:57 PM
Hello Bob,
Here is the file.
Sheet1 has the items I want to create the string from.
Sheet22 has an example of the final result.
Sheet2 is what my code produces now, which is preliminary.

Bob Phillips
12-06-2007, 04:48 PM
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim sh As Worksheet

With ActiveSheet

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Results").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set sh = Worksheets.Add
sh.Name = "Results"

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow

sh.Cells((i - 1) * 5 + 1, "A").Value = .Cells(i, "A").Text
sh.Cells((i - 1) * 5 + 2, "A").Value = .Cells(i, "B").Text & " " & _
.Cells(i, "C").Text
sh.Cells((i - 1) * 5 + 3, "A").Value = .Cells(i, "D").Text & " " & _
.Cells(i, "E").Text
sh.Cells((i - 1) * 5 + 4, "A").Value = .Cells(i, "F").Text & " " & _
.Cells(i, "G").Text
sh.Cells((i - 1) * 5 + 1, "A").Resize(5).BorderAround _
ColorIndex:=xlColorIndexAutomatic, Weight:=xlThin

Next i
sh.Columns(1).HorizontalAlignment = xlCenter
sh.Columns(1).AutoFit
End With

End Sub

YellowLabPro
12-06-2007, 05:17 PM
Perfectum.

Most grateful, thank you Bob.