VBA Vlookup loop with if statement

[Shadkng]

Hi, I am would like to achieve the following if someone could help. I tried to scrap some code online, see below, but I don't know enough to complete the job.

In my workbook, I have a sheet named "QUOTE" and I need to total the amount in the cells in a specific column that meet a certain criteria based on the IF statement. I know we can have the background color change if the criteria is met, and then I can do a SUMIF by color, but I assume it can be done in macro. Below is the information - I'm sure I left something out! Note I need to repeat this process in several columns which I hope to do on my own once I have the format. Thanks in advance.

Vlookup value: sheet QUOTE, C24 and down the column if cells have data
Table array: sheet COSTS, A2:AZ1000, column AF
If statement: if cell in column AF = "RE"
I would like the sum total of those cells to be in sheet QUOTE cell CL15

A second column would be:
Vlookup value: sheet QUOTE, D24 and down the column if cells have data
Table array: sheet COSTS, A2:AZ1000, column AF
If statement: if cell in column AF = "RE"
I would like the sum total of those cells to be in sheet QUOTE cell CP15

Sub ROLLEASE()  
    Dim N As Long, i As Long, j As Long
    N = Worksheets("COSTS").Cells(Rows.Count, "B").End(xlUp).Row
    j = 24
    For i = 2 To N
        If Worksheets("COSTS").Cells(i, "AF").Value = "RE" Then
        If Worksheets("QUOTE").Cells(j, "CL").Value > 0 Then
            Cells(j, "CL").Interior.color = 65535
            j = j + 1
        End If
        End If
    Next i
End Sub

 

[Shadkng]

I tried that. But ek is a higher number than dq. Should allcols be a high enough number to include all possible columns?

 

[offthelip]

My Mistake, too quick looking at it, the type mismatch would seem to imply that the value in "allcols(j, colno(k))" is NOT a number. When you run it and get the error go into debug and hover over the allcols and then excel should show you what the value is. If you also look at the value in "j" you can find out which row the error is on, but be aware that because we loaded inarr starting at row 2 the actual row onthe sheet is j +1

 

[Shadkng]

ok, so "allcols(j, colno(k))" shows the value as "" and it looks like row 24 which is my first row. Col DQ row 24 is blank with just a formula. I will have many columns like that. The previous columns I used had data in them.

 

[offthelip]

Put an if statement check in for blank round that statement

 

[Shadkng]

Put an if statement check in for blank round that statement

Sorry, I have no idea how to do that in vba.

 

[offthelip]

like this:

For j = 1 To UBound(inarr)
                  If (Dic(inarr(j, 1))) = "RE" Then 'this matches the value given by the index inarr(i,1) in the dictionary
            colel(j, 1) = allcols(j, colno(0)) ' copy first column into column el
            For k = 1 To UBound(colno)
                If allcols(j, colno(k)) <> "" Then
                colel(j, 1) = colel(j, 1) + allcols(j, colno(k)) ' add the other columns in taking the index from the array colno
                End If
            Next k
         End If
      Next j