Looping letters (or columns)
 

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?

Looping letters (or columns)
 

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

"JCanyoneer" wrote in message
...
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
. Anyone have any simple ideas?

Looping letters (or columns)
 

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" wrote:

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

"JCanyoneer" wrote in message
...
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
. Anyone have any simple ideas?

Looping letters (or columns)
 

"JCanyoneer" wrote in message
...
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

Looping letters (or columns)
 

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

"Fredrik Wahlgren" wrote:


"JCanyoneer" wrote in message
...
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

Looping letters (or columns)
 

"JCanyoneer" wrote in message
...
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

Looping letters (or columns)
 

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

--
Regards,
Tom Ogilvy




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

"Fredrik Wahlgren" wrote:


"JCanyoneer" wrote in message
...
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