Loop Help

[dkk]

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()

 

[Joel]

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..

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()

 

[dkk]

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

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..

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

 

[Joel]

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

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

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..

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

 

[dkk]

Thank you so much Joel!! I'll give this a try!!

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

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

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..

:

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

 

[dkk]

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).

Thank you so much Joel!! I'll give this a try!!

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

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


:

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..

:

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

 

[dkk]

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!

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).

Thank you so much Joel!! I'll give this a try!!

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




:

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


:

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..

:

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