Simple Macro - Sum of values that are related to a sprecific word chosen by you.

[Adam Brave]

Hi,

I want to do a simple macro but I'm having some troubles and I think you may help me.

I have a table with just two columns (the first column has numbers and the second words) and some rows. So I want to create a macro that consists on a loop with a condiction inside. This macro would sum all the values of the first column that meet a specific word on the second column like the example bellow where the result would be 2+5= "7". The specific word that I want to test should be specified and written by me at the beginning of the macro in a popup box that in the example bellow would be "food".

Can anyone help me?

2	food
1	football
5	food
4	cinema

Best regards,

[Andrew-R]

Hi Adam, welcome to the forum. Unfortunately you need to read the forum rules and give your thread a proper title, as per rule #1.

In the meantime - is there a reason you want a macro to do this? It can be done very simply with a formula.

[Adam Brave]

Hi Adam, welcome to the forum. Unfortunately you need to read the forum rules and give your thread a proper title, as per rule #1.

In the meantime - is there a reason you want a macro to do this? It can be done very simply with a formula.

Hi Andrew-R, for two reasons; with the macro I guess it would make easier for me to choose the word I want to use to do the sum but the main reason is for make myself to start using and understanding how to make macros =P

[royUK]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

PLEASE PM WHEN YOU HAVE DONE THIS AND I WILL DELETE THIS POST

[Andrew-R]

If your scores are in cells A1:A4 and the words are in B1:B4 then you could have another cell, let's say C1, where the user could enter the word and then you could use:

=SUMIF(A1:A4,B1:B4,C1)

I.e. SUM the cells in column A where the corresponding values in column B equals the value in C1.

However, if you're keen to teach yourself macros then it's perfectly understandable that you don't want to use a formula. Is it going to be helpful if somebody here just writes the macro for you, or would you rather show us how far you've got yourself and ask for help/advice on specific topics?

[Adam Brave]

Well maybe it would be better if someone just writes the macro. The popup box to write the word is easy but I have some difficulties with the main part =\

Popup box:

Dim S As String
S = InputBox("Introduza um texto")
If StrPtr(S) <> 0 Then
Range("C1").Value = S

[Andrew-R]

Sorry to slap you with the forum rules again, but you need to use code tags around any macro code you post. Edit your last post to put some in and then we'll have a look at how to do this.

[Adam Brave]

Sorry lolol

Done

[Andrew-R]

No problems.

I'd do this one like this*...

Sub CountByWord()

Const lWORD_COLUMN = 2
Const lROW_OFFSET = 0
Const lCOL_OFFSET = -1

Dim sWordToMatch As String 'Avoid using single letters for your variable names.  It makes your code hard to read
Dim lCount As Long
Dim rngMatchWord As Range
Dim sFirstAddress As String

sWordToMatch = InputBox("Please enter word to match")

