A simple vba problem

[petar256]

Hi


Could you please tell me how to write the sum of each found cell in range

IF there is a value "DND" in a range I want to sum all found offset.value of DND and put the result in a desired cell.


This code only gives me last value of DND instance

Sub novi()



Dim dnd As String
Dim ofdnd As Range
Dim rng As Range


Set rng = Worksheets(1).Range("G9:G20")
Set rng2 = Worksheets(1).Range("J1")

For Each cell In rng

If cell.Value = "DND" Then

  rng2 = application.sum(cell.Offset(, 1).Value)

End If

Next cell

End Sub

[NeedForExcel]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[TMS]

rng2 = rng2 + cell.Offset(, 1).Value

[kvsrinivasamurthy]

Try this code

Sub novi()

Dim dnd As String
Dim cel As Range
Dim rng As Range
Dim X As Long

Set rng = Worksheets(1).Range("A1:A20")

For Each cel In rng

If cel.Value = "DND" Then

  Worksheets(2).Range("A1").Offset(X, 0) = "DND"
  Worksheets(2).Range("A1").Offset(X, 1) = cel.Offset(, 1).Value
  X = X + 1
End If

Next cel

End Sub

[TMS]

A couple of options:

Option Explicit

Sub novi()
' original with variables defined

Dim dnd As String
Dim ofdnd As Range
Dim rng As Range
Dim rng2 As Range
Dim cell As Range

Set rng = Worksheets(1).Range("G9:G20")
Set rng2 = Worksheets(1).Range("J1")

For Each cell In rng
    If cell.Value = "DND" Then
        rng2 = Application.Sum(cell.Offset(, 1).Value)
    End If
Next cell

End Sub

Sub novi_v2()
' amended version with loop

Dim rng As Range
Dim rng2 As Range
Dim cell As Range

Set rng = Worksheets(1).Range("G9:G20")
Set rng2 = Worksheets(1).Range("J1")

rng2.Value = 0

For Each cell In rng
    If cell.Value = "DND" Then
        rng2.Value = rng2.Value + cell.Offset(, 1).Value
    End If
Next cell

End Sub

Sub novi_v3()
' using SUMIF without loop

Dim rng As Range
Dim rng2 As Range

Set rng = Worksheets(1).Range("G9:G20")
Set rng2 = Worksheets(1).Range("J1")

rng2.Value = Application.WorksheetFunction.SumIf(rng, "DND", rng.Offset(, 1))

End Sub

[petar256]

A couple of options:

Option Explicit

Sub novi()
' original with variables defined

Dim dnd As String
Dim ofdnd As Range
Dim rng As Range
Dim rng2 As Range
Dim cell As Range

Set rng = Worksheets(1).Range("G9:G20")
Set rng2 = Worksheets(1).Range("J1")

For Each cell In rng
    If cell.Value = "DND" Then
        rng2 = Application.Sum(cell.Offset(, 1).Value)
    End If
Next cell

End Sub

Sub novi_v2()
' amended version with loop

Dim rng As Range
Dim rng2 As Range
Dim cell As Range

Set rng = Worksheets(1).Range("G9:G20")
Set rng2 = Worksheets(1).Range("J1")

rng2.Value = 0

For Each cell In rng
    If cell.Value = "DND" Then
        rng2.Value = rng2.Value + cell.Offset(, 1).Value
    End If
Next cell

End Sub

Sub novi_v3()
' using SUMIF without loop

Dim rng As Range
Dim rng2 As Range

Set rng = Worksheets(1).Range("G9:G20")
Set rng2 = Worksheets(1).Range("J1")

rng2.Value = Application.WorksheetFunction.SumIf(rng, "DND", rng.Offset(, 1))

End Sub

Thnk you! Exactly what I needed

[TMS]

You're welcome. Thanks for the rep.