Using an If statement in vba for multiple records in a range 2

[mrtauntaun]

Hi all. I am using the following bit of code:

Dim rngCalc As Range
Set rngCalc = Range("AD2:AD" & Cells(Rows.Count,1).End(xlUp).Row)
rngCalc.Formula = "=(8-K2)"
rngCalc.Value = rngCalc.Value

In this case, the first cell to be replaced is AD2 and it continues for as many rows as I have. What I want to do is use some If statements in conjuction with this, and have these if statements run for each cell (AD2, AD3, AD4, etc)
So in this case, if AD2 = 7 then xxxxxxxxxxxxxx
And so on. Im not sure how to word it. Should it be 'if rngcalc.value = 7'?
I tried that and got a type mismatch. Any help would be appreciated with this, thanks!!!!

 

[Rofeu]

Hi,

you'll have to loop through the cells within the range.

Something like this:

 Dim I as long
for I = 1 to rngCalc.cells.count
     if rngCalc.cells(I) = 7 then 
         'your code
     endif
next

Cheers,

Roel

 

[mrtauntaun]

Awesome, that did it. I love tektips.

Thanks Rofeu!

 

[Rofeu]

You're welcome. Thanx for the star.

 

[Zathras]

What I want to do is use some If statements in conjuction with this

You didn't specify if you wanted to set up different formulas based on the values found while setting up the formulas or if you want to do follow-on processing after changing all of the values with the same formula. In case it was the former, you can do that if you use R1C1 notation:

Sub test()
Dim rngCalc As Range
Dim c As Range
  Set rngCalc = Range("AD2:AD" & Cells(Rows.Count, 1).End(xlUp).Row)
  For Each c In rngCalc
    If c.Value = 7 Then
      c.FormulaR1C1 = "=(7-RC[-19])"
    Else
      c.FormulaR1C1 = "=(8-RC[-19])"
    End If
  Next c
End Sub

And you can still convert the formulas to values as you have done with

rngCalc.Value = rngCalc.Value

following the "Next c" line.

Or if you want to test for "7" after putting your formula in the cell it can be done like this:

Sub test()
Dim rngCalc As Range
Dim c As Range
  Set rngCalc = Range("AD2:AD" & Cells(Rows.Count, 1).End(xlUp).Row)
  For Each c In rngCalc
    c.FormulaR1C1 = "=(7-RC[-19])"
    If c.Value = 7 Then[COLOR=green]
      ' Do something...[/color]
    End If
  Next c
  rngCalc.Value = rngCalc.Value
End Sub

 

[firefytr]

Hi mrtauntaun,

When I gave you that code originally, I meant it to NOT have to loop, that was the entire purpose. Why now add a loop? Why not just build it into your formula? ...

Dim rngCalc As Range
Set rngCalc = Range("AD2:AD" & Cells(Rows.Count,1).End(xlUp).Row)
rngCalc.Formula = "=IF(AD2=7,(8-K2),0)" 'set formula here
rngCalc.Value = rngCalc.Value

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Zathras]

Zack, did you test that code? It generates circular references.

That's why the loop is now necessary. The cell value needs to be tested before it is changed. (At least it does the way I read mrtauntaun's requirements.)

 

[mrtauntaun]

Hi guys. And here I thought Math not Zathras skill
Fireftyr, originally I did not need it to loop. Just to perform a simple formula. However, I have since been asked to add a lot of new criteria to the calculation.
Also, it is the result of that formula that needs the if, not just if it equals 7. In this case.
Thank you all for your input!

 

[firefytr]

Then add it to a new column. Even changing columns once or twice is better than looping. Wherever we can take out loops, we'll generally and usually save time and efficiency.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci