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]

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")

lastRow = Worksheets("Data").Cells(Rows.Count, 31).End(xlUp).Row

 

[Akbarov]

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

Sadly not works for me, how can i change F1:F45 to F40:F58 i don't understand this part Cell.offset(0, 4)

 

[maabadi]

Offset means number of rows & columns after your Criteria cell. if your Criteria cell is F40, then Offset( 0 , 4) don't change row ( 0) but add 4 column to your Criteria column. to Take Column J
Also You can change it to

Set ResultCell = Sheets("HISTOR").Range("J" & Cell.Row)

you can change F1:F45 to F40:F58 no problem.

What is Error if not working. Are you add

Dim Cell As Range

for Declaring Cell

 

[Akbarov]

I checked now, in SUM range there are errors #Value how can i ignore error values?

 

[maabadi]

then what is your sum range formula?

 

[Akbarov]

then what is your sum range formula?

I can fix it from formula, but i would like to know if there is possibility to do it from current vba?

 

[maabadi]

I don't think & I don't Know. Maybe Another one can help.

 

[Akbarov]

Thank you so much for help and information.
I never even dared to ask this question:
"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"

 

[maabadi]

You're Welcome & thanks for feedback.
Don't Hesitate to Ask any question.
Or onebody answer or not. No Problem At All.
We have one Proverb:
Don't Knowing Not Fault, Don't Asking is Fault