Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Help
I found this code & is very close to what I need! Since I'm a novice at code
writing, I find what I need & then try to modify. I think this will work but I need a "do loop" or something to select "This" and "That" (see all original code at bottom) from a two column table. (The code seems to work if I put in the location of Ang & Rol in place of "This" ([F5]) & "That" ([G5])): Set rngColumnFound = rngColumnToSearch.Find(What:=[F5], LookAt:=xlWhole) Set rngRowFound = rngRowToSearch.Find(What:=[G5], LookAt:=xlWhole) but I need to loop through the Ang/Rol table. Can anyone help? example table (of course table can have "n" rows): Ang (Col F) Rol (Col G) Row 5 2 2 2 4 4 6 n n I modified the code below to look through a hugh table & find the intersection of Ang & Rol & then highlight the cell yellow. But need the code to go through the Ang/Rol table instead of entering "This" & "That" (F5/G5, F6/G6, etc.) each time. Thanks in advance, dkk ORIGINAL CODE: Sub FindIntersection() Dim rngColumnToSearch As Range Dim rngRowToSearch As Range Dim rngColumnFound As Range Dim rngRowFound As Range Dim rngIntersection As Range Dim wks As Worksheet 'Change the sheet row and column as necessary Set wks = Sheets("Sheet1") Set rngColumnToSearch = wks.Columns("A") Set rngRowToSearch = wks.Rows(1) 'Change the next 2 lines What:="???" Set rngColumnFound = rngColumnToSearch.Find(What:="This", LookAt:=xlWhole) Set rngRowFound = rngRowToSearch.Find(What:="That", LookAt:=xlWhole) If Not (rngColumnFound Is Nothing Or rngRowFound Is Nothing) Then Set rngIntersection = Intersect(rngColumnFound.EntireRow, _ rngRowFound.EntireColumn) MsgBox rngIntersection.Address End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Help
I don'tthink you need a loop unless you are looking for multiple results.
I'm not surre exactly what you are looking for. there are two possiblities. 1) You are looking to find a number(s) in column F and return the corresponding number in column G 2) You are trying to find a row(s) that matches both the F column data and the G column data.. "dkk" wrote: I found this code & is very close to what I need! Since I'm a novice at code writing, I find what I need & then try to modify. I think this will work but I need a "do loop" or something to select "This" and "That" (see all original code at bottom) from a two column table. (The code seems to work if I put in the location of Ang & Rol in place of "This" ([F5]) & "That" ([G5])): Set rngColumnFound = rngColumnToSearch.Find(What:=[F5], LookAt:=xlWhole) Set rngRowFound = rngRowToSearch.Find(What:=[G5], LookAt:=xlWhole) but I need to loop through the Ang/Rol table. Can anyone help? example table (of course table can have "n" rows): Ang (Col F) Rol (Col G) Row 5 2 2 2 4 4 6 n n I modified the code below to look through a hugh table & find the intersection of Ang & Rol & then highlight the cell yellow. But need the code to go through the Ang/Rol table instead of entering "This" & "That" (F5/G5, F6/G6, etc.) each time. Thanks in advance, dkk ORIGINAL CODE: Sub FindIntersection() Dim rngColumnToSearch As Range Dim rngRowToSearch As Range Dim rngColumnFound As Range Dim rngRowFound As Range Dim rngIntersection As Range Dim wks As Worksheet 'Change the sheet row and column as necessary Set wks = Sheets("Sheet1") Set rngColumnToSearch = wks.Columns("A") Set rngRowToSearch = wks.Rows(1) 'Change the next 2 lines What:="???" Set rngColumnFound = rngColumnToSearch.Find(What:="This", LookAt:=xlWhole) Set rngRowFound = rngRowToSearch.Find(What:="That", LookAt:=xlWhole) If Not (rngColumnFound Is Nothing Or rngRowFound Is Nothing) Then Set rngIntersection = Intersect(rngColumnFound.EntireRow, _ rngRowFound.EntireColumn) MsgBox rngIntersection.Address End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Help
I want a macro that takes the first row in F & G (in example numbers 2 & 2) &
in another table that has 0 to 360 columns & 0 to 180 rows find the intersection of 2 & 2 and highlights cell to yellow, then takes the next row (in example 2, 4) and highlights that intersection cell...until the whole Ang/Rol table has been completed. Ang/Rol Table: Ang (F) Rol(G) 2 2 2 4 4 6 etc etc Big Table: 0 2 4 6 (Columns) (Row1) 0 x x x x Macro Highlights cell intersection of 2 & 2, (Row2) 2 x x x x then highlights cell intersection of 2 & 4, (Row3) 4 x x x x then highlights cell intersection of 4 & 6, etc (Row4) 6 x x x x dkk "Joel" wrote: I don'tthink you need a loop unless you are looking for multiple results. I'm not surre exactly what you are looking for. there are two possiblities. 1) You are looking to find a number(s) in column F and return the corresponding number in column G 2) You are trying to find a row(s) that matches both the F column data and the G column data.. "dkk" wrote: I found this code & is very close to what I need! Since I'm a novice at code writing, I find what I need & then try to modify. I think this will work but I need a "do loop" or something to select "This" and "That" (see all original code at bottom) from a two column table. (The code seems to work if I put in the location of Ang & Rol in place of "This" ([F5]) & "That" ([G5])): Set rngColumnFound = rngColumnToSearch.Find(What:=[F5], LookAt:=xlWhole) Set rngRowFound = rngRowToSearch.Find(What:=[G5], LookAt:=xlWhole) but I need to loop through the Ang/Rol table. Can anyone help? example table (of course table can have "n" rows): Ang (Col F) Rol (Col G) Row 5 2 2 2 4 4 6 n n I modified the code below to look through a hugh table & find the intersection of Ang & Rol & then highlight the cell yellow. But need the code to go through the Ang/Rol table instead of entering "This" & "That" (F5/G5, F6/G6, etc.) each time. Thanks in advance, dkk ORIGINAL CODE: Sub FindIntersection() Dim rngColumnToSearch As Range Dim rngRowToSearch As Range Dim rngColumnFound As Range Dim rngRowFound As Range Dim rngIntersection As Range Dim wks As Worksheet 'Change the sheet row and column as necessary Set wks = Sheets("Sheet1") Set rngColumnToSearch = wks.Columns("A") Set rngRowToSearch = wks.Rows(1) 'Change the next 2 lines What:="???" Set rngColumnFound = rngColumnToSearch.Find(What:="This", LookAt:=xlWhole) Set rngRowFound = rngRowToSearch.Find(What:="That", LookAt:=xlWhole) If Not (rngColumnFound Is Nothing Or rngRowFound Is Nothing) Then Set rngIntersection = Intersect(rngColumnFound.EntireRow, _ rngRowFound.EntireColumn) MsgBox rngIntersection.Address End If End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Help
This is real easy. Change the sheet names as required. The code is simply
going to the Row Number and Column Number on the big sheet and highlighting the cell. I thinnk you may want to perform a lookup and the header row on the big table to get the correct column. if you need this then let me know which is the header row on the Big Table and I will make a small change to the code. Sub HighlightCells() With Sheets("AngLe/Row") RowCount = 1 Do While .Range("F" & RowCount) < "" Angle = .Range("F" & RowCount) Rol = .Range("G" & RowCount) With Sheets("Big Table") .Cells(Angle, Rol).Interior.ColorIndex = 6 End With RowCount = RowCount + 1 Loop End With End Sub "dkk" wrote: I want a macro that takes the first row in F & G (in example numbers 2 & 2) & in another table that has 0 to 360 columns & 0 to 180 rows find the intersection of 2 & 2 and highlights cell to yellow, then takes the next row (in example 2, 4) and highlights that intersection cell...until the whole Ang/Rol table has been completed. Ang/Rol Table: Ang (F) Rol(G) 2 2 2 4 4 6 etc etc Big Table: 0 2 4 6 (Columns) (Row1) 0 x x x x Macro Highlights cell intersection of 2 & 2, (Row2) 2 x x x x then highlights cell intersection of 2 & 4, (Row3) 4 x x x x then highlights cell intersection of 4 & 6, etc (Row4) 6 x x x x dkk "Joel" wrote: I don'tthink you need a loop unless you are looking for multiple results. I'm not surre exactly what you are looking for. there are two possiblities. 1) You are looking to find a number(s) in column F and return the corresponding number in column G 2) You are trying to find a row(s) that matches both the F column data and the G column data.. "dkk" wrote: I found this code & is very close to what I need! Since I'm a novice at code writing, I find what I need & then try to modify. I think this will work but I need a "do loop" or something to select "This" and "That" (see all original code at bottom) from a two column table. (The code seems to work if I put in the location of Ang & Rol in place of "This" ([F5]) & "That" ([G5])): Set rngColumnFound = rngColumnToSearch.Find(What:=[F5], LookAt:=xlWhole) Set rngRowFound = rngRowToSearch.Find(What:=[G5], LookAt:=xlWhole) but I need to loop through the Ang/Rol table. Can anyone help? example table (of course table can have "n" rows): Ang (Col F) Rol (Col G) Row 5 2 2 2 4 4 6 n n I modified the code below to look through a hugh table & find the intersection of Ang & Rol & then highlight the cell yellow. But need the code to go through the Ang/Rol table instead of entering "This" & "That" (F5/G5, F6/G6, etc.) each time. Thanks in advance, dkk ORIGINAL CODE: Sub FindIntersection() Dim rngColumnToSearch As Range Dim rngRowToSearch As Range Dim rngColumnFound As Range Dim rngRowFound As Range Dim rngIntersection As Range Dim wks As Worksheet 'Change the sheet row and column as necessary Set wks = Sheets("Sheet1") Set rngColumnToSearch = wks.Columns("A") Set rngRowToSearch = wks.Rows(1) 'Change the next 2 lines What:="???" Set rngColumnFound = rngColumnToSearch.Find(What:="This", LookAt:=xlWhole) Set rngRowFound = rngRowToSearch.Find(What:="That", LookAt:=xlWhole) If Not (rngColumnFound Is Nothing Or rngRowFound Is Nothing) Then Set rngIntersection = Intersect(rngColumnFound.EntireRow, _ rngRowFound.EntireColumn) MsgBox rngIntersection.Address End If End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Help
Thank you so much Joel!! I'll give this a try!!
"Joel" wrote: This is real easy. Change the sheet names as required. The code is simply going to the Row Number and Column Number on the big sheet and highlighting the cell. I thinnk you may want to perform a lookup and the header row on the big table to get the correct column. if you need this then let me know which is the header row on the Big Table and I will make a small change to the code. Sub HighlightCells() With Sheets("AngLe/Row") RowCount = 1 Do While .Range("F" & RowCount) < "" Angle = .Range("F" & RowCount) Rol = .Range("G" & RowCount) With Sheets("Big Table") .Cells(Angle, Rol).Interior.ColorIndex = 6 End With RowCount = RowCount + 1 Loop End With End Sub "dkk" wrote: I want a macro that takes the first row in F & G (in example numbers 2 & 2) & in another table that has 0 to 360 columns & 0 to 180 rows find the intersection of 2 & 2 and highlights cell to yellow, then takes the next row (in example 2, 4) and highlights that intersection cell...until the whole Ang/Rol table has been completed. Ang/Rol Table: Ang (F) Rol(G) 2 2 2 4 4 6 etc etc Big Table: 0 2 4 6 (Columns) (Row1) 0 x x x x Macro Highlights cell intersection of 2 & 2, (Row2) 2 x x x x then highlights cell intersection of 2 & 4, (Row3) 4 x x x x then highlights cell intersection of 4 & 6, etc (Row4) 6 x x x x dkk "Joel" wrote: I don'tthink you need a loop unless you are looking for multiple results. I'm not surre exactly what you are looking for. there are two possiblities. 1) You are looking to find a number(s) in column F and return the corresponding number in column G 2) You are trying to find a row(s) that matches both the F column data and the G column data.. "dkk" wrote: I found this code & is very close to what I need! Since I'm a novice at code writing, I find what I need & then try to modify. I think this will work but I need a "do loop" or something to select "This" and "That" (see all original code at bottom) from a two column table. (The code seems to work if I put in the location of Ang & Rol in place of "This" ([F5]) & "That" ([G5])): Set rngColumnFound = rngColumnToSearch.Find(What:=[F5], LookAt:=xlWhole) Set rngRowFound = rngRowToSearch.Find(What:=[G5], LookAt:=xlWhole) but I need to loop through the Ang/Rol table. Can anyone help? example table (of course table can have "n" rows): Ang (Col F) Rol (Col G) Row 5 2 2 2 4 4 6 n n I modified the code below to look through a hugh table & find the intersection of Ang & Rol & then highlight the cell yellow. But need the code to go through the Ang/Rol table instead of entering "This" & "That" (F5/G5, F6/G6, etc.) each time. Thanks in advance, dkk ORIGINAL CODE: Sub FindIntersection() Dim rngColumnToSearch As Range Dim rngRowToSearch As Range Dim rngColumnFound As Range Dim rngRowFound As Range Dim rngIntersection As Range Dim wks As Worksheet 'Change the sheet row and column as necessary Set wks = Sheets("Sheet1") Set rngColumnToSearch = wks.Columns("A") Set rngRowToSearch = wks.Rows(1) 'Change the next 2 lines What:="???" Set rngColumnFound = rngColumnToSearch.Find(What:="This", LookAt:=xlWhole) Set rngRowFound = rngRowToSearch.Find(What:="That", LookAt:=xlWhole) If Not (rngColumnFound Is Nothing Or rngRowFound Is Nothing) Then Set rngIntersection = Intersect(rngColumnFound.EntireRow, _ rngRowFound.EntireColumn) MsgBox rngIntersection.Address End If End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Help
Joel, it didn't work. Code stops at ".Cells(Angle, Rol).Interior.ColorIndex
= 6" & gives me an error "Run-time error 13: Type mismatch". I DO want lookup on column & row headings in big table. In big table the headings for the columns are (0-360 not row numbers) & headings for rows are (0 - 180 not row numbers). "dkk" wrote: Thank you so much Joel!! I'll give this a try!! "Joel" wrote: This is real easy. Change the sheet names as required. The code is simply going to the Row Number and Column Number on the big sheet and highlighting the cell. I thinnk you may want to perform a lookup and the header row on the big table to get the correct column. if you need this then let me know which is the header row on the Big Table and I will make a small change to the code. Sub HighlightCells() With Sheets("AngLe/Row") RowCount = 1 Do While .Range("F" & RowCount) < "" Angle = .Range("F" & RowCount) Rol = .Range("G" & RowCount) With Sheets("Big Table") .Cells(Angle, Rol).Interior.ColorIndex = 6 End With RowCount = RowCount + 1 Loop End With End Sub "dkk" wrote: I want a macro that takes the first row in F & G (in example numbers 2 & 2) & in another table that has 0 to 360 columns & 0 to 180 rows find the intersection of 2 & 2 and highlights cell to yellow, then takes the next row (in example 2, 4) and highlights that intersection cell...until the whole Ang/Rol table has been completed. Ang/Rol Table: Ang (F) Rol(G) 2 2 2 4 4 6 etc etc Big Table: 0 2 4 6 (Columns) (Row1) 0 x x x x Macro Highlights cell intersection of 2 & 2, (Row2) 2 x x x x then highlights cell intersection of 2 & 4, (Row3) 4 x x x x then highlights cell intersection of 4 & 6, etc (Row4) 6 x x x x dkk "Joel" wrote: I don'tthink you need a loop unless you are looking for multiple results. I'm not surre exactly what you are looking for. there are two possiblities. 1) You are looking to find a number(s) in column F and return the corresponding number in column G 2) You are trying to find a row(s) that matches both the F column data and the G column data.. "dkk" wrote: I found this code & is very close to what I need! Since I'm a novice at code writing, I find what I need & then try to modify. I think this will work but I need a "do loop" or something to select "This" and "That" (see all original code at bottom) from a two column table. (The code seems to work if I put in the location of Ang & Rol in place of "This" ([F5]) & "That" ([G5])): Set rngColumnFound = rngColumnToSearch.Find(What:=[F5], LookAt:=xlWhole) Set rngRowFound = rngRowToSearch.Find(What:=[G5], LookAt:=xlWhole) but I need to loop through the Ang/Rol table. Can anyone help? example table (of course table can have "n" rows): Ang (Col F) Rol (Col G) Row 5 2 2 2 4 4 6 n n I modified the code below to look through a hugh table & find the intersection of Ang & Rol & then highlight the cell yellow. But need the code to go through the Ang/Rol table instead of entering "This" & "That" (F5/G5, F6/G6, etc.) each time. Thanks in advance, dkk ORIGINAL CODE: Sub FindIntersection() Dim rngColumnToSearch As Range Dim rngRowToSearch As Range Dim rngColumnFound As Range Dim rngRowFound As Range Dim rngIntersection As Range Dim wks As Worksheet 'Change the sheet row and column as necessary Set wks = Sheets("Sheet1") Set rngColumnToSearch = wks.Columns("A") Set rngRowToSearch = wks.Rows(1) 'Change the next 2 lines What:="???" Set rngColumnFound = rngColumnToSearch.Find(What:="This", LookAt:=xlWhole) Set rngRowFound = rngRowToSearch.Find(What:="That", LookAt:=xlWhole) If Not (rngColumnFound Is Nothing Or rngRowFound Is Nothing) Then Set rngIntersection = Intersect(rngColumnFound.EntireRow, _ rngRowFound.EntireColumn) MsgBox rngIntersection.Address End If End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop Help
Joel....Almost there! I got it to work & it is like you said, it looks up
row number & column number....but I do want it to look up row Headings & column Headings. Thank you so much for getting me this far! If you could help just a little more & help with looking up row & column headings, I would deeply appreciate it! "dkk" wrote: Joel, it didn't work. Code stops at ".Cells(Angle, Rol).Interior.ColorIndex = 6" & gives me an error "Run-time error 13: Type mismatch". I DO want lookup on column & row headings in big table. In big table the headings for the columns are (0-360 not row numbers) & headings for rows are (0 - 180 not row numbers). "dkk" wrote: Thank you so much Joel!! I'll give this a try!! "Joel" wrote: This is real easy. Change the sheet names as required. The code is simply going to the Row Number and Column Number on the big sheet and highlighting the cell. I thinnk you may want to perform a lookup and the header row on the big table to get the correct column. if you need this then let me know which is the header row on the Big Table and I will make a small change to the code. Sub HighlightCells() With Sheets("AngLe/Row") RowCount = 1 Do While .Range("F" & RowCount) < "" Angle = .Range("F" & RowCount) Rol = .Range("G" & RowCount) With Sheets("Big Table") .Cells(Angle, Rol).Interior.ColorIndex = 6 End With RowCount = RowCount + 1 Loop End With End Sub "dkk" wrote: I want a macro that takes the first row in F & G (in example numbers 2 & 2) & in another table that has 0 to 360 columns & 0 to 180 rows find the intersection of 2 & 2 and highlights cell to yellow, then takes the next row (in example 2, 4) and highlights that intersection cell...until the whole Ang/Rol table has been completed. Ang/Rol Table: Ang (F) Rol(G) 2 2 2 4 4 6 etc etc Big Table: 0 2 4 6 (Columns) (Row1) 0 x x x x Macro Highlights cell intersection of 2 & 2, (Row2) 2 x x x x then highlights cell intersection of 2 & 4, (Row3) 4 x x x x then highlights cell intersection of 4 & 6, etc (Row4) 6 x x x x dkk "Joel" wrote: I don'tthink you need a loop unless you are looking for multiple results. I'm not surre exactly what you are looking for. there are two possiblities. 1) You are looking to find a number(s) in column F and return the corresponding number in column G 2) You are trying to find a row(s) that matches both the F column data and the G column data.. "dkk" wrote: I found this code & is very close to what I need! Since I'm a novice at code writing, I find what I need & then try to modify. I think this will work but I need a "do loop" or something to select "This" and "That" (see all original code at bottom) from a two column table. (The code seems to work if I put in the location of Ang & Rol in place of "This" ([F5]) & "That" ([G5])): Set rngColumnFound = rngColumnToSearch.Find(What:=[F5], LookAt:=xlWhole) Set rngRowFound = rngRowToSearch.Find(What:=[G5], LookAt:=xlWhole) but I need to loop through the Ang/Rol table. Can anyone help? example table (of course table can have "n" rows): Ang (Col F) Rol (Col G) Row 5 2 2 2 4 4 6 n n I modified the code below to look through a hugh table & find the intersection of Ang & Rol & then highlight the cell yellow. But need the code to go through the Ang/Rol table instead of entering "This" & "That" (F5/G5, F6/G6, etc.) each time. Thanks in advance, dkk ORIGINAL CODE: Sub FindIntersection() Dim rngColumnToSearch As Range Dim rngRowToSearch As Range Dim rngColumnFound As Range Dim rngRowFound As Range Dim rngIntersection As Range Dim wks As Worksheet 'Change the sheet row and column as necessary Set wks = Sheets("Sheet1") Set rngColumnToSearch = wks.Columns("A") Set rngRowToSearch = wks.Rows(1) 'Change the next 2 lines What:="???" Set rngColumnFound = rngColumnToSearch.Find(What:="This", LookAt:=xlWhole) Set rngRowFound = rngRowToSearch.Find(What:="That", LookAt:=xlWhole) If Not (rngColumnFound Is Nothing Or rngRowFound Is Nothing) Then Set rngIntersection = Intersect(rngColumnFound.EntireRow, _ rngRowFound.EntireColumn) MsgBox rngIntersection.Address End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
returning back to loop check condition without completing the loop | Excel Programming | |||
Loop to Filter, Name Sheets. If Blank, Exit Loop | Excel Programming | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
(Complex) Loop within loop to create worksheets | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming |