VBA SQL like data pull

[arimpact]

Hi,

I'm wondering how to do something in VBA

Let say you have a table with column A (name) and column B (scores) and it lists multiple names with different game scores. And I want to find the total score for a specific name. In SQL you can just use a "where" criteria to return results only for the specific name and sum his/her scores. How would you do that using EXCEL VBA? I'd imagine using an array table somehow but beyond that I don't know how to filter then put a sum action to it. Please help.

 

[Joe4]

Welcome to the Board!

There are a few topics you want to look into Filters, Pivot Tables, and Subtotals

Filters are pretty easy, and work much like the "WHERE" clause in SQL. It will just return the records you want.
However, if you want totals or subtotals, then you will probably want to use Subtotals or Pivot Tables in Excel.

Excel's Help files are a good place to start for details on these things, but there are also many good articles, videos, and tutorials on-line which can be located pretty easily using a Google Search.

 

[arimpact]

Welcome to the Board!

There are a few topics you want to look into Filters, Pivot Tables, and Subtotals

Filters are pretty easy, and work much like the "WHERE" clause in SQL. It will just return the records you want.
However, if you want totals or subtotals, then you will probably want to use Subtotals or Pivot Tables in Excel.

Excel's Help files are a good place to start for details on these things, but there are also many good articles, videos, and tutorials on-line which can be located pretty easily using a Google Search.

Thanks for the reply.

I'm actually looking specifically to do all this as part of a VBA macro without altering the sheet data.

For example, right now I created a dictionary where the keys are the person's name and their values are the total scores. Now I want to return in a cell the name of the person with the Max score. Not sure how to do that quite yet.

 

[mrmmickle1]

Here is one way to get the answer. I'm not sure it will work if there are two occurences of the High Score though. It will just hit the first name.....

Data Looks Like:

Excel 2012
	A	B
1	Names	Scores
2	Mike	55
3	John	66
4	Matt	77
5	Gerald	88
6	John	58
Sheet5

