VBA Variables....

[MarkHear1]

I am still having problems with my varaibles. Below is the code i have
written, can anybody offer any help as to why it is not working?

Dim BottomRow As Range
Set BottomRow = ActiveCell

Range("a2").Select
Dim Beginning As Range
Set Beginning = ActiveCell

Selection.AutoFill Destination:=Range(Beginning, BottomRow.Offset(0,
-1)), Type:=xlFillDefault

Many Thanks,
Mark

 

[Bob Phillips]

Mark,

It worked for me. What are you getting?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[MarkHear1]

Mark,

It worked for me. What are you getting?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)










- Show quoted text -

Hummmmmm very odd. The error I am getting is as follows...
Run-Time error '1004':
AutoFill method of Range class failed.


- Mark

 

[Bob Phillips]

What is the data you are trying to fill-down?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[MarkHear1]

What is the data you are trying to fill-down?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)








- Show quoted text -

It's a formula ...
Range("a2").Value = "=VLOOKUP(B2,'download.csv'!$A$2:$J$5000,10,0)"

- mark

 

[Don Guillett]

Your problem appears to be that you are trying to offset a1 -1 column.

Try this to fill down from a2 to the activecell in column A

Sub filldowntoactivecell()
range("a2:a"&activecell.Row).filldown
End Sub

 

[MarkHear1]

Your problem appears to be that you are trying to offset a1 -1 column.

Try this to fill down from a2 to the activecell in column A

Sub filldowntoactivecell()
range("a2:a"&activecell.Row).filldown
End Sub

--
Don Guillett
SalesAid Software









- Show quoted text -

I tested that code, and it doesn't copy the value in a2 down - do you
have any other suggestions?

 

[Don Guillett]

I also tested before I sent and it does copy a2 down to the ACTIVECELL row.
So, if you select row 10 in ANY column it will copy a2 down to a10.

 

[MarkHear1]

I also tested before I sent and it does copy a2 down to the ACTIVECELL row.
So, if you select row 10 in ANY column it will copy a2 down to a10.
--
Don Guillett
SalesAid Software






- Show quoted text -

My apologies - i just retested this and it does work however, on my
spreadsheet i have got an autofilter set up, and when this is
configured as it needs to be cell a2 is not visible, and this prevents
the code from working...

 

[Don Guillett]

It appears that you want to copy down to the last row in col B so I would
use

Sub filldowntoactivecell()
lrinB=cells(rows.count,"b").end(xlup).row
range("a2:a"&lrinB).filldown
End Sub

 

[MarkHear1]

It appears that you want to copy down to the last row in col B so I would
use

Sub filldowntoactivecell()
lrinB=cells(rows.count,"b").end(xlup).row
range("a2:a"&lrinB).filldown
End Sub

--
Don Guillett
SalesAid Software







- Show quoted text -

That's correct.
And that code works a treat.
Thank you very much for your help.

 

[Dave Peterson]

Where's the activecell when you start this procedure?

Maybe...

Set BottomRow = ActiveCell.entirerow.cells(1) 'column A
Set Beginning = range("a2")

Beginning.autofill _
Destination:=Range(Beginning, BottomRow)), Type:=xlFillDefault

=====
Another option:

Range("a2:a" & activecell.row).formula _
= "=VLOOKUP(B2,'download.csv'!$A$2:$J$5000,10,0)"

 

[Don Guillett]

Glad you got it sorted out.