How to loop thru cells in a row

[bourbon84]

I have several thousand rows in a single worksheet. It looks similar to this:

Date1 Date2 Activity Count Width Length Description
June 29 June 30 Run 1 10 20 Yellow
June 28 June 29 Run Walk 3 15 35 Red
June 27 June 26 Drive Bike Walk 2 17 22
Blue
June 26 June 25 Walk Drive Run 5 12 32
Green

Above, row1 is correct, or rather, how things are supposed to look.
Starting with row2, I need the text under "Activity" and "Count" to be
concatenated under "Activity". In rows 3 and 4, I need Drive Bike Walk to be
concatenated under "Activity" and so on. If you notice, 3 cells of numbers
follow each textual "Activity". Starting with Column3, I need to evaluate
each row in my worksheet, and find the first cell that has a number, after
the text in "Activity". After I concatenate all the text into each cell of
Column3, I need to move the first number to be in Column4, the second number
to be in Column5, and the third, and last, number to be in Column6. It
should look like the following...

Date1 Date2 Activity Count Width Length
Description
June 29 June 30 Run 1 10 20
Yellow
June 28 June 29 Run Walk 3 15 35
Red
June 27 June 26 Drive Bike Walk 2 17 22
Blue
June 26 June 25 Walk Drive Run 5 12 32
Green

So, I need a loop, which I just can't seem to figure out. When it's done
evaluating which one is a number and which one isn't, I need to start cutting
and pasting values within the cell. I'd post my code, but it's so
ridiculously bad, I'm embarrassed to do so. Thanks for the help.

Mark

[Jim Thomlinson]

I think this is what you are looking for...

Public Sub AdjustRows()
Dim rngToAdjust As Range
Dim rng As Range
Dim wks As Worksheet

Set wks = Sheets("Sheet1") 'Adjust this as necessary
Set rngToAdjust = wks.Range("C2", wks.Cells(Rows.Count, "C").End(xlUp))

For Each rng In rngToAdjust
With rng
Do While Not IsNumeric(.Offset(0, 1).Value)
.Value = .Value & ", " & .Offset(0, 1).Value
.Offset(0, 1).Delete xlToLeft
Loop
End With
Next rng
End Sub
--
HTH...

Jim Thomlinson


"bourbon84" wrote:

> I have several thousand rows in a single worksheet. It looks similar to this:
>
> Date1 Date2 Activity Count Width Length Description
> June 29 June 30 Run 1 10 20 Yellow
> June 28 June 29 Run Walk 3 15 35 Red
> June 27 June 26 Drive Bike Walk 2 17 22
> Blue
> June 26 June 25 Walk Drive Run 5 12 32
> Green
>
> Above, row1 is correct, or rather, how things are supposed to look.
> Starting with row2, I need the text under "Activity" and "Count" to be
> concatenated under "Activity". In rows 3 and 4, I need Drive Bike Walk to be
> concatenated under "Activity" and so on. If you notice, 3 cells of numbers
> follow each textual "Activity". Starting with Column3, I need to evaluate
> each row in my worksheet, and find the first cell that has a number, after
> the text in "Activity". After I concatenate all the text into each cell of
> Column3, I need to move the first number to be in Column4, the second number
> to be in Column5, and the third, and last, number to be in Column6. It
> should look like the following...
>
> Date1 Date2 Activity Count Width Length
> Description
> June 29 June 30 Run 1 10 20
> Yellow
> June 28 June 29 Run Walk 3 15 35
> Red
> June 27 June 26 Drive Bike Walk 2 17 22
> Blue
> June 26 June 25 Walk Drive Run 5 12 32
> Green
>
> So, I need a loop, which I just can't seem to figure out. When it's done
> evaluating which one is a number and which one isn't, I need to start cutting
> and pasting values within the cell. I'd post my code, but it's so
> ridiculously bad, I'm embarrassed to do so. Thanks for the help.
>
> Mark

[bourbon84]

That totally worked!!!! You rock. Thanks.
Mark

"Jim Thomlinson" wrote:

> I think this is what you are looking for...
>
> Public Sub AdjustRows()
> Dim rngToAdjust As Range
> Dim rng As Range
> Dim wks As Worksheet
>
> Set wks = Sheets("Sheet1") 'Adjust this as necessary
> Set rngToAdjust = wks.Range("C2", wks.Cells(Rows.Count, "C").End(xlUp))
>
> For Each rng In rngToAdjust
> With rng
> Do While Not IsNumeric(.Offset(0, 1).Value)
> .Value = .Value & ", " & .Offset(0, 1).Value
> .Offset(0, 1).Delete xlToLeft
> Loop
> End With
> Next rng
> End Sub
> --
> HTH...
>
> Jim Thomlinson
>
>
> "bourbon84" wrote:
>
> > I have several thousand rows in a single worksheet. It looks similar to this:
> >
> > Date1 Date2 Activity Count Width Length Description
> > June 29 June 30 Run 1 10 20 Yellow
> > June 28 June 29 Run Walk 3 15 35 Red
> > June 27 June 26 Drive Bike Walk 2 17 22
> > Blue
> > June 26 June 25 Walk Drive Run 5 12 32
> > Green
> >
> > Above, row1 is correct, or rather, how things are supposed to look.
> > Starting with row2, I need the text under "Activity" and "Count" to be
> > concatenated under "Activity". In rows 3 and 4, I need Drive Bike Walk to be
> > concatenated under "Activity" and so on. If you notice, 3 cells of numbers
> > follow each textual "Activity". Starting with Column3, I need to evaluate
> > each row in my worksheet, and find the first cell that has a number, after
> > the text in "Activity". After I concatenate all the text into each cell of
> > Column3, I need to move the first number to be in Column4, the second number
> > to be in Column5, and the third, and last, number to be in Column6. It
> > should look like the following...
> >
> > Date1 Date2 Activity Count Width Length
> > Description
> > June 29 June 30 Run 1 10 20
> > Yellow
> > June 28 June 29 Run Walk 3 15 35
> > Red
> > June 27 June 26 Drive Bike Walk 2 17 22
> > Blue
> > June 26 June 25 Walk Drive Run 5 12 32
> > Green
> >
> > So, I need a loop, which I just can't seem to figure out. When it's done
> > evaluating which one is a number and which one isn't, I need to start cutting
> > and pasting values within the cell. I'd post my code, but it's so
> > ridiculously bad, I'm embarrassed to do so. Thanks for the help.
> >
> > Mark