Looping through columns

[hallingh]

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:

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.

Any help you can provide would be greatly appreciated...

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

 

[mikerickson]

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

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]

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

THanks!

Hank

 

[hallingh]

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?

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

Thanks again for the help!

Hank

 

[mikerickson]

I'd use the Cells property rather than Range.

Sheets("Helper BAC V").Cells(3, (onecolumnindex - 119)).Value = 5

 

[hallingh]

Awesome.

Thanks again!

 

[hallingh]

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

 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]

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