Autofill Error

[Guest]

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

 

[Jim Cone]

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

 

[Dave Peterson]

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>