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”, which is
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)

"wesley holtman" wrote:
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.


Wesley wrote:
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.