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

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"
<[email protected]>
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

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