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.

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

--
HTH...

Jim Thomlinson


"Labrat" wrote:

> 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.

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" wrote:

> 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.

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

Thanks again!!

"Jim Thomlinson" wrote:

> 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
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Labrat" wrote:
>
> > 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.