Sum offset from found range

[TheOneWeDreamOf]

Hi!

I want to sum the values in column E by a found value in column A
The answer of the sum must be displayed in a cell on another worksheet "NewData"

So for example:

Because the search word is "hello" the sum is E2 + E3 + E4 + E8

sumhello.png

This sum should be displayed in a cell on the other sheet

I got to this but it doesn't work. Don; t know how to sum the offsetted values. Perhaps I took a wrong turn with this

Sub SumFoundOffset()

Dim wsData As Worksheet
Set wsData = Worksheets("Data")

Dim wsNewData As Worksheet
Set wsNewData = Worksheets("NewData")

Dim dataLastRow As Long
Dim i As Long
dataLastRow = wsData.Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To dataLastRow
        If wsData.Cells(i, 1).Value = "Houten kozijn" Then
            
           wsNewData.Cells("A1") = WorksheetFunction.Sum wsData.Cells(i, 5).Value
        
        End If
Next

End Sub

Hope this is clear enough. There is a sample file attached. Any help would be more than welcome!

Thanks!



note: also posted this on a dutch support forum

[KOKOSEK]

Do you really need VBA for it?
You can use SUMIF function, like:

=SUMIF(A:A,"hello",E:E)

Check attached file.

[TheOneWeDreamOf]

Wow, really broke my brains over this in VBA and it's this simple... Thanks KOKOSEK

Still I would really like to see how its done in VBA

[KOKOSEK]

Sub SumFoundOffset()
Dim YourSum As Long
YourSum = 0
Dim wsData As Worksheet
Set wsData = Worksheets("Data")

Dim wsNewData As Worksheet
Set wsNewData = Worksheets("NewData")

Dim dataLastRow As Long
Dim i As Long
dataLastRow = wsData.Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To dataLastRow
        If wsData.Cells(i, 1).Value = "hello" Then
            YourSum = YourSum + wsData.Cells(i, 5).Value
            ' wsNewData.Cells("A1") = YourSum
        End If
Next
MsgBox ("Sum for hello is: " & YourSum)
End Sub

[TheOneWeDreamOf]

Thanks KOKOSEK.
I get the message box but the sum is not displayed in A1. When I remove the ' in front of wsNewData.Cells("A1") = YourSum I get an error.

What might be wrong?

[nigelog]

replace

("A1")

with

(1,1)

in that line

Possibly use an input box for the search items

Dim mySearch As Variant
mySearch = InputBox("Search for What")
For i = 1 To dataLastRow
        If wsData.Cells(i, 1).Value = mySearch Then
            YourSum = YourSum + wsData.Cells(i, 5).Value
            wsNewData.Cells(1, 1) = YourSum
        End If
Next

[TheOneWeDreamOf]

Thanks nigelog.

Although I don't understand why "A1" didn't do the trick.

[nigelog]

Could use with

wsNewData.Range("a1")

[KOKOSEK]

Could use with

wsNewData.Range("a1")

Sorry, I've missed this. Cells(1,1) or Range("A1") as nicelog wrote.