Thread
:
Macro auto fill formula
View Single Post
#
2
Posted to microsoft.public.excel.worksheet.functions
Don Guillett
external usenet poster
Posts: 10,124
Macro auto fill formula
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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Erin" wrote in message
...
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?
Reply With Quote
Don Guillett
View Public Profile
Find all posts by Don Guillett