VBA SQL like data pull

arimpact

New Member
Joined
Aug 29, 2014
Messages
3
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Upvote 0
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.
 
Upvote 0
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
AB
1NamesScores
2Mike55
3John66
4Matt77
5Gerald88
6John58
Sheet5


Code:
[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]
 
Last edited:
Upvote 0
If you change the code to this it will also check for multiple top values:

Code:
[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]
 
Upvote 0
If you change the code to this it will also check for multiple top values:

Code:
[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!
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top