VBA coding a nested Vlookup, and a sumif formula

I need help.

I have this formula:

=IF(ISERROR(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)),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("Info1")
        iLastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
        For i = 3 To iLastRow 'iLastRow to 1 Step -2
            .Cells(i, "K").Value = 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:

=SUMIF(Info1!$K$3:$K$43691,Summary!$A7&Summary!F$1,Info1!$I$3:$I$43691)+SUMIF(Info2!$K$3:$K$65536,Summary!$A7&Summary!F$1,info2!$I$3:$I$65536)

I could have used a code similar to the code for the vlookup one above, but I'd have to do that code for too many column. I want a more flexible formula.

I cross-posted or posted-multi posted because the code didn't give me the desired result and I needed a response quickly.
And I think I got into a little bit of trouble. Well, that's what I get for not reading the rules.
Thanks
Xrull
http://groups.google.com/group/micro...&q=dingo&pli=1
http://www.mrexcel.com/forum/showthr...ighlight=xrull
http://www.microsoft.com/office/comm...a-bced830f0d47
Thanks,
Xrull

A sample file would be clearer, I'd recommend posting one.

So your code works in terms of pasting this into the proper cells, but you're having problems with the formula, correct?

EDIT
Try copying the applicable pages into a new workbook, and posting that, I've had problems uploading entire spreadsheets before, that usually works.

mew!

Range("F7", "DN71").Formula = "=SUMIF(Info1!$K$3:$K$43691,Summary!$A7&Summary!F$1,Info1!$I$3:$I$43691)+SUMIF(Info2!$K$3:$K$65536,Summary!$A7&Summary!F$1,info2!$I$3:$I$65536)"

If you're looking to post that formula in every cell range F7:DN71, that code will do it.

Don't forget to read my above post.

Oh, and as per your concern about writing a new code for every column, just put in a for/next loop, and make the integer which increases on each loop, the value of the column. That'll move it on down the line and keep running the same loop for each column. IE make x = 6, and have the column references refer to x instead of "F", then when it loops it'll be 7, aka column "G".

EDIT:
I have no life.

Dim iLastRow As Long
Dim i As Long
Dim x As Integer


For x = 6 To 100

    With Sheets("Sheet1")
        iLastRow = .Cells(.Rows.Count, x).End(xlUp).Row
        For i = 3 To iLastRow 'iLastRow to 1 Step -2
            .Cells(i, x + 5).Value = 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

    
Next x

Please note that I've changed the name of the worksheet for the sake of making sure this worked properly. This is an example of what I meant by looping the column reference.

Since this runs on the same sheet it pastes on, you'll get wonky results with it as is. I believe from what you've written so far though, you can see how it works. if you'd like to set a limit to how many columns to go through you can use the same sort of technique you've used previously, or just have it count the number of columns across the top row that aren't blank, and use that as the integer which replaces 100 in the for x = 6 to 100 loop.

Me again,
The information on the pages info1 and info2 go beyond 30000 rows. Information is in Column C, but it would be found beyond row 10000.
Mewingkitty, thanks I tried your formula, but it didn't work, or better yet, I don't know to implement the suggestion. I hope the file will help you to help me.
Sheets info1 and info2 pick up data from the sheet named Data, and the Summary sheet picks up data from the info1 and info2 sheets.
Xrull

Otay, well that gives us somewhere to start anyways.

As per your formula, try this in K3, and let me know if it gives the result you're looking for. I've gone through and I think I see what you were trying to do with it, I believe this performs the same function, and is much easier to follow.

=IFERROR(IF(C3="",VLOOKUP(D3,Data!B:O,F3+2,0),IF(D3="",VLOOKUP(C3,Data!B:O,F3+2,0))),H3&"C0MISCELLANEOUS")

There is one thing about your original formula which really confused me. You have a VLOOKUP which is returning a match from a single column array, in column 1. That's kinda like saying "Find the number 3 in this column, then tell me it's a 3".

Let me know if that formula does what you want it to, and we'll go from there in terms of applying it through VBA. Figure it's best to start from the beginning.

EDIT:
VLOOKUP requires that your first column be sorted in order to return values properly, so I sorted the column in question for this to work.

Alternatively, INDEX(MATCH...

Can be used to accomplish the same thing without sorting.

=IFERROR(IF(C3="",INDEX(Data!B:O,MATCH(D3,Data!B:B,0),F3+2),IF(D3="",INDEX(Data!B:O,MATCH(C3,Data!B:B,0),F3+2))),H3&"C0MISCELLANEOUS")