View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Greg Wilson Greg Wilson is offline
external usenet poster
 
Posts: 747
Default Loop or condition? To check down the rows

I hard coded the column as "1" instead of "col -2" in the second part of the
range definition by mistake. My testing was in column A so I didn't pick up
on it:

Set r = ws.Range(ws.Cells(rw + 1, col - 2), _
ws.Cells(Rows.Count, 1).End(xlUp))

Should be:

Set r = ws.Range(ws.Cells(rw + 1, col - 2), _
ws.Cells(Rows.Count, col - 2).End(xlUp))


Does the following corrected code help?

Sub Test()
Dim r As Range, c As Range
Dim rw As Long, col As Long
Dim ws As Worksheet

Set ws = ActiveSheet
rw = ActiveCell.Row
col = ActiveCell.Column
Set r = ws.Range(ws.Cells(rw + 1, col - 2), _
ws.Cells(Rows.Count, col - 2).End(xlUp))
For Each c In r.Cells
If Not IsEmpty(c) Then c(0, 3).FormulaR1C1 = _
"=EXACT(RC[-1],R[1]C[-1])"
Next
End Sub

Regards,
Greg