Problem with Countif & Lookup at VBA with Cells argument

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,681
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
What is Problem of this code. It return Countif =0 always.
Please check lookup function also.

VBA Code:
Sub SumLookUp()
 Dim cell As Range
 Dim x As Long
 Dim firstrow As Long
 Dim firstColumn As Long
 Dim lastrow As Long
 Dim sumcolumn As Long
 Dim y As Long
 Dim InputData As Range
 Dim Sumrange As Range
 Dim SumLookUp As Long
 
 Set InputData = Range("A2:A44")
 Set Sumrange = Range("B2:B44")
 y = 0
 
 firstrow = InputData.Row
 firstColumn = InputData.Column
 sumcolumn = Sumrange.Column
 lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For Each cell In InputData
     Debug.Print Cells(firstrow, firstColumn).Value
     Debug.Print Cells(cell.Row, firstColumn).Value
     Debug.Print Cells(cell.Row, cell.Column).Value
        If Application.WorksheetFunction.CountIf(Range(Cells(firstrow, firstColumn), Cells(cell.Row, firstColumn)), Range(Cells(cell.Row, cell.Column))) = 1 Then
           x = Evaluate("=Application.WorksheetFunction.Lookup(2, 1 / (Range(Cells(firstrow, firstColumn), Cells(lastrow, firstColumn))) = Range(Cells(cell.Row, cell.Column)), Range(Cells(firstrow, sumcolumn), Cells(lastrow, sumcolumn)))")
            
           y = y + x
           Else
           Debug.Print Application.WorksheetFunction.CountIf(Range(Cells(firstrow, firstColumn), Cells(cell.Row, firstColumn)), Range(Cells(cell.Row, cell.Column)))
        End If
    Next cell
    SumLookUp = y
    MsgBox SumLookUp
    Debug.Print y
End Sub
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
I correct Lookup formula to this but still show Syntax error? Please Help:
VBA Code:
Sub SumLookup()
Dim x as long
x = Evaluate("=Application.WorksheetFunction.Lookup(2, 1 / (Range("A2:A44") = Range("A2")), Range("B2:B44"))")
Debug.Print x
End Sub
 
Upvote 0
Evaluate takes a formula string, not VBA, so:

Code:
x = Evaluate("Lookup(2, 1 / (A2:A44=A2),B2:B44)")
 
Upvote 0
Solution
Thanks RoryA,
But why it return 0 (I put data at A2:B44 )
 
Upvote 0
I would assume that is down to the data - it's matching either a 0 or an empty cell in B2:B44. Bear in mind that this is evaluating whichever sheet is active at the time.
 
Upvote 0
Yes. I know. But I haven't empty and 0 at data at all. and I run it at activesheet also.
And Never Mind. I changed the code to another and now it solved.
VBA Code:
Function SumLookUp(InputData As Range, Sumrange As Range) As Long
 Dim FC As Range
 Dim LC As Range
 Dim FS As Range
 Dim LS As Range
 Dim AC As Range
 Dim SC As Range
 Dim cell As Range
 Dim lastrow As Long
 Dim x As Long
 Dim y As Long
  
 Set FC = Cells(InputData.Row, InputData.Column)
 lastrow = Cells(Rows.Count, 1).End(xlUp).Row
 Set LC = Cells(lastrow, InputData.Column)
 Set FS = Cells(InputData.Row, Sumrange.Column)
 Set LS = Cells(lastrow, Sumrange.Column)
  
      y = 0
    
   For Each cell In InputData
       Set AC = Cells(cell.Row, cell.Column)
       Set SC = Cells(cell.Row, Sumrange.Column)
        If Application.WorksheetFunction.CountIf(Range(FC, AC), AC) = Application.WorksheetFunction.CountIf(Range(FC, LC), AC) Then
              x = SC.Value
              y = y + x
           Else
        End If
    Next cell
      SumLookUp = y
    Debug.Print y

End Function
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top