mercredi 13 février 2019

Sum values in the range based on if condition and for each loop

I cannot get to work the following task - I am not sure if it does not wotk because I should use the arrays or it something in the code.

This is what I want to do: In the sheet I have 3 columns of long data (I will present only subset for ease of understanding). The first column shows label number, 2nd column the quanatities along label number, and 3rd the price. I want to write the code that will sum the price in for labels in columns 3 but only if Quantity in colyumn 2 is >= 1, otherwise it Quantity is, it should do nothing.

This is, the data before running the code:

enter image description here

ANd this is my dersired outcome:

enter image description here

So in the outcome, the Price in 3rd column is summed out of all values for Label1 and assigned to each label If Quantity in column 2 is >=1. For all the others, the price does not change because quantity is 0.

This is the code, I was trying to use but I am getting the mismatch for the ranges used. I am also not sure if maybe I should use arrays:

Sub test()
Dim cell As Range
Dim cell2 As Range
Dim rngLabel As Range
Dim rngQuantity As Range
Dim rngAmount As Range

Dim ws1 As Worksheet

Set ws1 = ThisWorkbook.Sheets("Sheet3")

With ws1
Set rngLabel = .Range(.Cells(3, 1), .Cells(.Rows.Count, 1))
Set rngQuantity = .Range(.Cells(3, 2), .Cells(.Rows.Count, 2))
Set rngAmount = .Range(.Cells(3, 3), .Cells(.Rows.Count, 3))


    For Each cell In rngLabel.Cells
        For Each cell2 In rngAmount.Cells
            If rngQuantity.Value >= 1 Then
            cell = WorksheetFunction.Sum(rngAmount)
            End If
        Next cell2
    Next cell

End With
End Sub

Do anybody be so kind and help me in this issue?

Aucun commentaire:

Enregistrer un commentaire