If sWordToMatch <> "" Then

  lCount = 0
  
  Set rngMatchWord = Columns(lWORD_COLUMN).Find(sWordToMatch, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
  
  If Not rngMatchWord Is Nothing Then
  
    sFirstAddress = rngMatchWord.Address
    
    Do
    
      lCount = lCount + rngMatchWord.Offset(lROW_OFFSET, lCOL_OFFSET).Value
      Set rngMatchWord = Columns(lWORD_COLUMN).Find(sWordToMatch, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False, after:=rngMatchWord)
      
    Loop Until rngMatchWord.Address = sFirstAddress
    
  End If
  
  MsgBox "Count = " & lCount
  
End If

End Sub

However a lot of new coders would go through their worksheet row by row, checking each value. This can be very slow indeed for larger sheets.

* This is a lie, I'd use WorksheetFunction.SumIf to implement the formula I wrote up above, but if I'd forgotten how to do that then I'd do things as above. Probably

[Adam Brave]

WOW!! While I was waiting I actually got to write one macro by myself and it works but it takes to long to present the result. Unlike my code your is way faster comparing with mine and it works Oo I still dont understand him at full but I will analyse him

Thank you very much

Regards,


(I send the code bellow)

' popup Macro
'
Dim S As String
Dim total As Integer
Dim Var As Integer
total = 0
S = InputBox("Introduza um texto")
If StrPtr(S) <> 0 Then
Range("H1").Value = S

    
     For Var = 1 To 120
        If Sheets("teste").Cells(Var, 3) = S Then

            total = total + Sheets("teste").Cells(Var, 2)

        Else

           total = total

        End If

     Next Var
     
     Sheets("teste").Range("H2").Value = total
   

Else
' user clicked cancel
End If
'
    Application.Goto Reference:="popup"
    ActiveWorkbook.Save
    Application.WindowState = xlNormal
ActiveWorkbook.Save
    Range("N13").Select
End Sub

[Andrew-R]

Yes, it's always going to be slower going through the rows 1 by 1, rather than using .Find to find the values you're looking for.

Aside from that, you don't need:

Else

   total=total

Taking those 2 lines out won't change how your code works at all.

[Adam Brave]

Can you please explain me this piece of code :

Set rngMatchWord = Columns(lWORD_COLUMN).Find(sWordToMatch, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
  
  If Not rngMatchWord Is Nothing Then
  
    sFirstAddress = rngMatchWord.Address

I really dont get it, what is the xlValues and the xlWhole? What do you put in the rngMatchWord variable? What do the "If" condition in your case?

Thanks

[Andrew-R]

Using the Find method is the code version of pressing Ctrl-F in Excel and then typing in a word to search for - LookIn:=xlValues tells Excel to search in the values, rather than in the formula, and Lookat:=xlWhole says that the entire cell has to match the sWordToMatch, not just part of it. So searching for "food" wouldn't match "foodstuff", for example.

What Find returns is a range object, not a variable in the normal sense, but something we can treat as a range. So if we wanted we could put in a line of code saying:

rngMatchWord.Interior.Color=xlRed

And the macro would highlight in red each match found.

The second line of code you quoted checks that a range has been returned. If there is no match for the search term then rngMatchWord will be recorded as Nothing, and if there is no match then we do not need to continue.

My code calls the Find method in a loop to get every match, but as with Ctrl-F it will loop back around to the beginning once it has found all of the matches. To stop it doing that we record the cell address of the first match, e.g. $B$4 and stop looping when we get back round to that address.

So, what's in the rngMatchWord variable? Lots of things, we could examine rngMatchWord.Value if we wanted to see what value it has, but that value will be the one we just searched for, so what's important is not what's in rngMatchWord, but where that range is, because we know it's one cell to the right of a value we want to count, so rngMatchWord.Offset(0,-1).Value gets the count for us to add to our running total.

If you're new to coding, or used to more old-fashioned languages, it can take a while to get your head around these object variables, but they are incredibly powerful and can make coding a lot easier.

[Adam Brave]

Thanks for the explanation, now I get it

[Adam Brave]

Just one more question Andrew, when you do:

lCount = lCount + rngMatchWord.Offset(lROW_OFFSET, lCOL_OFFSET).Value

I guess I understand the lCOL_OFFSET, you want to check the cell on the left side of the cell you found but I dont understand the lROW_OFFSET = 0. Will you not get stuck in the row zero that way?

[Andrew-R]

No, because the row offset is relative to the range rngMatchWord, so a row offset of 0 is just saying to stay on the same row.

If, say, rngMatchWord is set to the range A1 then rngMatchWord.Offset(0,1) refers to cell B1 and rngMatchWord.Offset(1,0) to cell A2.

[Adam Brave]

Andrew, how do I make result be presented with decimal places?

[Andrew-R]

Is the result going in a cell or just being displayed in a msgbox?

If in a cell just format that cell, if a box use the Format function.