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

 

[offthelip]

I am not really clear what you are trying to do, but I have made some modifications that might help you:

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

 

[Shadkng]

I am not really clear what you are trying to do, but I have made some modifications that might help you:

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

Hi, #2 code didn't work at all. Please come back to me with any questions and I'll try to explain more clearly.

Actually, the code I submitted is a poor example of what I need to achieve. Instead of the 2 "if'" lines, I need a vlookup of sheet QUOTE range C & j on COSTS range AF & i and if it is equal to RE, then change the color of the cell on sheet QUOTE. My "value>0" was kind of experimental for me.

But instead of changing the background color, is it possible to total those cells that meet the criteria and have that total in a cell CP15?

Thanks

 

[Shadkng]

Hi, I am refreshing this post in hopes of providing a better explanation of my goal. I provided the formula in bold to show more what need in vba. I would like the cells on sheet QUOTE, column CL to change color based on the vlookup in sheet COSTS, column 32 equal to "RE". I plan to use SUMIF to total all the colored cells into a cell at the top of the column. If there is a better way please suggest. I plan to do the same for several columns the same way, but I just wanted t get the basic one working. I hope this is more clear this time. Thanks

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

[B]=IF(VLOOKUP(QUOTE!$C24,COSTS!$A$2:$AZ$700,32,FALSE)="RE", [I]need interior color 65535[/I]  ,"")[/B]
      
            j = j + 1
        End If
        End If
    Next i
End Sub

 

[offthelip]

Using color as logical flag to determine a condtion is not a good way of designing an excel workbook , particularly if you intend to use VBa., This is because to determine the color of a celll in vBa you have to read each cell individually whcih can make VBA very slow if you have a lot of cells to look at . Color is very good for presenting information to the user and so it shoudl be used just for that. You can use conditional formatting to test for "RE" in a column and make it your desired color then you don't need to worry about VBA to detect which cells need interior color. Also you can use Sumif statement to sum all the cells have RE in column 32. So I don't know why you are thinking of using VBA

 

[Shadkng]

Using color as logical flag to determine a condtion is not a good way of designing an excel workbook , particularly if you intend to use VBa., This is because to determine the color of a celll in vBa you have to read each cell individually whcih can make VBA very slow if you have a lot of cells to look at . Color is very good for presenting information to the user and so it shoudl be used just for that. You can use conditional formatting to test for "RE" in a column and make it your desired color then you don't need to worry about VBA to detect which cells need interior color. Also you can use Sumif statement to sum all the cells have RE in column 32. So I don't know why you are thinking of using VBA

Thanks for your explanation, I understand. I was concerned to use conditional formatting cells - I thought conditional formatting also creates overhead on the workbook. If you say that's the better way to go regardless I will try it. Thanks

 

[offthelip]

It does create an overhead, but it wouldn't be as slow as trying to do exactly the same thing using VBA. The only way to really speed up it up would be not to color the cells at all. I suggest try it and see if update time really is an issue.

 

[Shadkng]

It does create an overhead, but it wouldn't be as slow as trying to do exactly the same thing using VBA. The only way to really speed up it up would be not to color the cells at all. I suggest try it and see if update time really is an issue.

If I didn't use cond format, than we can use vba to do the vlookup/if and to copy the cell to another column and tally it up there. I was trying to avoid that and use the existing column.

 

[offthelip]

Yes you certainly can use VBA to do a vlookup, however it is much faster to use VBA to do the same thing as a VLOOKUP but use a dictionary to do the looking up. Here is some code that loads the data from the "Cost" worksheet into a dictionary and then uses that to lookup all the valuesin column C of the "Quotes" sporeadsheet and write the answer into column CV. This way of doing it is super fast since it is all done in memory

Sub dictionarydemo()
' this shows the use of a dictionary to copy a value to a matching worksheet
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
   Dim Cl As Range
   Dim outarr() As Variant
   
   Set Dic = CreateObject("Scripting.dictionary")
   With Worksheets("COSTS")
   ' load lookup date into a varaint array
      Ary = .Range("$A$2:$AZ$700").Value
   End With
   For i = 1 To UBound(Ary)
      Dic(Ary(i, 1)) = Ary(i, 32)  ' load the lookup column into the dictionary
   Next i
      
   With Worksheets("Quotes")
    lastrow = .Cells(Rows.Count, "C").End(xlUp).Row  ' check column C for the last row
    inarr = .Range(.Cells(2, 3), .Cells(lastrow, 3)) ' load all the value to lookup
    ReDim outarr(1 To lastrow - 1, 1 To 1)
      For j = 1 To UBound(inarr)
         outarr(j, 1) = Dic(inarr(j, 1)) 'this matches the value given by the index inarr(i,1) in the dictionary
      Next j
      .Range(.Cells(2, 100), .Cells(lastrow, 100)) = outarr ' I didn't know where you wanted it so I put it in column CV ( column 100)
   End With
End Sub

 

[Shadkng]

Yes you certainly can use VBA to do a vlookup, however it is much faster to use VBA to do the same thing as a VLOOKUP but use a dictionary to do the looking up. Here is some code that loads the data from the "Cost" worksheet into a dictionary and then uses that to lookup all the valuesin column C of the "Quotes" sporeadsheet and write the answer into column CV. This way of doing it is super fast since it is all done in memory

Sub dictionarydemo()
' this shows the use of a dictionary to copy a value to a matching worksheet
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
   Dim Cl As Range
   Dim outarr() As Variant
  
   Set Dic = CreateObject("Scripting.dictionary")
   With Worksheets("COSTS")
   ' load lookup date into a varaint array
      Ary = .Range("$A$2:$AZ$700").Value
   End With
   For i = 1 To UBound(Ary)
      Dic(Ary(i, 1)) = Ary(i, 32)  ' load the lookup column into the dictionary
   Next i
     
   With Worksheets("Quotes")
    lastrow = .Cells(Rows.Count, "C").End(xlUp).Row  ' check column C for the last row
    inarr = .Range(.Cells(2, 3), .Cells(lastrow, 3)) ' load all the value to lookup
    ReDim outarr(1 To lastrow - 1, 1 To 1)
      For j = 1 To UBound(inarr)
         outarr(j, 1) = Dic(inarr(j, 1)) 'this matches the value given by the index inarr(i,1) in the dictionary
      Next j
      .Range(.Cells(2, 100), .Cells(lastrow, 100)) = outarr ' I didn't know where you wanted it so I put it in column CV ( column 100)
   End With
End Sub

Hi, wow thank you. It basically worked, however I need the cell values in column CL in sheets "quote" to move to col CV if the vlookup finds a match, instead of the value found in sheet "costs".

Sorry I mistyped - the sheets name is quote without the s.

I will have to repeat this process for several columns in sheets quote which hopefully I can do myself after I understand the format. I may need a little more explanation.