Looping letters (or columns)

[Guest]

I have a section of code that runs muliply times that could be reduced to a
loop if I could figure out the syntax. Looking for help with this:

' Alternating Safety Lights
If ThisWorkbook.Worksheets("Sheet1").Range("A19") = True Then
destWB.Worksheets("Sheet1").Range("H" & Lr) = "X"
End If
' Bed Step
If ThisWorkbook.Worksheets("Sheet1").Range("A20") = True Then
destWB.Worksheets("Sheet1").Range("I" & Lr) = "X"
End If
' Blanket Can Holder
If ThisWorkbook.Worksheets("Sheet1").Range("A21") = True Then
destWB.Worksheets("Sheet1").Range("J" & Lr) = "X"
End If
' Canopy Roof
If ThisWorkbook.Worksheets("Sheet1").Range("A22") = True Then
destWB.Worksheets("Sheet1").Range("K" & Lr) = "X"
End If

Each if loop is basically for a different part (There are 38 parts). The
loop needed must change the Range("Axx") value (I can do this pretty easy,
for xx=19 to 22), the part I nee help with is the Letter or Column loop
changing the Range("X" & Lr) from H to K. Anyone have any simple ideas?

 

[Fredrik Wahlgren]

Something like this? I am not sure I understand "changing the Range("X" &
Lr) from H to K"

Public Sub test()
Dim i As Long

For i = 19 To 21 ' Or whatever
If ThisWorkbook.Worksheets("Sheet1").Range("A" & CStr(i)) = True Then
destWB.Worksheets("Sheet1").Range("H" & Lr) = "X"
End If
Next
End Sub

/Fredrik

 

[Guest]

Yes, I think this code will loop my the range in the if statement. I need to
be able to loop the Column or letter(H, I, J, K) in range from the destWB
line.
Does that help clear anything up?

 

[Fredrik Wahlgren]

Yes, I think this code will loop my the range in the if statement. I need to
be able to loop the Column or letter(H, I, J, K) in range from the destWB
line.
Does that help clear anything up?

I hope so. Here's my second suggestion

Public Sub test()
Dim i As Long
Dim c As Long

For i = 19 To 21
If ThisWorkbook.Worksheets("Blad1").Range("A" & CStr(i)) = True Then
For c = 72 To 75
destWB.Worksheets("Blad1").Range(Chr(c) & Lr) = "X"
Next c
End If
Next
End Sub

/Fredrik

 

[Guest]

That should do it! Things are pretty easy when you know the code to use. CHR
is new to me. Thanks!

 

[Fredrik Wahlgren]

That should do it! Things are pretty easy when you know the code to use. CHR
is new to me. Thanks!

Good thing that you realized things could be simplified. I have seen code
that did the same much the same thing about 5000 times.
/fredrik

 

[Tom Ogilvy]

Just my opinion, but you are headed down a deadend street. What happends if
you want to loop from W to AF? You don't need to get letters involved at
all and **nobody** does it that way.

Public Sub test()
Dim i As Long
Dim c As Long

For i = 19 To 21
If ThisWorkbook.Worksheets("Blad1").Cells(i, 1) = True Then
For c = 8 To 11
destWB.Worksheets("Blad1").Cells(Lr, c) = "X"
Next c
End If
Next
End Sub