Lookup Formula Problem

[wesley holtman]

Hello,

I have a lookup formula that has worked great for the last few weeks
but now, for some unknow reason it prints the formula rather than
executing it. The formula right before it is almost exactly the same
and it works fine. Please help!

Dim StartHere As Worksheet, TB As Workbook, BWS As Worksheet, Cusip1
As Worksheet, Trader1 As Worksheet, Moody1 As Worksheet, Fitch1 As
Worksheet, SnP1 As Worksheet, GradeCode1 As Worksheet, Level41 As
Worksheet, SortCode1 As Worksheet, Basel1RW1 As Worksheet, Basel1Pro1
As Worksheet, SecCode1 As Worksheet, Account1 As Worksheet, FinalRow
As Long, CFinalRow As Long, TFinalRow As Long, MFinalRow As Long,
FFinalRow As Long, SFinalRow As Long, GFinalRow As Long, LFinalRow As
Long, SOFinalRow As Long, BFinalRow As Long, BAFinalRow As Long,
SEFinalRow As Long, AFinalRow As Long, BASFinalRow As Long, FinalCol
As Long, CFinalCol As Long, TFinalCol As Long, MFinalCol As Long,
FFinalCol As Long, SFinalCol As Long, GFinalCol As Long, LFinalCol As
Long, SOFinalCol As Long, BFinalCol As Long, BAFinalCol As Long,
SEFinalCol As Long, AFinalCol As Long, BASFinalCol As Long
Dim Cusip As Range, Trader As Range, Moody As Range, Fitch As
Range, SnP As Range, GradeCode As Range, Level4 As Range, SortCode As
Range, Basel1RW As Range, Basel1Pro As Range, SecCode As Range,
Account As Range, Basel25RW As Range

Set StartHere = Worksheets("Start Here")
Set TB = ActiveWorkbook
Set BWS = Worksheets("JPMS")
Set Cusip1 = Worksheets("Cusip")
Set Trader1 = Worksheets("Trader")
Set Moody1 = Worksheets("Moody's")
Set Fitch1 = Worksheets("Fitch")
Set SnP1 = Worksheets("SnP")
Set GradeCode1 = Worksheets("Grade Code")
Set Level41 = Worksheets("level 4 Sum")
Set SortCode1 = Worksheets("Sort Code")
Set Basel1RW1 = Worksheets("Basel 1 RW")
Set Basel1Pro1 = Worksheets("Basel 1 Product")
Set SecCode1 = Worksheets("Sec Code")
Set Account1 = Worksheets("Account")
Set Basel25RW1 = Worksheets("Basel 2.5 RW")

FinalRow = BWS.Cells(Rows.Count - 2, 1).End(xlUp).Row
CFinalRow = Cusip1.Cells(Rows.Count - 1, 1).End(xlUp).Row
TFinalRow = Trader1.Cells(Rows.Count - 1, 1).End(xlUp).Row
MFinalRow = Moody1.Cells(Rows.Count - 1, 1).End(xlUp).Row
FFinalRow = Fitch1.Cells(Rows.Count - 1, 1).End(xlUp).Row
SFinalRow = SnP1.Cells(Rows.Count - 1, 1).End(xlUp).Row
GFinalRow = GradeCode1.Cells(Rows.Count - 1, 1).End(xlUp).Row
LFinalRow = Level41.Cells(Rows.Count - 1, 1).End(xlUp).Row
SOFinalRow = SortCode1.Cells(Rows.Count - 1, 1).End(xlUp).Row
BFinalRow = Basel1RW1.Cells(Rows.Count - 1, 1).End(xlUp).Row
BAFinalRow = Basel1Pro1.Cells(Rows.Count - 1,
1).End(xlUp).Row
SEFinalRow = SecCode1.Cells(Rows.Count - 1, 1).End(xlUp).Row
AFinalRow = Account1.Cells(Rows.Count - 1, 1).End(xlUp).Row
BASFinalRow = Basel25RW1.Cells(Rows.Count - 1,
1).End(xlUp).Row

