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:
ANd this is my dersired outcome:
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