lookup isn't working

[Guest]

Below is the code I am using to lookup a value from a table in another
workbook. I intend for the lookup to be in cell f20, and the value then be
known as "mysmooth". When stepping through the macro, I can get to the
range("h" & mysmooth . . . step. However, the previous step is resulting
in a #NAME? and therefore the following code does not work. I can not figure
out why I am getting the #NAME?. Any help would be much appreciated.

TIA

Dim myrange As Range
Range("a2").Select
Selection.CurrentRegion.Select
Set myrange = Range("a2").CurrentRegion
numberrows = myrange.Rows.Count
Range("f20").Select
ActiveCell.Formula = "=LOOKUP(numberrows,'[Trending Source.xls]Stats'!_
R1C1:R4C1,'[Trending Source.xls]Stats'!R1C2:R4C2)"
mysmooth = activeCell.value
Range("h" & mysmooth + 2).Select
ActiveCell.FormulaR1C1 = "=average(RC2:r[-" & mysmooth - 1 & "]c2)"

 

[Frank Kabel]

Hi
try
ActiveCell.Formula = "=LOOKUP(" & numberrows & ",'[Trending
Source.xls]Stats'!
R1C1:R4C1,'[Trending Source.xls]Stats'!R1C2:R4C2)"

 

[Guest]

Thanks Frank.
Once again you have saved me.
I sure appreciate you.

Hi
try
ActiveCell.Formula = "=LOOKUP(" & numberrows & ",'[Trending
Source.xls]Stats'!
R1C1:R4C1,'[Trending Source.xls]Stats'!R1C2:R4C2)"


--
Regards
Frank Kabel
Frankfurt, Germany

Below is the code I am using to lookup a value from a table in another
workbook. I intend for the lookup to be in cell f20, and the value
then be known as "mysmooth". When stepping through the macro, I can
get to the range("h" & mysmooth . . . step. However, the previous
step is resulting in a #NAME? and therefore the following code does
not work. I can not figure out why I am getting the #NAME?. Any
help would be much appreciated.

TIA

Dim myrange As Range
Range("a2").Select
Selection.CurrentRegion.Select
Set myrange = Range("a2").CurrentRegion
numberrows = myrange.Rows.Count
Range("f20").Select
ActiveCell.Formula = "=LOOKUP(numberrows,'[Trending
Source.xls]Stats'!_ R1C1:R4C1,'[Trending Source.xls]Stats'!R1C2:R4C2)"
mysmooth = activeCell.value
Range("h" & mysmooth + 2).Select
ActiveCell.FormulaR1C1 = "=average(RC2:r[-" & mysmooth - 1 & "]c2)"