Macro to add a formula into blank column until blank row?

R

rockdrigotoca

New Member
Joined
Aug 24, 2010
Messages
23
Hello everyone!

I would like to see if you can help me with this that I cannot manage to deal with it...

I have a macro that (thanks to Alpha Frog) consolidates and summarizes the debts and pastes this into a new spreadsheet. Then, I would like to put formulas on column H that will differentiate amounts of 10 or less, 10 or more... but only paste formulas UNTIL the last cell is blank... because I can receive one day 10 debts and another day 20... so to populate until there's no more data on the sheet...

Column A has the name
Column B the account
Col C other data
Column D has the Amount

I would like to input on Column H: =IF(D3<10,"Small Debt","Big Debt")

But rather than prepopulating the worksheet with 60K formulas, is there any way to put it with a macro until there is no more information on Column D?

Please?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Try:
Code: 
Sub DebtFormulaMacro ()
 
' Assuming by example below your first amount is in cell D3
Range("H3:H" & Range("D" & Rows.count).end(xlup).row).formulaR1C1 = _ 
"=IF(RC[-4],10,""Small debt"",""Big debt"")"
 
End Sub
 
Last edited:
Upvote 0
Hi JackDanIce!

Thanks for your quick response! Though I'm not able to make it work correctly...

It has Runtime error 1004: App-defined or Object defined error.

My first amount would be in cell D2, but that's just changing the 3 for 2.. .

I understand it is getting the range on H to the end, and the end is set by the Rows.Count and then enter in R1C1 the formula... but doesn't seem to work?

Can you help me please??

I ended up only changing the 2 but with the same result:

Sub DebtFormulaMacro()

' Assuming by example below your first amount is in cell D3
Range("H2:H" & Range("D" & Rows.Count).End(xlUp).Row).FormulaR1C1 = _
"=IF(RC[-4],10,""Small debt"",""Big debt"")"

End Sub
 
Upvote 0
This just worked for me, try:
Code: 
Sub DebtFormulaMacro ()
 Dim i as Long
i = Range("D" & Rows.Count).End(xlUp).Row
Range("H2:H" & i).formulaR1C1 = "=IF(RC[-4]<10,""Small debt"",""Big debt"")"
 
End Sub
 
Last edited:
Upvote 0
=IF(D3<10,"Small Debt","Big Debt")

What row would this formula appear on?

Just wondering if the row has to be offset.

What usually causes this error is if the formula you're trying to insert is not valid in some way.
 
Upvote 0
Found my mistake in the first one, a typo. This will work:
Rich (BB code): 
Sub DebtFormulaMacro ()

Range("H2:H" & Range("D" & Rows.count).end(xlup).row).formulaR1C1 = _ 
"=IF(RC[-4]<10,""Small debt"",""Big debt"")"
 
End Sub
 
Upvote 0
Great!!

I was still having issues, but the thing is that the formula had the "," rather than the "<"

Wicked!

Thanks JackDanIce!!

Weaver:

Thanks to your post i reviewed the formula and then got it working! Thanks also!
 
Upvote 0
I must have not pressed the SHIFT key when typing the < hence why it was , oh well, works now!
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Excel VBA - Return Specific Data from Master Sheet Based on Date Search + Duplicate Certain Items
Replies
1
Views
446
LookingForHelp1
L
N
  • Question
If type of Formula
Replies
4
Views
210
arthurbr
arthurbr
J
  • Solved
How to sum values in column D until a blank cell is reached and if values in column C equals "WP" or "BS"
Replies
9
Views
1K
Peter_SSs
Peter_SSs
V
  • Question
Copy Last column to Last row
Replies
3
Views
449
MARK858
MARK858
B
  • Solved
Delete rows based on blank cell; then copy formula to an entire column
2
Replies
12
Views
842
Skyybot
Skyybot

Forum statistics

Threads
1,215,048
Messages
6,122,862
Members
449,097
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top