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

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

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