Macro auto fill formula

E

Erin

Having a quirky issue

I've set up a macro that runs fine on my system but isn't working on a
co-worker's system (and it had been working fine for a couple of months prior
to now). Here's the code:

Range("B11").Select
ActiveCell.FormulaR1C1 =
"=IF(LEFT(RC[-1],1)<""A"",IF(LEN(RC[-1])=14,LEFT(RC[-1],5),Left(RC[-1],4)),VLOOKUP(RC[-1],'[FA Vehicles PCard DO NOT DELETE.xls]Sheet 1'!C1:C2,2,FALSE))"
Range("b11").AutoFill Destination:=Range("b11:B" & endRow)
Range("b11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("b11:b" & endRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Basically, it copies the formula down the column. On her system, the
formula works in the first cell but then copies the result from the first
cell down -- not the formula, so the whole column has the same data.

We're using the same source data. I've tried reinstalling the macro on her
system but it's still not working. We have the same versions of Excel (2003)
and VB (6.5). The only thing that is different recently is that she's
learning macros and has been playing with the system (but hasn't touched this
macro -- even if she had, I've reinstalled it so this shouldn't be an issue).
I've tried deleting her personal workbook and reactivating it, but no luck.
Any ideas?
 
D

Don Guillett

Try it this way from anywhere in the workbook. Change your sheet names and
formula to suit.

Sub dolookup()
With Sheets("source")
For i = 1 To .Cells(Rows.Count, "b").End(xlUp).Row
.Cells(i, "c").Value = _
Application.VLookup(.Cells(i, "b"), _
Sheets("dest").Range("a1:B22"), 2, 0)
Next i
End With
End Sub
 
D

Dave Peterson

You code fills the range with formulas and then does a copy|paste values.

I'd comment out that .pastespecial line and see if there were really formulas
there.

If there are formulas in each cell, but each cell shows the same value, I'd
check to make sure that calculation is set to automatic--not manual.

tools|Options|calculation tab.
Having a quirky issue

I've set up a macro that runs fine on my system but isn't working on a
co-worker's system (and it had been working fine for a couple of months prior
to now). Here's the code:

Range("B11").Select
ActiveCell.FormulaR1C1 =
"=IF(LEFT(RC[-1],1)<""A"",IF(LEN(RC[-1])=14,LEFT(RC[-1],5),Left(RC[-1],4)),VLOOKUP(RC[-1],'[FA Vehicles PCard DO NOT DELETE.xls]Sheet 1'!C1:C2,2,FALSE))"
Range("b11").AutoFill Destination:=Range("b11:B" & endRow)
Range("b11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("b11:b" & endRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Basically, it copies the formula down the column. On her system, the
formula works in the first cell but then copies the result from the first
cell down -- not the formula, so the whole column has the same data.

We're using the same source data. I've tried reinstalling the macro on her
system but it's still not working. We have the same versions of Excel (2003)
and VB (6.5). The only thing that is different recently is that she's
learning macros and has been playing with the system (but hasn't touched this
macro -- even if she had, I've reinstalled it so this shouldn't be an issue).
I've tried deleting her personal workbook and reactivating it, but no luck.
Any ideas?
Click to expand...
 
E

Erin

Thanks so much! That was it -- the calculation was set to manual. We fixed
that and all is well -- thanks again!

Dave Peterson said:
You code fills the range with formulas and then does a copy|paste values.

I'd comment out that .pastespecial line and see if there were really formulas
there.

If there are formulas in each cell, but each cell shows the same value, I'd
check to make sure that calculation is set to automatic--not manual.

tools|Options|calculation tab.
Having a quirky issue

I've set up a macro that runs fine on my system but isn't working on a
co-worker's system (and it had been working fine for a couple of months prior
to now). Here's the code:

Range("B11").Select
ActiveCell.FormulaR1C1 =
"=IF(LEFT(RC[-1],1)<""A"",IF(LEN(RC[-1])=14,LEFT(RC[-1],5),Left(RC[-1],4)),VLOOKUP(RC[-1],'[FA Vehicles PCard DO NOT DELETE.xls]Sheet 1'!C1:C2,2,FALSE))"
Range("b11").AutoFill Destination:=Range("b11:B" & endRow)
Range("b11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("b11:b" & endRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Basically, it copies the formula down the column. On her system, the
formula works in the first cell but then copies the result from the first
cell down -- not the formula, so the whole column has the same data.

We're using the same source data. I've tried reinstalling the macro on her
system but it's still not working. We have the same versions of Excel (2003)
and VB (6.5). The only thing that is different recently is that she's
learning macros and has been playing with the system (but hasn't touched this
macro -- even if she had, I've reinstalled it so this shouldn't be an issue).
I've tried deleting her personal workbook and reactivating it, but no luck.
Any ideas?
Click to expand...
Click to expand...
 
D

Don Guillett

Did you try my simpler approach?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Erin said:
Thanks so much! That was it -- the calculation was set to manual. We
fixed
that and all is well -- thanks again!

Dave Peterson said:
You code fills the range with formulas and then does a copy|paste values.

I'd comment out that .pastespecial line and see if there were really
formulas
there.

If there are formulas in each cell, but each cell shows the same value,
I'd
check to make sure that calculation is set to automatic--not manual.

tools|Options|calculation tab.
Having a quirky issue

I've set up a macro that runs fine on my system but isn't working on a
co-worker's system (and it had been working fine for a couple of months
prior
to now). Here's the code:

Range("B11").Select
ActiveCell.FormulaR1C1 =
"=IF(LEFT(RC[-1],1)<""A"",IF(LEN(RC[-1])=14,LEFT(RC[-1],5),Left(RC[-1],4)),VLOOKUP(RC[-1],'[FA
Vehicles PCard DO NOT DELETE.xls]Sheet 1'!C1:C2,2,FALSE))"
Range("b11").AutoFill Destination:=Range("b11:B" & endRow)
Range("b11").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("b11:b" & endRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Basically, it copies the formula down the column. On her system, the
formula works in the first cell but then copies the result from the
first
cell down -- not the formula, so the whole column has the same data.

We're using the same source data. I've tried reinstalling the macro on
her
system but it's still not working. We have the same versions of Excel
(2003)
and VB (6.5). The only thing that is different recently is that she's
learning macros and has been playing with the system (but hasn't
touched this
macro -- even if she had, I've reinstalled it so this shouldn't be an
issue).
I've tried deleting her personal workbook and reactivating it, but no
luck.
Any ideas?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to Select text from a point given to a specific word 2
Macro/Formula help 5
Macro 3
Creating Pivot Table with Macro using Multiple Sheets 0
Vlookup error 1
conditonal formatting in VB 2
Using INDIRECT & R1C1 Ref style 5
smart tags 1

Top