How to use an IF formula in VBA

[lmichaluk]

Hi there - very new to excel and trying to write a macro that will sum the cells above and then will not let the answer exceed a given amount. For example: the "sum macro" would be something like
Sub b8()
Range("b8").Formula = "=b6+b7"
End Sub

The "If" statement would be something like =IF(B8<100.1,B8,100)

So. How do I include the If statement in the macro?
Thanks

 

[VoG]

Try

Range("B8").Formula = "=MIN(100,B6+B7)"

 

[vijayendra.shukla]

Assign a new variable iSum = b6+b7

If iSum < 100.1 Then
Range("b8").Formula = iSum ' Or write like "=b6+b7"
Else
Range("b8").Formula = 100
End If

 

[lmichaluk]

Try

Range("B8").Formula = "=MIN(100,B6+B7)"

Brilliant. Thanks.

 

[lmichaluk]

Brilliant. Thanks.

Sorry - one more question. Is there a way, in the macro, that I can allow for the user to add a row; i.e. the first range is B6+B7; if the user adds two more rows, how do I configure the macro so that it will capture B6+B7+B8+B9...if it was autosum I would use B6:B9. How would that work here?

 

[VoG]

Try like this

Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
Range("B" & LR + 1).Formula = "=MIN(100,B6+B" & LR & ")"

 

[lmichaluk]

Try like this

Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
Range("B" & LR + 1).Formula = "=MIN(100,B6+B" & LR & ")"

Thanks Peter - just tried that and I get a "compile error - Invalid outside procedure" with the (x1Up) shaded.
Linda

 

[VoG]

It is xlUp not X1Up.

Copy and paste what I gave you.

 

[lmichaluk]

Thanks for your help Peter. I did copy and paste, but that is the error I get...I also just tried to to copy the statement from your second note, but it automatically changes to the 1. Is there a symbol key or something that I should use? I notice that your note as displayed on my system shows a 1 as well. Wierd...
Thanks again for persevering.
Linda

 

[VoG]

Well, that is a new one on me. Do you have a sub called 1 or l? Or any Auto text feature like that?

Anyway it has to be the letter L not the number 1.