Problem with Countif & Lookup at VBA with Cells argument

[maabadi]

What is Problem of this code. It return Countif =0 always.
Please check lookup function also.

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

 

[maabadi]

I correct Lookup formula to this but still show Syntax error? Please Help:

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

 

[RoryA]

Evaluate takes a formula string, not VBA, so:

x = Evaluate("Lookup(2, 1 / (A2:A44=A2),B2:B44)")

 

[maabadi]

Thanks RoryA,
But why it return 0 (I put data at A2:B44 )

 

[RoryA]

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.

 

[maabadi]

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.

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