Thread: Loop Help
View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
dkk dkk is offline
external usenet poster
 
Posts: 8
Default 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