Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba sumif and vlookup codes
I need help.
I have this formula: =IF(ISERROR(VLOOKUP(IF(C3="",VLOOKUP(D3,Data!B:O,F 3+2,0),IF(D3="",VLOOKUP(C3,Data!B:O,F3+2,0))),$O$3 :$O$114,1,0)),H3&"C0MISCELLANEOUS",H3&(VLOOKUP(IF( C3="",VLOOKUP(D3,Data!B:O,F3+2,0),IF(D3="",VLOOKUP (C3,Data!B:O,F3+2,0))),$O$3:$O$114,1,0))) And I tried using this code to run instead of the formula above because the formula takes about 10 minute to run for worksheets, but it gives me #Value! at row 100. Sub Run_Data() Dim iLastRow As Long Dim i As Long With Sheets("FBL3N_1") iLastRow = .Cells(.Rows.Count, "F").End(xlUp).Row For i = 3 To iLastRow 'iLastRow to 1 Step -1 .Cells(i, "K").Formula = Evaluate("=IF(ISERROR(VLOOKUP(IF(C" & i & "="""",VLOOKUP(" & _ "D" & i & ",Data!B:O,F3+2,0),IF(D" & i & "="""",VLOOKUP(" & "C" & i & ",Data!B:O,F3+2,0))),$O$3:$O$114,1,0)),H" & i & "& ""C0MISCELLANEOUS"",H" & i & _ "&(VLOOKUP(IF(C" & i & "="""",VLOOKUP(D" & i & ",Data!B:O,F3+2,0),IF(" & "D" & i & "="""",VLOOKUP(C" & i & ",Data!B:O,F3+2,0))),$O$3:$O$114,1,0)))") Next i End With End Sub And how would I write a VBA Code to run from F7 to DN71 for the formula below: I dont want the formulas in the cell. I just want values. Formulas take too long. =SUMIF(LZL3N_1!$K$3:$K$43691,Summary!$A7&Summary!F $1,LZL3N_1!$I$3:$I$43691)+SUMIF(LZL3N_2!$K$3:$K$65 536,Summary!$A7&Summary!F$1,LZL3N_2!$I$3:$I$65536) Thanks, Xrull |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba sumif and vlookup codes
Desist from all of this multi-posting, we can read it wherever it is.
-- __________________________________ HTH Bob "Xrull" wrote in message ... I need help. I have this formula: =IF(ISERROR(VLOOKUP(IF(C3="",VLOOKUP(D3,Data!B:O,F 3+2,0),IF(D3="",VLOOKUP(C3,Data!B:O,F3+2,0))),$O$3 :$O$114,1,0)),H3&"C0MISCELLANEOUS",H3&(VLOOKUP(IF( C3="",VLOOKUP(D3,Data!B:O,F3+2,0),IF(D3="",VLOOKUP (C3,Data!B:O,F3+2,0))),$O$3:$O$114,1,0))) And I tried using this code to run instead of the formula above because the formula takes about 10 minute to run for worksheets, but it gives me #Value! at row 100. Sub Run_Data() Dim iLastRow As Long Dim i As Long With Sheets("FBL3N_1") iLastRow = .Cells(.Rows.Count, "F").End(xlUp).Row For i = 3 To iLastRow 'iLastRow to 1 Step -1 .Cells(i, "K").Formula = Evaluate("=IF(ISERROR(VLOOKUP(IF(C" & i & "="""",VLOOKUP(" & _ "D" & i & ",Data!B:O,F3+2,0),IF(D" & i & "="""",VLOOKUP(" & "C" & i & ",Data!B:O,F3+2,0))),$O$3:$O$114,1,0)),H" & i & "& ""C0MISCELLANEOUS"",H" & i & _ "&(VLOOKUP(IF(C" & i & "="""",VLOOKUP(D" & i & ",Data!B:O,F3+2,0),IF(" & "D" & i & "="""",VLOOKUP(C" & i & ",Data!B:O,F3+2,0))),$O$3:$O$114,1,0)))") Next i End With End Sub And how would I write a VBA Code to run from F7 to DN71 for the formula below: I don't want the formulas in the cell. I just want values. Formulas take too long. =SUMIF(LZL3N_1!$K$3:$K$43691,Summary!$A7&Summary!F $1,LZL3N_1!$I$3:$I$43691)+SUMIF(LZL3N_2!$K$3:$K$65 536,Summary!$A7&Summary!F$1,LZL3N_2!$I$3:$I$65536) Thanks, Xrull |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif or vlookup | Excel Worksheet Functions | |||
vlookup - matching where codes are not exact | Excel Discussion (Misc queries) | |||
vlookup with zip codes - not working - frustrated | Excel Worksheet Functions | |||
Vlookup using letter and numeric codes | Excel Discussion (Misc queries) | |||
Vlookup w/multiple codes | Excel Worksheet Functions |