How do I declare (Dim) the sum of Application Matches in VBA for Excel?

vba declare variable and assign value
application.match vba
vba declare global variable and assign value
vba set variable to cell value
excel vba global variable across modules
how to assign a value to a variable in excel
dim vba
vba data types

I am trying to declare the sum of three Application.Match row numbers. If I add all these parts to Watches I see Values for each of my matches (e.g.: 1, 1, 1), but when I add them up I get "0" in the watch Value. Below is the related code and watch results when debugged.

Dim S13 As Integer
 S13 = Application.Match(K(3), TF13, 0) + Application.Match(K(2), TC13, 0) + Application.Match(K(1), TB13, 0)
Dim SD13 As Integer
 SD13 = S13 / 3

Watches: Expression : Value : Type Watch : Application.Match(K(1), TB13, 0) : 1 : Variant/Double Watch : Application.Match(K(2), TC13, 0) : 1 : Variant/Double Watch : Application.Match(K(2), TF13, 0) : 1 : Variant/Double Watch : S13    : 0 : Integer Watch : SD13 : 0 : Integer

I tried "Long" and other declarations on S13, but I really only need it to be "Integer" because this number should exceed the type character limit.

BTW, I've tried to declare an easy formula like these below and still get "0" as a Value in the Watches.

Dim X As Integer: X = 3 / 3
Dim Y As Integer: Y = 1 + 1 + 1
Dim Z As Integer: Z = "1" + "1" + "1"

That said, I'm not so sure Application.Match has anything to do with the issue. Nonetheless, I figured I'd mention it for my case.

UPDATE: New Test Scenario I opened up a new workbook and Module, entered the code below and got the expected result, "2" for TEST1 and "1" for TEST2. Looks like I may have a different issue affecting my other workbook. Anybody have an thoughts off the top of their head to help me start my investigation?

Sub TEST()
Dim TEST1 As Integer: TEST1 = 1 + 1 ' Works
Dim TEST2 As Integer: TEST2 = 2 / 2 ' Works
MsgBox TEST1 & " : " & TEST2 ' Message is "2 : 1"
End Sub

PROBLEM SOLVED: I figured out my issue, which could not be seen by the info provided (sorry). My code was like:

ElseIf...
'Dim don't go here...
    Dim S13 As Integer: S13 = Application.Match(K(3), TF13, 0) _
      + Application.Match(K(2), TC13, 0) _
      + Application.Match(K(1), TB13, 0)
    Dim SD13 As Integer: SD13 = S13 / 3
ElseIf Not IsError(Application.Match(K(3), TF13, 0)) _
  And Not IsError(Application.Match(K(2), TC13, 0)) _
  And Not IsError(Application.Match(K(1), TB13, 0)) Then
    If <<some formula to help me validate match above (e.g.: all true and all same number)>> Then
      Worksheets("MATCHES").Range("$A" & ARR & ":" & "$F" & ARR) _
       = TR13.Range("$A" & SD13 & ":" & "$F" & SD13)
    End If
ElseIf...

That code placed my declarations before my ElseIf/Then (into the previous ElseIf) instead of placing it after the Then where the variables needed to be declared. Below is the correction.

ElseIf...
ElseIf Not IsError(Application.Match(K(3), TF13, 0)) _
  And Not IsError(Application.Match(K(2), TC13, 0)) _
  And Not IsError(Application.Match(K(1), TB13, 0)) Then
'Dim go here...
    Dim S13 As Integer: S13 = Application.Match(K(3), TF13, 0) _
      + Application.Match(K(2), TC13, 0) _
      + Application.Match(K(1), TB13, 0)
    Dim SD13 As Integer: SD13 = S13 / 3
    If <<some formula to help me validate match above (e.g.: all true and all same number)>> Then
      Worksheets("MATCHES").Range("$A" & ARR & ":" & "$F" & ARR) _
       = TR13.Range("$A" & SD13 & ":" & "$F" & SD13)
    End If
ElseIf...
Dim S13 As Integer
 S13 = CInt(Application.Match(K(3), TF13, 0)) + CInt(Application.Match(K(2), TC13, 0)) + CInt(Application.Match(K(1), TB13, 0))
 Dim SD13 As Integer
  SD13 = S13 \ 3

Declaring Variables in Excel VBA: Three Keys for Success, WorksheetFunction. object to call one of the built-in Excel worksheet functions. Application.WorksheetFunction.Sum. The SUM function returns the total value of the the result must be declared as a Variant and not a String. The VLOOKUP function returns the value in the same row after finding a matching value in the  Excel VBA Dim. DIM in VBA can be termed as it declares variable in different data types such as integer Boolean string or double etc. In any programming language, a variable needs to be declared to a specific data type such as X is a variable and if we define X as an integer which means we can store integer values in X.

Please try with this:

Dim S1 As Integer
Dim S2 As Integer
Dim S3 As Integer
S1 = Application.Match(K(3), Range("TF13"), 0) 
S2 = Application.Match(K(2), Range("TC13"), 0) 
S3 = Application.Match(K(1), Range("TB13"), 0)
Dim SD13 As Integer
SD13 = (S1 + S2 + S3) / 3

msgbox "S1: " & S1 & ", S2: " & S2 & ", S3: " & S3 & ", SD13: " & SD13

What the result S1, S2, S3 and SD13 now?

