Pulling formulas down.

[Peggy2015]

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

 

[JazzSP8]

Is there a reason you're trying to do this with code?

Looks like a formula would do?

=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...

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)

 

[Peggy2015]

Is there a reason you're trying to do this with code?

Looks like a formula would do?

=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...

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

 

[JazzSP8]

Fair enough! - Glad I could help