Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Evaluate formula results in VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Evaluate formula results in VBA
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. "Fourtrax" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Evaluate formula results in VBA
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? "JLatham" wrote: 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. "Fourtrax" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Evaluate formula results in VBA
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 "Fourtrax" wrote: 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? "JLatham" wrote: 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. "Fourtrax" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Evaluate formula results in VBA
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. "Fourtrax" wrote: 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? "JLatham" wrote: 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. "Fourtrax" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Evaluate formula results in VBA
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. "JLatham" wrote: 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. "Fourtrax" wrote: 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? "JLatham" wrote: 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. "Fourtrax" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to evaluate dates | Excel Worksheet Functions | |||
Might be a bug in Evaluate Formula? | Excel Discussion (Misc queries) | |||
What is evaluate formula? | Excel Worksheet Functions | |||
Formula Will Not Evaluate | Excel Worksheet Functions | |||
Evaluate formula using VBA | Excel Discussion (Misc queries) |