Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Columns now numbers rather than letters how do i get letters back | Excel Discussion (Misc queries) | |||
looping across columns in range? | Excel Discussion (Misc queries) | |||
Looping through columns | Excel Programming | |||
Looping thru columns beyond Z | Excel Programming | |||
Looping question for 2 columns | Excel Programming |