Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to loop thru cells in a row
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to loop thru cells in a row
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to loop thru cells in a row
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help - loop through cells in a range that are not together (several different cells as Target) | Excel Programming | |||
Referencing cells in a loop | Excel Programming | |||
Loop through cells | Excel Programming | |||
HOW-TO? Loop through cells in a column | Excel Programming | |||
VBA loop cells | Excel Programming |