FinalCol = BWS.Cells(3, Columns.Count).End(xlToLeft).Column
CFinalCol = Cusip1.Cells(1,
Columns.Count).End(xlToLeft).Column
TFinalCol = Trader1.Cells(1,
Columns.Count).End(xlToLeft).Column
MFinalCol = Moody1.Cells(1,
Columns.Count).End(xlToLeft).Column
FFinalCol = Fitch1.Cells(1,
Columns.Count).End(xlToLeft).Column
SFinalCol = SnP1.Cells(1, Columns.Count).End(xlToLeft).Column
GFinalCol = GradeCode1.Cells(1,
Columns.Count).End(xlToLeft).Column
LFinalCol = Level41.Cells(1,
Columns.Count).End(xlToLeft).Column
SOFinalCol = SortCode1.Cells(1,
Columns.Count).End(xlToLeft).Column
BFinalCol = Basel1RW1.Cells(1,
Columns.Count).End(xlToLeft).Column
BAFinalCol = Basel1Pro1.Cells(1,
Columns.Count).End(xlToLeft).Column
SEFinalCol = SecCode1.Cells(1,
Columns.Count).End(xlToLeft).Column
AFinalCol = Account1.Cells(1,
Columns.Count).End(xlToLeft).Column
BASFinalCol = Basel25RW1.Cells(1,
Columns.Count).End(xlToLeft).Column

DataRow = FinalRow - 3

'NO HARD CODED MAPPING TABLES!!!!!!!

'Names.Add Name:="Cusip", RefersTo:="=Cusip1.cell(1,
1).Resize(CFinalRow, CFinalCol)"
Set Cusip = Cusip1.Cells(1, 1).Resize(CFinalRow,
CFinalCol)
Set Trader = Trader1.Cells(1, 1).Resize(TFinalRow,
TFinalCol)
Set Moody = Moody1.Cells(1, 1).Resize(MFinalRow,
MFinalCol)
Set Fitch = Fitch1.Cells(1, 1).Resize(FFinalRow,
FFinalCol)
Set SnP = SnP1.Cells(1, 1).Resize(SFinalRow, SFinalCol)
Set GradeCode = GradeCode1.Cells(1, 1).Resize(GFinalRow,
GFinalCol)
Set Level4 = Level41.Cells(1, 1).Resize(LFinalRow,
LFinalCol)
Set SortCode = SortCode1.Cells(1, 1).Resize(SOFinalRow,
SOFinalCol)
Set Basel1RW = Basel1RW1.Cells(1, 1).Resize(BFinalRow,
BFinalCol)
Set Basel1Pro = Basel1Pro1.Cells(1, 1).Resize(BAFinalRow,
BAFinalCol)
Set SecCode = SecCode1.Cells(1, 1).Resize(SEFinalRow,
SEFinalCol)
Set Account = Account1.Cells(1, 1).Resize(AFinalRow,
AFinalCol)
Set Basel25RW = Basel25RW1.Cells(1,
1).Resize(BASFinalRow, BASFinalCol)

Application.ScreenUpdating = False
'Prod Clas
On Error Resume Next
BWS.Range("AW4").Resize(DataRow, 1).FormulaR1C1 =
"=VLOOKUP(RC[-41]," & SecCode.Address(external:=True,
ReferenceStyle:=xlR1C1) & ",3,false)"

'Gov't Class
BWS.Range("AX4").Resize(DataRow, 1).FormulaR1C1 =
"=VLOOKUP(RC[-42]," & SecCode.Address(external:=True,
ReferenceStyle:=xlR1C1) & ",4,FALSE)"

 

[wesley holtman]

The last formula is the one I'm having trouble with

 

[wesley holtman]

The last formula is the one I'm having trouble with

It just started working again...weird