Close on VBA Autofill

[aivoryuk]

Hi I have th following VBA code

Sub Macro1()
'
' Macro1 Macro
'
Dim lr As Long
lr = Range("A2000").End(xlUp).Row
Range("chtname").AutoFill Destination:=Range("B1:B" & lr)
End Sub

What this does is autofill the cells in column B (which is named range chtname) based on the range of column A.

This works as it is but rather than use the range A2000 I want to use another named range "chtfirst" as the reference column.
I also can't get the VBA to work ("B1:B" & lr) using the named range "chtname"

Any ideas?

 

[GlennUK]

Before anything else, what is the definition of "chtname"?

 

[Weaver]

What's in cell B1? Is it text or a formula?

If you want to write to a range, it might be easier to avoid using autofill, but that depends on what you're autofilling with.

 

[aivoryuk]

Hi all
thanks for the replies

chtname refers to the column B where the formula I want to autofill

I tried writing the "B1:B" as chtname but this did not work.

I also wanted to write the "A2000" as a named range but could not get this to work.

 

[GlennUK]

Hi all
thanks for the replies

chtname refers to the column B where the formula I want to autofill
I tried writing the "B1:B" as chtname but this did not work.

I also wanted to write the "A2000" as a named range but could not get this to work.

So, what is the actual definition? Paste it here, don't describe it in words!

 

[aivoryuk]

So, what is the actual definition? Paste it here, don't describe it in words!

Is this what you mean

=OFFSET('sheet1'!$B$2,0,0,COUNTA('sheet1'!$B:$B),1)

ps I have tried what I originaly wrote on my actual datasheet and it doesn't work properley as when it does the autofill it misses out the first cell (prob due to the fact the I ahve to go from B2:b as using B1:B gets an error message saying the autofill method of range class failed)

 

[GlennUK]

Does this do what you want:

Sub Macro1()
'
' Macro1 Macro
'
Dim lr As Long
lr = Range("A2000").End(xlUp).Row
Range("B1").AutoFill Destination:=Range("B1:B" & lr)
End Sub

?

 

[aivoryuk]

Does this do what you want:

Sub Macro1()
'
' Macro1 Macro
'
Dim lr As Long
lr = Range("A2000").End(xlUp).Row
Range("B1").AutoFill Destination:=Range("B1:B" & lr)
End Sub

?

now that I have tested it a bit more no it doesn't as when I created it B1 was a numeric value. in my actual sheet B1 is the column header so I have to set it to B2:B which then when the macro the autofill misses the first cell

 

[Norie]

The reason you are getting the error is probably because you can't copy from one column to a different column.

I think it's been asked already but what is it you are trying to autofill?

A formula? A value?

Where do you want to copy from and where to?

Also, why do you want all these named ranges?

 

[Dartagnan]

I must say I am lost. How can you autofill on just one value? With only one cell doesn't Excel just copy the cell. I thought autofill needed at least 2 cells to autofill down a column or across a row.