Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Macro
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Macro
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 -- Don Guillett SalesAid Software "Michael M" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Macro
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 -- HTH... Jim Thomlinson "Michael M" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Macro
Gentlemen
Thank you for the responses and helpful comments. One day the penny might drop for me !!. Regards Michael M "Don Guillett" wrote: 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 -- Don Guillett SalesAid Software "Michael M" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Macro
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 "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "Michael M" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Macro
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 "Don Guillett" wrote: 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 -- Don Guillett SalesAid Software "Michael M" wrote in message ... 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 "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "Michael M" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Closing File Error | Excel Discussion (Misc queries) | |||
Formula for current month minus one = Quarter number in a macro. | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Macro Formula revision? | Excel Worksheet Functions |