I have been able to get my range with the following:
Set SRng = ActiveSheet.UsedRange
Allthough I have to be carefull no errant data exists on the sheet or any
errant data had existed, even if a cell had its data deleted the above code
still finds that cell in the UsedRange?

From ActiveSheet.UsedRange I can then disect the values and set variables
for my range.

sRow = Right(SRng.Rows(x + 1).Address(0, 0), 3)
sRow = Replace(sRow, ":", "")

If Len(sRow) = "2" Then
sRow2 = Left(sRow, 1)
'MsgBox sRow2 'for testing
Else
If Len(sRow) = "3" Then
sRow2 = Left(sRow, 2)
sRow2 = Left(sRow2, 1)
'MsgBox sRow2 'for testing
End If
End If

Not the best, but its working. :fingers crossed: ;)

--
Regards

Rick
XP Pro
Office 2007



"Rick S." wrote:

I am not sure how to word the subject or the question.
In the code below I am iterating thru a range to test conditions. My
problem is the range will vary all the time. It will always start in "C5"
and the rows/Coulumns will always vary.

In this code "Set rRngCol = Range("C5", Range("g" & Rows.Count).End(xlUp))
'when finds empty cell, program ends?"
I am setting a fixed range, how can I make the range a variable dependant on
any worksheet? (I have the worksheet iteration completed, just working out
the cell iterations).

Begin code===================
Sub test3()
Dim Dif As Long
Dim rRngCol As Range
Dim i As Range 'range of cells
Dim sEmpName As String 'for Employee name in column A

'i.Address is cell address. example "C5"

Set rRngCol = Range("C5", Range("g" & Rows.Count).End(xlUp)) 'when finds
empty cell, program ends?

For Each i In rRngCol

If IsDate(i) = True Then
Dif = Date - i
If Dif "365" Then
MsgBox i & " 1+ year" & " " & i.Address & " " & sName
Else
If Dif "302" Then
MsgBox i & " 10+ months" & " " & i.Address
Else
End If
End If
End If
NextCell:
Next i
End Sub
End code=======================

--
Regards

Rick
XP Pro
Office 2007