VBA, Sumifs, Long

[Akbarov]

Hello Dear Community,

Can anyone help me please? what am i doing wrong here?
IfCell and IsCell are sport names
SumCell is scores they achieved

Sub test()
  Dim IfCell As Range, lr As Long
  Dim IsCell As Range
  Dim SumCell As Range, lr1 As Long
  Dim ResultCell As Range
  Dim CriteriaValue As String
  lr = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  lr1 = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  Set IfCell = Sheets("DATA").Range("ae25:ae2000")
  Set IsCell = Sheets("HISTOR").Range("F41")
  Set SumCell = Sheets("DATA").Range("AF25:AF2000")
  Set ResultCell = Sheets("HISTOR").Range("J41")

  CriteriaValue = "<>"

  ResultCell = WorksheetFunction.SumIfs(SumCell, IfCell, IsCell)
End Sub

 

[Saurabhj]

Hi Akbarov,

Kindly share the sheet having data and what is expected from the code.

Thanks,
Saurabh

 

[Akbarov]

Hello, Saurabhj​

It is not complicated, Sumifs:
IfCell = Sheets("DATA").Range("ae25:ae2000") List Of Sports ( 16 different sports )
match
IsCell = Sheets("HISTOR").Range("F41") <--- Name of sport i am trying to sum results
Sum = Sheets("DATA").Range("AF25:AF2000") <--- This column is result of scores they achieved so they are numbers ( but formulas )

Instead of using range like AF25:AF2000 i want to use last.row but i forgot how to do that. ("AF25:AF") & lastrow

And finally i want result of calculation here:
ResultCell = Sheets("HISTOR").Range("J41")

 

[maabadi]

Sub test()
  Dim IfCell As Range, lr As Long
  Dim IsCell As Range
  Dim SumCell As Range, lr1 As Long
  Dim ResultCell As Range
  Dim CriteriaValue As String
  lr = Sheets("DATA").Cells(Rows.Count, 31).End(xlUp).Row

  Set IfCell = Sheets("DATA").Range("AE25:AE" & lr)
  Set IsCell = Sheets("HISTOR").Range("F41")
  Set SumCell = Sheets("DATA").Range("AF25:AF" & lr)
  Set ResultCell = Sheets("HISTOR").Range("J41")

  CriteriaValue = "<>"

  ResultCell = WorksheetFunction.SumIfs(SumCell, IfCell, IsCell)
End Sub

 

[Akbarov]

Thanks for reply but i get error Type-mismatch : ResultCell = WorksheetFunction.SumIfs

 

[maabadi]

For me Working. Upload code that you used.

 

[Akbarov]

Made little change it seems to work now, thank you very much.
2 question ,
what is Rows.Count,31 <---- 31 is id of column?
Do i need to change Dim as Range or other types if i will use this VBA for non-numerical data?

Sub test()
  Dim IfCell As Range, lr As Long
  Dim IsCell As Range
  Dim SumCell As Range, lr1 As Long
  Dim ResultCell As Range
  lr = Sheets("DATA").Cells(Rows.Count, 31).End(xlUp).Row
  lr1 = Sheets("DATA").Cells(Rows.Count, 31).End(xlUp).Row
  Set IfCell = Sheets("DATA").Range("AE25:AE" & lr)
  Set IsCell = Sheets("HISTOR").Range("F41")
  Set SumCell = Sheets("DATA").Range("AF25:AF" & lr1)
  Set ResultCell = Sheets("HISTOR").Range("J41")

  CriteriaValue = "<>"

  ResultCell = WorksheetFunction.SumIfs(SumCell, IfCell, IsCell)
End Sub

 

[maabadi]

1. 31 is column number . for AE column number is 31
2. No. Range is Range don't different for Values or Stirngs ( Numbers or Text)

 

[Akbarov]

I guess there is no any easy way to make this code to calculate from F1 to F45 and put results to J1 to J45
IsCell = Sheets("HISTOR").Range("F41")
Set ResultCell = Sheets("HISTOR").Range("J41")

 

[maabadi]

Sub test()
  Dim IfCell As Range, lr As Long
  Dim IsCell As Range
  Dim SumCell As Range, lr1 As Long
  Dim ResultCell As Range, Cell As Range
  lr = Sheets("DATA").Cells(Rows.Count, 31).End(xlUp).Row
  lr1 = Sheets("DATA").Cells(Rows.Count, 32).End(xlUp).Row
  Set IfCell = Sheets("DATA").Range("AE25:AE" & lr)
Set SumCell = Sheets("DATA").Range("AF25:AF" & lr1)
  For Each Cell in Sheets("HISTOR").Range("F1:F45")
   Set IsCell = Cell
   Set ResultCell = Cell.offset(0, 4)
  'CriteriaValue = "<>"
  ResultCell = WorksheetFunction.SumIfs(SumCell, IfCell, IsCell)
Next Cell
End Sub