Help entering a formula containing variables

[asmith4035]

I am new to VBA but working hard to learn
I used 2 InputBoxes to get 2 variables, Rows2Insert and InsertAfter. I want to use these variables in a relative formula in an active cell. My attempt, that doesn't work is:
ActiveCell.FormulaR1C1 = "=IF(RC[1]>=insertafter,RC[1]+Rows2Insert,RC[1])"
This is what goes into the cell
=IF(CQ3>=@insertafter,CQ3+@Rows2Insert,CQ3)
Thanks, alan

 

[RudRud]

Hi, While waiting for other experts, I will share my thought here

It would be good if you can share your case by posting a screenshot by install an add in (xlbb) in your excel

• Want to help your helpers by posting a small, copyable, screen shot directly in your post? XL2BB Instructions & Download (latest January 2021 v 2.0 )

Book1
	A	B	C
2	aa	2	4
3		3	3
Sheet1

Sub test()
Dim rows2insert, InsertAfter As Integer

rows2insert = InputBox("input rows2insert")
InsertAfter = InputBox("Input InsertAfter")

If ActiveCell.Value >= InsertAfter Then
    ActiveCell.Offset(0, 1) = ActiveCell.Value + rows2insert 'If active cell = b2 then result will be in c2 - Offset row 0 column 1 = C2 
Else
    ActiveCell.Offset(0, 1) = ActiveCell.Value
End If


End Sub

 

[RudRud]

Book1
	A	B	C
1	Rows2Insert	2
2	InsertAfter	2
3
4		ActiveCell Formula
5	2	4	4
6	1	1	1
7
Sheet2

Cell Formulas
Range	Formula
B5:B6	B5	=IF(A5>=$B$2,A5+$B$1,A5)

Sub test()
Dim rows2insert, InsertAfter As Integer

rows2insert = [b1].Value
InsertAfter = [b2].Value

For Each ss In Range("a5:a" & Cells(Rows.Count, "A").End(xlUp).Row)
        If ss.Value >= InsertAfter Then
            ss.Offset(0, 2).Value = ss.Value + InsertAfter
        Else
            ss.Offset(0, 2).Value = ss.Value
        End If
        
Next ss
End Sub

this one is dynamic loop version, it will start checking starting from a5 into last row

 

[Eric W]

If you're looking to insert a formula, try:

ActiveCell.FormulaR1C1 = "=IF(RC[1]>=" & InsertAfter & ",RC[1]+" & Rows2Insert & ",RC[1])"

Note how the 2 variables must be handled differently.

 

[asmith4035]

Hi, While waiting for other experts, I will share my thought here

It would be good if you can share your case by posting a screenshot by install an add in (xlbb) in your excel

• Want to help your helpers by posting a small, copyable, screen shot directly in your post? XL2BB Instructions & Download (latest January 2021 v 2.0 )

Book1
	A	B	C
2	aa	2	4
3		3	3
Sheet1

Sub test()
Dim rows2insert, InsertAfter As Integer

rows2insert = InputBox("input rows2insert")
InsertAfter = InputBox("Input InsertAfter")

If ActiveCell.Value >= InsertAfter Then
    ActiveCell.Offset(0, 1) = ActiveCell.Value + rows2insert 'If active cell = b2 then result will be in c2 - Offset row 0 column 1 = C2
Else
    ActiveCell.Offset(0, 1) = ActiveCell.Value
End If


End Sub

Thank you for your reply. I tried installing XL2BB and ran into a problem. I don't remember exactly what the problem was, my brain was scrabbled so I took a break. I will give your solution a shot and let you know what happens. Thank you again

 

[asmith4035]

Book1
	A	B	C
1	Rows2Insert	2
2	InsertAfter	2
3
4		ActiveCell Formula
5	2	4	4
6	1	1	1
7
Sheet2

Cell Formulas
Range	Formula
B5:B6	B5	=IF(A5>=$B$2,A5+$B$1,A5)

Sub test()
Dim rows2insert, InsertAfter As Integer

rows2insert = [b1].Value
InsertAfter = [b2].Value

For Each ss In Range("a5:a" & Cells(Rows.Count, "A").End(xlUp).Row)
        If ss.Value >= InsertAfter Then
            ss.Offset(0, 2).Value = ss.Value + InsertAfter
        Else
            ss.Offset(0, 2).Value = ss.Value
        End If
       
Next ss
End Sub

this one is dynamic loop version, it will start checking starting from a5 into last row

Thank You Rudud, I will give this a shot. I want to figure it out and understand it NOT just copy it into my workbook. My first understanding, it looks to me that instead of putting a formula in a cell and copying down, the macro examines one cell at a time and inserts a value in column C and moves down one row at a time? Is that close/
Thanks Again, I appreciate your time.

 

[asmith4035]

If you're looking to insert a formula, try:

ActiveCell.FormulaR1C1 = "=IF(RC[1]>=" & InsertAfter & ",RC[1]+" & Rows2Insert & ",RC[1])"

Note how the 2 variables must be handled differently.

Thank for your suggestion Eric W. It didnt seem to work. I got the message "Run-time error '104': and "Application defined or object defined error"
I don't know how to fix it.

 

[RudRud]

Thank You Rudud, I will give this a shot. I want to figure it out and understand it NOT just copy it into my workbook. My first understanding, it looks to me that instead of putting a formula in a cell and copying down, the macro examines one cell at a time and inserts a value in column C and moves down one row at a time? Is that close/
Thanks Again, I appreciate your time.

Hi @asmith4035 ,

The code will loop from cell a5 to the last cell of column A end xl up (which is 6 for now) .

Actually, there are other methods to go about it, depending on what you want to accomplish. I think other experts here will have a different perspective and provide you with additional options as well.

 

[Peter_SSs]

I tried installing XL2BB and ran into a problem. I don't remember exactly what the problem was

I suggest that you try again and if you still have a problem post a thread** in the About This Board forum and describe what the problem is and at what stage of the installation or usage of the Add-In you had the problem.

** Before starting a thread in About This Board review the "Stickies" at the top of the forum in case your issue is covered there.

 

[asmith4035]

I suggest that you try again and if you still have a problem post a thread** in the About This Board forum and describe what the problem is and at what stage of the installation or usage of the Add-In you had the problem.

** Before starting a thread in About This Board review the "Stickies" at the top of the forum in case your issue is covered there.

Thank You Peter_SSs, I will try again and follow your suggestions, paying more attention to record to the problem if I have one next time.