How to Evaluate formula results in VBA

[Guest]

Hello,

I am trying to add VBA code in Excel to evaluate the results of a Formula in
a specific cell and then to use the result in an If statement.
The macro loops thru each row, and I want to compare the formula result in a
specific cell to see if the result is equal to 0. If the result is equal to 0
then I want to change the value of the cell to a text string.

Here is what I have so far.

Dim intRows As Integer
intRows = Range("C1").CurrentRegion.Rows.Count
'Loop through all rows
For i = 2 To intRows
If Application.Evaluate("AJ & i") = 0 Then
Range("AJ" & i).Value = "USED"
End If

Thanks in advance for your help.

Jeff

 

[Guest]

Why not just simply

If Range("AJ" & i) = 0 then
Range("AJ" & i) = "USED"
End If

I didn't specify .Value because .Value is the default property anyhow. Oh -
and don't forget the Next to close the For loop.

 

[Guest]

I tried what you suggested and I get the following error:
Run time error 13 - type mismatch

It errors on the line:
If Range("AJ" & i) = 0 Then

Any ideas?

 

[Guest]

Try changing the type for intRows to Long vs Integer, I don't know how large
your range is, but with potentially over 65,000 rows, an Integer won't hack
the mission. To check if that's what happening, when it errors out, go into
debug and find out value of i at that point, either by hovering over it or
going into the Immediate window and using Print i to see what it is.

I just ran this code against a few rows, some with zero in them, some with
words "NOT USED" (so I could make sure they weren't getting overwritten) and
even some completely empty cells, and all with zeros or that were empty ended
up with "USED" in them. Matter of fact I even changed the For I = 1 to 11 to
'For I = 1 to Rows.Count' and it ran fine - now I have a column of 65536
"USED" entries ;-)

Sub Testing()
Dim I As Long
For I = 1 To 11
If Range("AJ" & I) = 0 Then
Range("AJ" & I) = "USED"
End If
Next
End Sub

 

[Guest]

Oh, if value of 'i' seems acceptable, go take a look on the worksheet itself
at the cell that is being pointed to and see what is in it. Might be
something special or unexpected.

 

[Guest]

Got it! It was the value in the cell itself. I did not have ISERROR in the
function so it was trying to evaluate an error code. Once I fixed the error
code it works. Thanks for your help.