Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB copy formula and auto fill a range
I am adding a formula residing in "Targeting Parameters" into
"DrListWorkCopy" using autofil into a whole column. When I compile the macro, I got an error on the auto fill line saying" autofill method of Range class failed. Would appreciate some help. My Codes: Public Sub CopyFormulaDrListWorkCopy() Dim Target1Wks As Worksheet, Source1Wks As Worksheet Dim MBC As Range Dim LR As Long, FillRange As Range Set Target1Wks = Sheets("DrListWorkCopy") Set Source1Wks = Sheets("Targeting Parameters") With Source1Wks Set MBC = Source1Wks.Range("D59") End With With Target1Wks LR = Range("A65536").End(xlUp).Row Set FillRange = Target1Wks.Range(Cells(3, 12), Cells(LR, 12)) MBC.AutoFill FillRange End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB copy formula and auto fill a range
Instead of
MBC.AutoFill FillRange try FillRange.formular1c1 = MBC.formular1c1 HTH. Best wishes Harald "Blubber" wrote in message ... I am adding a formula residing in "Targeting Parameters" into "DrListWorkCopy" using autofil into a whole column. When I compile the macro, I got an error on the auto fill line saying" autofill method of Range class failed. Would appreciate some help. My Codes: Public Sub CopyFormulaDrListWorkCopy() Dim Target1Wks As Worksheet, Source1Wks As Worksheet Dim MBC As Range Dim LR As Long, FillRange As Range Set Target1Wks = Sheets("DrListWorkCopy") Set Source1Wks = Sheets("Targeting Parameters") With Source1Wks Set MBC = Source1Wks.Range("D59") End With With Target1Wks LR = Range("A65536").End(xlUp).Row Set FillRange = Target1Wks.Range(Cells(3, 12), Cells(LR, 12)) MBC.AutoFill FillRange End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB copy formula and auto fill a range
Hi,
If I understand correctly what you are trying to do then the problem is that you can't start an autofill at a non adjacent cell to the start cell. You need to copy the first cell and then autofill from that cell. With Target1Wks LR = Range("A65536").End(xlUp).Row Target1Wks.Select Set FillRange = Target1Wks.Range(Cells(3, 12), Cells(LR, 12)) MBC.Copy Destination:=FillRange.Cells(1, 1) 'Copy first cell FillRange.Cells(1, 1).AutoFill FillRange 'Autofill from the first cell End With -- Regards, OssieMac "Blubber" wrote: I am adding a formula residing in "Targeting Parameters" into "DrListWorkCopy" using autofil into a whole column. When I compile the macro, I got an error on the auto fill line saying" autofill method of Range class failed. Would appreciate some help. My Codes: Public Sub CopyFormulaDrListWorkCopy() Dim Target1Wks As Worksheet, Source1Wks As Worksheet Dim MBC As Range Dim LR As Long, FillRange As Range Set Target1Wks = Sheets("DrListWorkCopy") Set Source1Wks = Sheets("Targeting Parameters") With Source1Wks Set MBC = Source1Wks.Range("D59") End With With Target1Wks LR = Range("A65536").End(xlUp).Row Set FillRange = Target1Wks.Range(Cells(3, 12), Cells(LR, 12)) MBC.AutoFill FillRange End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB copy formula and auto fill a range
That worked like a charm thanks. I now made slight changes to similarly
populate the other columns with formulas. I hit a snag getting the same error message. I cant seem to see where went wrong... kindly help. My new codes are as follows: Public Sub CopyFormulaDrListWorkCopy() Dim Target1Wks As Worksheet, Source1Wks As Worksheet Dim MBC As Range, AT As Range, MNC As Range, Influ As Range, Third As Range Dim FRng1 As Range, FRng2 As Range, FRng3 As Range, FRng4 As Range, Frng5 As Range Dim LR As Long Set Target1Wks = Sheets("DrListWorkCopy") Set Source1Wks = Sheets("Targeting Parameters") With Source1Wks Set MBC = .Range("D59") Set AT = .Range("D60") Set MNC = .Range("D61") Set Influ = .Range("D62") Set Third = .Range("D63") End With With Target1Wks LR = Range("A65536").End(xlUp).Row Set FRng1 = .Range("L3:L" & LR) Set FRng2 = .Range("O3:O" & LR) Set FRng3 = .Range("Q3:Q" & LR) Set FRng4 = .Range("W3:W" & LR) Set Frng5 = .Range("Z3:Z" & LR) MBC.Copy Destination:=FRng1.Cells(1, 1) FRng1.Cells(1, 1).AutoFill FillRange AT.Copy Destination:=FRng2.Cells(1, 1) FRng2.Cells(1, 1).AutoFill FillRange MNC.Copy Destination:=FRng3.Cells(1, 1) FRng3.Cells(1, 1).AutoFill FillRange Influ.Copy Destination:=FRng4.Cells(1, 1) FRng4.Cells(1, 1).AutoFill FillRange Third.Copy Destination:=FRng1.Cells(1, 1) Frng5.Cells(1, 1).AutoFill FillRange End With End Sub "OssieMac" wrote: Hi, If I understand correctly what you are trying to do then the problem is that you can't start an autofill at a non adjacent cell to the start cell. You need to copy the first cell and then autofill from that cell. With Target1Wks LR = Range("A65536").End(xlUp).Row Target1Wks.Select Set FillRange = Target1Wks.Range(Cells(3, 12), Cells(LR, 12)) MBC.Copy Destination:=FillRange.Cells(1, 1) 'Copy first cell FillRange.Cells(1, 1).AutoFill FillRange 'Autofill from the first cell End With -- Regards, OssieMac "Blubber" wrote: I am adding a formula residing in "Targeting Parameters" into "DrListWorkCopy" using autofil into a whole column. When I compile the macro, I got an error on the auto fill line saying" autofill method of Range class failed. Would appreciate some help. My Codes: Public Sub CopyFormulaDrListWorkCopy() Dim Target1Wks As Worksheet, Source1Wks As Worksheet Dim MBC As Range Dim LR As Long, FillRange As Range Set Target1Wks = Sheets("DrListWorkCopy") Set Source1Wks = Sheets("Targeting Parameters") With Source1Wks Set MBC = Source1Wks.Range("D59") End With With Target1Wks LR = Range("A65536").End(xlUp).Row Set FillRange = Target1Wks.Range(Cells(3, 12), Cells(LR, 12)) MBC.AutoFill FillRange End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB copy formula and auto fill a range
Oops, Found my typo mistake.... Forgot to change FillRange to FRng1.......
Thanks again. "Blubber" wrote: That worked like a charm thanks. I now made slight changes to similarly populate the other columns with formulas. I hit a snag getting the same error message. I cant seem to see where went wrong... kindly help. My new codes are as follows: Public Sub CopyFormulaDrListWorkCopy() Dim Target1Wks As Worksheet, Source1Wks As Worksheet Dim MBC As Range, AT As Range, MNC As Range, Influ As Range, Third As Range Dim FRng1 As Range, FRng2 As Range, FRng3 As Range, FRng4 As Range, Frng5 As Range Dim LR As Long Set Target1Wks = Sheets("DrListWorkCopy") Set Source1Wks = Sheets("Targeting Parameters") With Source1Wks Set MBC = .Range("D59") Set AT = .Range("D60") Set MNC = .Range("D61") Set Influ = .Range("D62") Set Third = .Range("D63") End With With Target1Wks LR = Range("A65536").End(xlUp).Row Set FRng1 = .Range("L3:L" & LR) Set FRng2 = .Range("O3:O" & LR) Set FRng3 = .Range("Q3:Q" & LR) Set FRng4 = .Range("W3:W" & LR) Set Frng5 = .Range("Z3:Z" & LR) MBC.Copy Destination:=FRng1.Cells(1, 1) FRng1.Cells(1, 1).AutoFill FillRange AT.Copy Destination:=FRng2.Cells(1, 1) FRng2.Cells(1, 1).AutoFill FillRange MNC.Copy Destination:=FRng3.Cells(1, 1) FRng3.Cells(1, 1).AutoFill FillRange Influ.Copy Destination:=FRng4.Cells(1, 1) FRng4.Cells(1, 1).AutoFill FillRange Third.Copy Destination:=FRng1.Cells(1, 1) Frng5.Cells(1, 1).AutoFill FillRange End With End Sub "OssieMac" wrote: Hi, If I understand correctly what you are trying to do then the problem is that you can't start an autofill at a non adjacent cell to the start cell. You need to copy the first cell and then autofill from that cell. With Target1Wks LR = Range("A65536").End(xlUp).Row Target1Wks.Select Set FillRange = Target1Wks.Range(Cells(3, 12), Cells(LR, 12)) MBC.Copy Destination:=FillRange.Cells(1, 1) 'Copy first cell FillRange.Cells(1, 1).AutoFill FillRange 'Autofill from the first cell End With -- Regards, OssieMac "Blubber" wrote: I am adding a formula residing in "Targeting Parameters" into "DrListWorkCopy" using autofil into a whole column. When I compile the macro, I got an error on the auto fill line saying" autofill method of Range class failed. Would appreciate some help. My Codes: Public Sub CopyFormulaDrListWorkCopy() Dim Target1Wks As Worksheet, Source1Wks As Worksheet Dim MBC As Range Dim LR As Long, FillRange As Range Set Target1Wks = Sheets("DrListWorkCopy") Set Source1Wks = Sheets("Targeting Parameters") With Source1Wks Set MBC = Source1Wks.Range("D59") End With With Target1Wks LR = Range("A65536").End(xlUp).Row Set FillRange = Target1Wks.Range(Cells(3, 12), Cells(LR, 12)) MBC.AutoFill FillRange End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto-populate, Auto-copy or Auto-fill? | Excel Worksheet Functions | |||
Range Names & Auto Fill | Excel Worksheet Functions | |||
Set default in auto fill options, I always want to copy cell, som. | Excel Discussion (Misc queries) | |||
I Need a Formula to Auto-fill Phone Numbers in a Range | Excel Worksheet Functions | |||
Error using Auto Fill of the class range | Excel Programming |