Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Formula Problem
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)" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Formula Problem
The last formula is the one I'm having trouble with
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup Formula Problem
On Oct 6, 12:40*pm, wesley holtman wrote:
The last formula is the one I'm having trouble with It just started working again...weird |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem - lookup formula | Excel Worksheet Functions | |||
lookup problem | Excel Discussion (Misc queries) | |||
Lookup problem | Excel Worksheet Functions | |||
LOOKUP problem | Excel Worksheet Functions | |||
LOOKUP problem!!! | Excel Worksheet Functions |