Pulling formulas down.

Peggy2015

Board Regular
Joined
Oct 19, 2015
Messages
109
Hello,

How do I write a code that pulls down formula in column D and stops after searching for information when it reaches "Report Total:".

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Cash[/TD]
[TD][/TD]
[TD][insert formula][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cash[/TD]
[TD][/TD]
[TD][insert formula][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cheque[/TD]
[TD][/TD]
[TD][insert formula][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EFT[/TD]
[TD][/TD]
[TD][insert formula][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EFT[/TD]
[TD][/TD]
[TD][insert formula][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EFT[/TD]
[TD][/TD]
[TD][insert formula][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EFT[/TD]
[TD][/TD]
[TD][insert formula][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Report Total:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




















Can you please point me in the direction of how to pull down a formula. What I am trying to achieve is if the cell in column A has "Cash", "Cheque", or "EFT" in it then put the formula in column C for that row. There should be a formula in C2:C9, minus the rows with blanks. I tried Range (Range ("c2"), Range ("c2").end(xlDown). However, the formula populated every line in column C to my favourite place ~ row 1048576.
Here's what I have written since:
'Select Start of range
Range("c2").Select

'Look up formula to pull in to spreadsheet to prepare journal.
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-3],'[Uniware GL Codes.xlsx]Uniware Codes'!C1:C20,2,FALSE)"

'This sets up the whole process of pulling the formula down.
Columns("A:A").Select
'this finds if the column is not blank.
Selection.Find(What:="Cash", After:=Range("a1"), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Select
'this starts the IF function - If the active cell is not blank,....
While ActiveCell.Value <> ""
'this places the formula in the column.
Range("c2").FormulaR1C1 = _
"=VLOOKUP(RC[-3],'[Uniware GL Codes.xlsx]Uniware Codes'!C1:C20,2,FALSE)"

It seems to be stuck in a virtual loop and I cannot figure out how to move on the look at populating K3 with the formula.

Thanks for your help.

Best wishes,
Peggy
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Is there a reason you're trying to do this with code?

Looks like a formula would do?

Code:
=IF(OR(A2="Cash",A2="Cheque",A2="EFT"),VLOOKUP(A2,[Uniware GL Codes.xlsx]Uniware Codes'!C1:C20,2,FALSE),"")

If you do need code then this would be my approach...

Code:
Sub AddFormula()

LastRow = Range("A65536").End(xlUp).Row

For x = 2 To LastRow
    If Cells(x, "A").Value <> "" Then
        Range("C" & x).Select
        ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],'[Uniware GL Codes.xlsx]Uniware Codes'!C1:C20,2,FALSE)"
    End If
Next x

End Sub

(Not tested)
 
Upvote 0
Is there a reason you're trying to do this with code?

Looks like a formula would do?

Code:
=IF(OR(A2="Cash",A2="Cheque",A2="EFT"),VLOOKUP(A2,[Uniware GL Codes.xlsx]Uniware Codes'!C1:C20,2,FALSE),"")

If you do need code then this would be my approach...

Code:
Sub AddFormula()

LastRow = Range("A65536").End(xlUp).Row

For x = 2 To LastRow
    If Cells(x, "A").Value <> "" Then
        Range("C" & x).Select
        ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],'[Uniware GL Codes.xlsx]Uniware Codes'!C1:C20,2,FALSE)"
    End If
Next x

End Sub

(Not tested)

Hello,
Thank you for your reply and help with the code on this! It works like a charm! I am putting together a workbook that will automate the process because the users are not very familiar with excel or formulas.

Best wishes,
Peggy
 
Upvote 0

Forum statistics

Threads
1,224,585
Messages
6,179,705
Members
452,939
Latest member
WCrawford

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