Loop problem

[Todd Huttenstine]

Dim rngJ As Range
Dim numcount3
numcount3 = Application.WorksheetFunction.CountA(Workbooks
("Book2.xls").Sheets("Resolutions").Range("AD:AD"))

For Each cell In Workbooks("Book2.xls").Sheets
("Resolutions").Range("AF2:AF" & numcount3)
Set rngJ = Workbooks("Book2.xls").Sheets
("Resolutions").Range("AE1:AE200").Find(Workbooks
("Book2.xls").Sheets("Resolutions").Range("C2")) 'where
lookup value
If Not rngJ Is Nothing Then
rngJ.Offset(0, -1).Resize(, 2).Delete Shift:=xlShiftUp
End If
Next

The above coe works however I want to add one more
functionality. I want the code to look not only in AF2 but
in the entire range of AF:AF and for each value it finds,
I want it to do the code I already have.

Thanx

 

[Rob van Gelder]

I can't see what you're trying to achieve with the code you've posted.

You can loop through each cell in a column:

Sub test()
Dim rng As Range

For Each rng In Range("AF:AF")
Debug.Print rng.Address
Next
End Sub

 

[Tom Ogilvy]

Dim rngJ As Range
Dim numcount3
numcount3 = Workbooks("Book2.xls").Sheets( _
"Resolutions").Cells(rows.count,1).End(xlup).Row

For Each cell In Workbooks("Book2.xls").Sheets
("Resolutions").Range("AF2:AF" & numcount3)
Set rngJ = Workbooks("Book2.xls").Sheets
("Resolutions").Range("AE1:AE200").Find(Workbooks
("Book2.xls").Sheets("Resolutions").Range("C2")) 'where
lookup value
If Not rngJ Is Nothing Then
rngJ.Offset(0, -1).Resize(, 2).Delete Shift:=xlShiftUp
End If
Next

 

[Todd Huttenstine]

Hey

Dim rngJ As Range

With Workbooks("Book2.xls").Sheets("Resolutions")
Set rngJ = .Range("B1:B200").Find(.Range("C2"))
Do Until rngJ Is Nothing
rngJ.Resize(, 1).Delete Shift:=xlShiftUp
Set rngJ = .Range("B1:B200").FindNext
Loop
End With

That was the same as I already have. Ok it loops through
range B:B and for each match it finds with cell C2, it
deletes like its supposed to. This loop works fine.
However I want another loop for each value in C:C. There
are more than 1 lookup values to match than only cell C2.
So I now need the code to look at all the values in C:C
and for each value it finds, it loops through range B:B to
try to find matches(like it already does)

Does this make more sense?

 

[Rob van Gelder]

Sub test()
Dim rng As Range, rngJ As Range, rngF As Range

With Workbooks("Book2.xls").Sheets("Resolutions")
Set rngF = Range(.Range("C2"), .Range("C2").End(xlDown))
For Each rng In rngF
Set rngJ = .Range("B1:B200").Find(rng.Value)
Do Until rngJ Is Nothing
rngJ.Resize(, 1).Delete Shift:=xlShiftUp
Set rngJ = .Range("B1:B200").FindNext
Loop
Next
End With
End Sub