CODEWITHSUNDEEP
excelvbavlookup
firezen
firezen

Reputation: 15

Using range from a column to sum corresponding values

My data has two columns.
Column C (Alphabets - A, B, C ...)
Column D (Value - 1, 2, 3 ...).
Each alphabet has its corresponding value.

I am trying to locate two cells (e.g. "G" to "J") from column C to set the range, then use this range to sum the corresponding values in column D (i.e. 7 + 8 +9 + 10 = 34).

I tried VLOOKUP and MATCH. The MATCH code returned Error 2015.

Sub loop1()
    'Dynamic range for cells with data
    Dim LastRow As Long
    LastRow = Worksheets("HU").Cells(Rows.Count, 2).End(xlUp).Row '
    LastRow1 = LastRow - 1
    Rng = "C1:D" & LastRow1
    matchrng = "C1:C" & LastRow1

    'Locate start cell in data
    alphabet_start = "G"
    locate_start = Application.Match(alphabet_start, matchrng, 0)

    'Determine end cell
    alphabet_end = "J"
    'WIP_end = Application.VLookup(alphabet_end, myrange, 2, False)
End Sub

Upvotes: 1

Views: 225

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149335

You can achieve what you want using Excel formulas as well.

=IFERROR(SUM(INDIRECT("D" & MATCH("G",C:C,0) & ":D" & MATCH("J",C:C,0))),"Not Found")

To understand this, let's create a basic formula and then break up the formula to understand how it works.

Let's say, your worksheet looks like

enter image description here

So what we want is =SUM(D7:D10)? So let's break this up.

The formula can be broken up as =SUM("D" & "7" & ":D" & "10"). Do not yet directly enter this in the cell. It will not work. Ok next thing is to make it dynamic so that we can get the 7 & 10. Now 7 is the position of G and 10 is the position of J which we can get using MATCH. For example

MATCH("G",C:C,0) and
MATCH("J",C:C,0)

So the formula can now be written as

=SUM("D" & MATCH("G",C:C,0) & ":D" & MATCH("J",C:C,0))

"D" & MATCH("G",C:C,0) & ":D" & MATCH("J",C:C,0) is just a string at the moment. You have to use INDIRECT to tell excel to consider it as a range. So the string now becomes

INDIRECT("D" & MATCH("G",C:C,0) & ":D" & MATCH("J",C:C,0))

and then putting it inside the SUM formula.

SUM(INDIRECT("D" & MATCH("G",C:C,0) & ":D" & MATCH("J",C:C,0)))

One final thing. The MATCH can return an error if no match is found so we can tackle that using =IFERROR(FORMULA,"ERROR MESSAGE")

So we get our final formula

=IFERROR(SUM(INDIRECT("D" & MATCH("G",C:C,0) & ":D" & MATCH("J",C:C,0))),"Not Found")

one more question.. if i want to present the sum in another sheet in the same workbook, how should i change the formula?

Let's say the data is in Sheet1 so the formula in another sheet will look like

=SUM(Sheet1!D7:D10)

So now follow the above logic and you will get

=IFERROR(SUM(INDIRECT("Sheet1!D" & MATCH("G",Sheet1!C:C,0) & ":D" & MATCH("J",Sheet1!C:C,0))),"Not Found")

Upvotes: 2

Anmol Kumar
Anmol Kumar

Reputation: 182

You were heading in right direction firezen. Below mentioned code is what you desire.

Sub loop1()
    'Dynamic range for cells with data
    Dim LastRow As Long, locate_start As Variant, locate_end As Variant, matchrng As Range
    Dim alphabet_start As String, alphabet_end As String

    LastRow = Worksheets("HU").Cells(Rows.Count, 3).End(xlUp).Row '<== Last row of data in Column C
    Set matchrng = ThisWorkbook.Worksheets("HU").Range("C1:C" & LastRow) '<== Set range to Search
    
    'Locate start cell in data
    alphabet_start = "G"
    locate_start = Application.Match(alphabet_start, matchrng, 0) '<== First position
    
    'Determine end cell
    alphabet_end = "J"
    locate_end = Application.Match(alphabet_end, matchrng, 0) '<== Last Position
    
    'If not is error to handle error If alphabet_start or alphabet_end is not found
    If Not IsError(locate_start) And Not IsError(locate_end) Then
        ' Here output is at the end of column D after empty space. You may change as required
        ThisWorkbook.Worksheets("HU").Range("D" & LastRow + 2) = _
        Application.Sum(ThisWorkbook.Worksheets("HU").Range(Cells(locate_start, 4), Cells(locate_end, 4)))
    End If
End Sub

Upvotes: 1

Related Questions