Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill Error
Hi
I am trying to use the below code to add a calculated field to a report I am working on. Unfortunately an error is trigered on the autofill line. I am not sure what causes this but if in break mode i go into the sheet and select the cell and then run the code again it seems to work. Does anyone know why this happens? TIA Sub ZPOHeader() Dim iLRowHeader As Integer iLRowHeader = Sheets("ZPOHeader").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row - 1 'Update currency in ZPOHeader With Sheets("ZPOHeader") ..Range("N1").Value = "Eur Amount" ..Range("N2").Formula = "=M2*VLOOKUP(I2,Xrates!$A$2:$B$8,2,0)" ..Range("N2").AutoFill Destination:=Range("N2:N" & iLRowHeader) ..Columns(14).NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)" End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill Error
Edgar,
In my experience with Auto Fill, two cells (minimum) must be designated as the start range. Also, it is safer to declare Row variables as Long not Integer. Regards, Jim Cone San Francisco, USA ----- Original Message ----- From: "Edgar Thoemmes" Newsgroups: microsoft.public.excel.programming Sent: Wednesday, January 12, 2005 2:25 AM Subject: Autofill Error Hi I am trying to use the below code to add a calculated field to a report I am working on. Unfortunately an error is trigered on the autofill line. I am not sure what causes this but if in break mode i go into the sheet and select the cell and then run the code again it seems to work. Does anyone know why this happens? TIA Sub ZPOHeader() Dim iLRowHeader As Integer iLRowHeader = Sheets("ZPOHeader").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row - 1 'Update currency in ZPOHeader With Sheets("ZPOHeader") .Range("N1").Value = "Eur Amount" .Range("N2").Formula = "=M2*VLOOKUP(I2,Xrates!$A$2:$B$8,2,0)" .Range("N2").AutoFill Destination:=Range("N2:N" & iLRowHeader) .Columns(14).NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)" End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill Error
And try fully qualifying your ranges:
..Range("N2").AutoFill Destination:=Range("N2:N" & iLRowHeader) may work better as: ..Range("N2").AutoFill Destination:=.Range("N2:N" & iLRowHeader) Those leading dots are important! <vbg Edgar Thoemmes wrote: Hi I am trying to use the below code to add a calculated field to a report I am working on. Unfortunately an error is trigered on the autofill line. I am not sure what causes this but if in break mode i go into the sheet and select the cell and then run the code again it seems to work. Does anyone know why this happens? TIA Sub ZPOHeader() Dim iLRowHeader As Integer iLRowHeader = Sheets("ZPOHeader").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row - 1 'Update currency in ZPOHeader With Sheets("ZPOHeader") .Range("N1").Value = "Eur Amount" .Range("N2").Formula = "=M2*VLOOKUP(I2,Xrates!$A$2:$B$8,2,0)" .Range("N2").AutoFill Destination:=Range("N2:N" & iLRowHeader) .Columns(14).NumberFormat = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)" End With End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-time error '1004': AutoFill method of Range class failed | Excel Discussion (Misc queries) | |||
Run-time error '1004': AutoFill method of Range class failed | Excel Discussion (Misc queries) | |||
Autofill & On Error Resume Next | Excel Discussion (Misc queries) | |||
Q. Autofill question: Can I autofill alpha characters like I can numbers? | Excel Programming | |||
autofill macro has error when there is nothing to fill. | Excel Programming |