Looping through columns

hallingh

Well-known Member
OK, I have 13 columns containing either 0,1,3,9, or 99. I need to loop through these columns and do something depending on which of those values each column contains. My only issue is I don't know how to loop through columns. The first column is DR, the last column is ED. Something like this:

Code:
``````For ActiveSHeet.Columns(DR) To ActiveSheet.Columns(ED)
If Column.Row("3").Value = 0 Then
ElseIf .Value=1 Then
ElseIf .Value=3 Then
ElseIf .Value=9 Then
ElseIf .Value=99 Then
End If
Next Column``````

Obviously this isn't correct, just a stab so you have a better idea of what I'm trying to accomplish.

Oh, and I don't need help with the If statements, I just need to figure out how to loop through the correct columns.

Thanks!

Hank

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

mikerickson

MrExcel MVP
Actualy, that's very close. Avoiding the use of column as a variable and using a With...End With construction would give

Code:
``````Dim oneColumnIndex as Range

For oneColumnIndex = DR To ED

With ActiveSHeet.Columns(oneColumnIndex)
With .Rows(3)
If .Value = 0 Then
Rem do something
ElseIf .Value=1 Then
Rem do something
ElseIf .Value=3 Then
Rem do something
ElseIf .Value=9 Then
Rem do something
ElseIf .Value=99 Then
Rem do something
End If
End With
End With
Next oneColumnIndex``````

hallingh

Well-known Member
Fantastic. Thanks so much for the help. Just couldn't quite figure out how to get that loop to work properly.

THanks!

Hank

hallingh

Well-known Member
OK, one more question. The code in my IF statements needs to modify cells with different column values on another sheet. How could I do this?

I need it to modify two column values on another sheet for each onecolumnindex. The columns that need to be modified start at column C, which I think is 119 columns to the left of DR... Could I do something like this?

Code:
``````If .Value = 0 Then
Sheets("Helper BAC V").Range((onecolumnindex - 119) & "3").Value = 5``````

Thanks again for the help!

Hank

mikerickson

MrExcel MVP
I'd use the Cells property rather than Range.
Code:
``Sheets("Helper BAC V").Cells(3, (onecolumnindex - 119)).Value = 5``

Awesome.

Thanks again!

hallingh

Well-known Member
Ah! Not sure what happened, but I'm getting a "Type Mismatch" error on this code now:

Code:
`` For oneColumnIndex = DR To ED``

I have a bunch of other loops in my code, so maybe it wasn't running before?

Either way, do you have any idea why I might get this error? I have OneColumnIndex dimmed as Range at the top of my code...

Thanks again.

Hank

hallingh

Well-known Member
Nevermind, I just changed the variable to Long and used a number to reference the columns instead of a column letter. Thanks for all the help!

Hank

Replies
1
Views
84
Replies
3
Views
138
Replies
4
Views
339
Replies
1
Views
120
Replies
6
Views
332

1,195,989
Messages
6,012,718
Members
441,722
Latest member
tpaman1975

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back