Vlookup error

W

wesley holtman

Hello,

I have formula #1 included in a report generating macro that I am
currently attempting to debug. Formula #1 is exactly what I have
entered in the VBE. The problem is, when I run the macro, it enters
formula #2 in the spreadsheet, which doesn’t make sense to me. It
changes my lookup array, from my variable “Cusip” to “CorpA”, whichis
the sheet where formula is being entered. What am I doing wrong
here?

1) Corp.Range("BA4").Resize(DataRow, 1).FormulaR1C1 =
"=VLOOKUP(RC[-48]," & Cusip.Address(external:=True,
ReferenceStyle:=xlR1C1) & ",2,FALSE)"

2)=VLOOKUP(E4,CorpA!$A$1:$D$2027,2,FALSE)
 
J

joeu2004

wesley holtman said:
I have formula #1 included in a report generating
macro that I am currently attempting to debug. [....]
The problem is, when I run the macro, it enters
formula #2 in the spreadsheet [....]
What am I doing wrong here?

Posting the same question in two different newsgroups, for one.

Failing to show us all relevant lines of code, for another.

1) Corp.Range("BA4").Resize(DataRow, 1).FormulaR1C1 =
"=VLOOKUP(RC[-48]," & Cusip.Address(external:=True,
ReferenceStyle:=xlR1C1) & ",2,FALSE)"

2)=VLOOKUP(E4,CorpA!$A$1:$D$2027,2,FALSE)

Dave Peterson already provided an adequate guess in
m.p.e.worksheet.functions. My own independent assessment mostly duplicates
his.

Obviously, Cusip is not set to the range that you think it is. Apparently,
it is effectively set to Sheets("CorpA").Range("A1:D2027") instead of
Sheets("Cusip").Range("A1:D2027").

Presumably you should fix the statement "Set Cusip = ...", which you do not
show us.
 

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

Top