cell contents

[Kevin]

Hi
the code below returns the address of a cell on a worksheet
what i want to do is return the cell address subtract 10 from address
and read the contents of the new cell address
ie cell address = F15
subtract 10 from this to give F5
F5 contains the value 6
i want to reurn 6 as the answer

Sub kevin()
Dim rng As Range
Dim cell As Range
Dim rngData As Range
Set rng = Range("D15:H15")
For Each cell In rng
If cell.Text <> "#N/A" Then
MsgBox cell.Address
Exit For
End If
Next

thanks

kevin

 

[Bob Phillips]

Sub kevin()
Dim rng As Range
Dim cell As Range
Dim rngData As Range
Set rng = Range("D15:H15")
For Each cell In rng
If cell.Offset(-10,0).Text <> "#N/A" Then
MsgBox cell.Offset(-10,0).Value
Exit For
End If
Next

but why not just do

Sub kevin()
Dim rng As Range
Dim cell As Range
Dim rngData As Range
Set rng = Range("D5:H5")
For Each cell In rng
If cell.Text <> "#N/A" Then
MsgBox cell.Value
Exit For
End If
Next

--

HTH

RP
(remove nothere from the email address if mailing direct)