Formula Macro

[Michael M]

Hi all
I really don't seem to be able to come to grips with VBA
I have copied this formula verbatim from a VBA text book and it doesn't work
for me.
Can someone please give me some guidance as to where I am going wrong.


Sub CopyFormula()
Dim FinalRow As Long
FinalRow = Range("F65536").End(xlUp).Row
Range("F14").Formula = "=IF(OR(D14="",D14=0),"",G14/D14)"
Range("F14").Copy Destination:=Range("F15:F" & FinalRow)
End Sub

Regards to all that may help
Michael M

 

[Don Guillett]

Modify this to suit. You want to use a different column for the last row.

Sub balance()
Set frng = Range("f14:f" & Cells(Rows.Count, "a").End(xlUp).Row)
With frng
.Formula = "=h7+d8"
.Formula = .Value ' to convert to a value
End With
End Sub

 

[Jim Thomlinson]

For a quick translation here is what that macro is doing... It is finding the
last populated cell in column F by going to Cell F65536 and then moving up
from there. It then goes to Cell F14 and puts a formula in there... it copies
that formula and pastes it into Cells F15 to the last populated Cell in F...
That seems odd to me unless you are just rying to overwrtie existing data in
F the last cell found will probably be something like F13... Give this a
try... It uses column D to determine the last row.

Sub CopyFormula()
Dim FinalRow As Long
FinalRow = cells(rows.count, "D").End(xlUp).Row 'Use D to determine last row
Range("F14").Formula = "=IF(OR(D14="",D14=0),"",G14/D14)"
Range("F14").Copy Destination:=Range("F15:F" & FinalRow)
End Sub

 

[Michael M]

Gentlemen
Thank you for the responses and helpful comments.
One day the penny might drop for me !!.

Regards
Michael M

 

[Michael M]

Gents again
I have tried both examples of the code and both give me a "runtime error 1004"

Jim, I understand what the macro is doing, as I am importing data into a new
sheet then inserting formulae over what was previously, value only data.
But it's good to know it makes sense !!

Regards
Michael M

 

[Don Guillett]

try mine with this modification

Sub balance()
Set frng = Range("f14:f" & Cells(Rows.Count, "F").End(xlUp).Row) 'or "D"
With frng
.Formula ="=IF(OR(D14="",D14=0),"",G14/D14)"
.Formula = .Value ' to convert to a value
End With
End Sub

 

[Michael M]

Don
Before I checked my mail, we came up with this version that works.

Sub CopyFormula()
Dim FinalRow As Long
FinalRow = Cells(Rows.Count, "F").End(xlUp).Row 'Use D to determine last row
Worksheets("PRICE CULVERT OPTION").Range("F13").Formula =
"=IF(OR(D13="""",D13=0),"""",G13/D13)"
Worksheets("PRICE CULVERT OPTION").Range("F13").Copy
Destination:=Range("F14:F" & FinalRow)
End Sub

Yours is obviously a lot simpler and therefore more efficient, so looks like
it will get the vote.
I thank you, and all of your fellow users for assisting in this ongoing
little project of mine.

Regards
Michael M