VBA - sum of Miles in Column B = to Name in Column A

[smythcounty]

Hello,

I know this is going to be simple for you all but I've fried my brain trying to get this two work.

I have names of people in column A and miles traveled in column B. How can I get the total sum of miles traveled by each person in VBA?

A (Name) B (Miles)
Jim Smith 20
Nancy Cook 21
John Daly 25
Jim Acosta 26
John Daly 35
Nancy Cook 84
Steve Durst 86
Steve Durst 44
John Daly 55

Jim Smith Total =?
Nancy Cook Total =?
John Daly Total =?
Jim Acosta Total = ?

I hope that makes sense.

Thanks!

 

[smythcounty]

As your data is not in columns A & B (as per your op) my code is now

Sub smythcounty()
   Dim Ary As Variant
   Dim Dic As Object
   Dim i As Long
  
   Ary = Range("B2:F" & Range("B" & Rows.Count).End(xlUp)).Value2
  
   With CreateObject("scripting.dictionary")
      For i = 1 To UBound(Ary)
         .Item(Ary(i, 1)) = .Item(Ary(i, 1)) + Ary(i, 5)
      Next i
      Sheets.Add.Name "Results"
      Sheets("Results").Range("A2").Resize(.Count, 2).Value = Application.Transpose(Array(.Keys, .Items))
   End With
End Sub

Thanks I was manually changing it. I'm getting Run-Time error 1004, Method 'Range of object'_Global failed here:
Ary = Range("B2:F" & Range("B" & Rows.Count).End(xlUp)).Value2

 

[Fluff]

Oops, that should be

   Ary = Range("B2:F" & Range("B" & Rows.Count).End(xlUp).Row).Value2

 

[smythcounty]

Oops, that should be

   Ary = Range("B2:F" & Range("B" & Rows.Count).End(xlUp).Row).Value2

Fixed that Thank You!
Now I get Runtime Error 438 "object doesnt support this object or method" here:
Sheets.Add.Name "Results"

 

[Fluff]

Put an = sign after Name

 

[smythcounty]

That did it. Thank You!

 

[smythcounty]

If I wanted to edit this to show the results in a specific column instead of another sheet, such as "J2" how would I do so?

 

[Fluff]

You can do that like

Sub smythcounty()
   Dim Ary As Variant
   Dim Dic As Object
   Dim i As Long
   
   Ary = Range("B2:F" & Range("B" & Rows.Count).End(xlUp).Row).Value2
   
   With CreateObject("scripting.dictionary")
      For i = 1 To UBound(Ary)
         .Item(Ary(i, 1)) = .Item(Ary(i, 1)) + Ary(i, 5)
      Next i
      Range("J2").Resize(.Count, 2).Value = Application.Transpose(Array(.Keys, .Items))
   End With
End Sub