actual value of cell, not reference

[Labrat]

I have a macro that replaces values below a number set by the user to "ND".
The problem is it doesn't work when the cell contains a reference to another
cell or workbook.
Is there a way to get the absolute value of the cell and ignore the reference?

Here is what I have so far:

Sub ND()
'
'
Dim rng As Range

a = InputBox("Enter a value." & vbNewLine & "Any values below this will be
replaced by ND", "ND Replace")

If a = "" Or IsNumeric(a) = False Then

Exit Sub

End If
For Each rng In Selection
If Val(rng.Value) < a Or rng.Value = "" Then
rng.Replace what:=rng.Value, replacement:="ND"

End If
Next rng


End Sub

Any help would be appreciated.
Thanks.

 

[Jim Thomlinson]

Sub ND()
'
'
Dim rng As Range

a = InputBox("Enter a value." & vbNewLine & "Any values below this will be
replaced by ND", "ND Replace")

If a = "" Or IsNumeric(a) = False Then

Exit Sub

End If
For Each rng In Selection
If Val(rng.Value) < a Or rng.Value = "" Then
rng.Value ="ND"
End If
Next rng

End Sub

 

[Mike H]

Hi,

I think your trying to do this

Sub ND()
Dim rng As Range
a = CLng(InputBox("Enter a value." & vbNewLine & _
"Any values below this will be replaced by ND", "ND Replace"))
If a = vbNullString Or Not IsNumeric(a) Then
Exit Sub
End If

For Each rng In Selection
If rng.Value < a Or rng.Value = "" Then
rng.Replace what:=rng.Value, replacement:="ND"
End If
Next rng
End Sub



Mike

 

[Labrat]

Thanks!! That was fast. Problem solved.
It seems I was once again over-complicating things.

Thanks again!!