[COLOR=#0000ff]Sub[/COLOR] GetHighScore()

  [COLOR=#0000ff]  Dim[/COLOR] LastRow[COLOR=#0000ff]             As Long[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] Rng                 [COLOR=#0000ff]As [/COLOR]Range
    [COLOR=#0000ff]Dim[/COLOR] HighScoreAddress    [COLOR=#0000ff]As String[/COLOR]
    [COLOR=#0000ff]Dim[/COLOR] HighScore           [COLOR=#0000ff]As String[/COLOR]
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    HighScore = WorksheetFunction.Max(Range("B2:B" & LastRow))
    
   [COLOR=#0000ff]   Set[/COLOR] Rng = Range("B2:B" & LastRow).Find(What:=HighScore, _
                           After:=Range("B2"), _
                           LookAt:=xlWhole, _
                           LookIn:=xlValues, _
                           SearchOrder:=xlByRows, _
                           SearchDirection:=xlNext, _
                           MatchCase:=[COLOR=#0000ff]False[/COLOR])
                           
              [COLOR=#0000ff]  If Not [/COLOR]Rng[COLOR=#0000ff] Is Nothing Then[/COLOR]
                    HighScoreAddress = Rng.Address
                    NameVal = Range(HighScoreAddress).Offset(, -1).Address
[COLOR=#0000ff]                End If[/COLOR]
                        
            MsgBox Range(NameVal).Value
              
[COLOR=#0000ff]End Sub[/COLOR]

 

[mrmmickle1]

If you change the code to this it will also check for multiple top values:

[COLOR=#0000ff]Sub[/COLOR] GetHighScore()


[COLOR=#0000ff]    Dim[/COLOR] LastRow           [COLOR=#0000ff]As Long[/COLOR]
   [COLOR=#0000ff] Dim[/COLOR] Rng               [COLOR=#0000ff]As[/COLOR] Range
    [COLOR=#0000ff]Dim [/COLOR]HighScoreAddress  [COLOR=#0000ff]As String[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] HighScore         [COLOR=#0000ff]As String
    Dim[/COLOR][COLOR=#000000] CountMaxVal [/COLOR][COLOR=#0000ff]      As Integer[/COLOR]
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    HighScore = WorksheetFunction.Max(Range("B2:B" & LastRow))
    
   [COLOR=#0000ff]   Set[/COLOR] Rng = Range("B2:B" & LastRow).Find(What:=HighScore, _
                           After:=Range("B2"), _
                           LookAt:=xlWhole, _
                           LookIn:=xlValues, _
                           SearchOrder:=xlByRows, _
                           SearchDirection:=xlNext, _
                           MatchCase:=[COLOR=#0000ff]False[/COLOR])
                           
            [COLOR=#0000ff]    If Not [/COLOR]Rng [COLOR=#0000ff]Is Nothing Then[/COLOR]
                    HighScoreAddress = Rng.Address
                    NameVal = Range(HighScoreAddress).Offset(, -1).Address
[COLOR=#0000ff]                End If[/COLOR]
                
            CountMaxVal = Application.WorksheetFunction.CountIf(Range(Cells(2, 2), Cells(LastRow, 2)), CInt(HighScore))
            
    [COLOR=#0000ff]    If[/COLOR] CountMaxVal <= 1 [COLOR=#0000ff]Then[/COLOR]
            MsgBox Range(NameVal).Value, vbInformation, "Top Value Calculator"
[COLOR=#0000ff]        Else[/COLOR]
            MsgBox "Multiple Top Values", vbInformation, "Top Value Calculator"
[COLOR=#0000ff]        End If[/COLOR]

[COLOR=#0000ff]End Sub[/COLOR]

 

[arimpact]

If you change the code to this it will also check for multiple top values:

[COLOR=#0000ff]Sub[/COLOR] GetHighScore()


[COLOR=#0000ff]    Dim[/COLOR] LastRow           [COLOR=#0000ff]As Long[/COLOR]
   [COLOR=#0000ff] Dim[/COLOR] Rng               [COLOR=#0000ff]As[/COLOR] Range
    [COLOR=#0000ff]Dim [/COLOR]HighScoreAddress  [COLOR=#0000ff]As String[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] HighScore         [COLOR=#0000ff]As String
    Dim[/COLOR][COLOR=#000000] CountMaxVal [/COLOR][COLOR=#0000ff]      As Integer[/COLOR]
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    HighScore = WorksheetFunction.Max(Range("B2:B" & LastRow))
    
   [COLOR=#0000ff]   Set[/COLOR] Rng = Range("B2:B" & LastRow).Find(What:=HighScore, _
                           After:=Range("B2"), _
                           LookAt:=xlWhole, _
                           LookIn:=xlValues, _
                           SearchOrder:=xlByRows, _
                           SearchDirection:=xlNext, _
                           MatchCase:=[COLOR=#0000ff]False[/COLOR])
                           
            [COLOR=#0000ff]    If Not [/COLOR]Rng [COLOR=#0000ff]Is Nothing Then[/COLOR]
                    HighScoreAddress = Rng.Address
                    NameVal = Range(HighScoreAddress).Offset(, -1).Address
[COLOR=#0000ff]                End If[/COLOR]
                
            CountMaxVal = Application.WorksheetFunction.CountIf(Range(Cells(2, 2), Cells(LastRow, 2)), CInt(HighScore))
            
    [COLOR=#0000ff]    If[/COLOR] CountMaxVal <= 1 [COLOR=#0000ff]Then[/COLOR]
            MsgBox Range(NameVal).Value, vbInformation, "Top Value Calculator"
[COLOR=#0000ff]        Else[/COLOR]
            MsgBox "Multiple Top Values", vbInformation, "Top Value Calculator"
[COLOR=#0000ff]        End If[/COLOR]

[COLOR=#0000ff]End Sub[/COLOR]

Thanks!