VBA Dim - A Complete Guide, Detailed tutorial about defining variables in Excel VBA. When you declare variables in VBA, you tell Visual Basic for Applications what is the results in the Visual Basic Editor displaying a list of possible matches to complete the entry. However, in addition to that particular way, variable declaration also helps reduce  The Excel SUM function returns the sum of all numbers in a specified range. Learn how to apply the Excel SUM function using excel and VBA.

Ok, so I figured out my issue, which could not be seen by the info provided. Sorry.

My code was like this:

ElseIf...
'Dim don't go here...
    Dim S13 As Integer: S13 = Application.Match(K(3), TF13, 0) _
      + Application.Match(K(2), TC13, 0) _
      + Application.Match(K(1), TB13, 0)
    Dim SD13 As Integer: SD13 = S13 / 3
ElseIf Not IsError(Application.Match(K(3), TF13, 0)) _
  And Not IsError(Application.Match(K(2), TC13, 0)) _
  And Not IsError(Application.Match(K(1), TB13, 0)) Then
    If <<some formula to help me validate match above (e.g.: all true and all same number)>> Then
      Worksheets("MATCHES").Range("$A" & ARR & ":" & "$F" & ARR) _
       = TR13.Range("$A" & SD13 & ":" & "$F" & SD13)
    End If
ElseIf...

This code placed my declarations before my ElseIf/Then (into the previous ElseIf) instead of placing it after the Then where the variables apparently needed to be declared. Below is the correction.

ElseIf...
ElseIf Not IsError(Application.Match(K(3), TF13, 0)) _
  And Not IsError(Application.Match(K(2), TC13, 0)) _
  And Not IsError(Application.Match(K(1), TB13, 0)) Then
'Dim go here...
    Dim S13 As Integer: S13 = Application.Match(K(3), TF13, 0) _
      + Application.Match(K(2), TC13, 0) _
      + Application.Match(K(1), TB13, 0)
    Dim SD13 As Integer: SD13 = S13 / 3
    If <<some formula to help me validate match above (e.g.: all true and all same number)>> Then
      Worksheets("MATCHES").Range("$A" & ARR & ":" & "$F" & ARR) _
       = TR13.Range("$A" & SD13 & ":" & "$F" & SD13)
    End If
ElseIf...

What Does Dim Mean When Coding VBA Macros?, If you like to create efficient Excel macros, it's important to assign the correct data reserves a pre-defined amount of memory in anticipation of the variable's use. If you declare a variable but fail to include any information for its data type, Object is for storing ranges; such as application, workbook, worksheet, and range​. Thank you for your sharing on Excel and Vba. I would like to know: is it possible to use index + macth in vba on an array variable? example: I want to store in a two-dimensional array variable named myWeekdays. (this table is not stored in an Excel sheet) 1, “Monday” 2, “Tuesday” 3, “Wednesday” 4, “Thursday” 5, “Friday” 6

VBA DIM: Learn how to Declare Variables in VBA [step-by-step], A local variable can be declared with a Dim or Static statement. the value 3 is entered, the message box will display the running total value to be 5. A module​-level variable remains in existence while Visual Basic is running In Microsoft Excel 97 and in later versions of Excel, follow these steps to insert  The Excel SUMIF function returns the sum of all numbers in a specified range based on a single criteria. Learn how to apply the Excel SUMIF function using Excel and VBA.

Excel Functions VBA Code, Dim is short for the word Dimension and it allows you to declare variable names and their type. What Does Dim Mean in VBA Excel Macros is one word and does not match the name of any VBA function, class, or accessor  To sum all values in a single column you can apply an Excel or a VBA method. The formula used to sum values in an entire column is driven by an Excel SUM function. In both the VBA and Excel examples the formula sums all of the numbers in column C. This is achieved through the use of the Excel SUM function.

Define Variables In VBA: Declare Variables And Assign Them , It's best practice to declare your Excel VBA variables before you use them. Declaring Duration: 7:32 Posted: May 17, 2018 VBA Match function looks for the position or row number of the lookup value in the table array i.e. in the main excel table. In a worksheet, lookup functions are an integral part of the excel. Some of the important lookup functions are VLOOKUP , HLOOKUP, INDEX, and MATCH .

Comments
  • Try using ` \ ` instead of ` / `, as you are returning it to an integer. Also, try CInt() around the Appilication.Match statements, as this is VBA Excel, not VB.Net docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/…
  • @Cryostasys I wish I can say this is helpful, but I'm not clear on how/where exactly to apply your suggestions.
  • I cannot reproduce your problem. Using your code, SD13 shows 1 in both the Locals and Watch windows.
  • @RonRosenfeld that's strange. I can't even get the simple examples to work. Hmm.
  • FYI, I added an "UPDATE" test scenario yielding proper results.
  • This didn't work. I received the same results... Thanks for trying.
  • I actually just copied and pasted the code into a quick check on an open workbook I have, and it functioned. It may be some issue then with your variables; K(3), TF13, K(2), TC13, and K(1), TB13.
  • I can't even get the simple examples to work.
  • @DanielT are K(1), K(2), K(3), TF13, TC13, and TB13 variables in your Macro? If you are trying to look in Cell TF13, you will need to use Range("TF13"), Rather than just putting it as TF13 - which is being treated as a Variable (An Array or Array Reference, to be specific).
  • Again, I just want you to know that I appreciate you responding. Also, I figured out my issue and posted as my answer.
  • Thanks, this looks good, but I figured out my issue and posted as my answer. I appreciate your response.