CODEWITHSUNDEEP
vbaexcel
mxx9000
mxx9000

Reputation: 1

VBA Auto populate depending on value in two separate rows

I couldn't find the answer to my question anywhere so here it goes.
I have a table which contains order IDs and Unit numbers. Order IDs have numerous Unit numbers and I'm only interested in Order IDs that have Unit numbers that are different from zero, even if they have some Unit numbers that are zero.
It looks something like this:
Order ID Unit Number XD23 0 XD23 0 XD23 1 XD23 0 CF25 0 CF25 0 CF25 2 LQ29 0 LQ29 0 LQ29 0

And would like to get this kind of output:

Order ID   Unit Number
  XD23            1
  XD23            1
  XD23            1
  XD23            1
  CF25            1
  CF25            1
  CF25            1
  LQ29            0
  LQ29            0
  LQ29            0

So every row of Order ID should have a value of 1 if anywhere within that Order ID an Unit number different from zero exists.
I tried creating a loop with if conditions but I'm pretty new to VBA so it wasn't successful.
Maybe I need a solution that checks former, current and next Order ID to evaluate if they differ so they could restart checking 0 & 1.
Thank you very much!

Edit: Code that I have - I tried to test this out for a single Order ID 

Sub Test1()

Dim rngID As Range
Dim rngUnit As Range
Dim dblMax As Double
Dim OrderID As OrderID
Dim OrderID As New OrderID

OrderID.ID = "CBR516038001"


 'Set range from which to determine largest value
 Set rngID = Sheet1.Range("I2:I1000000")
Set rngUnit = Sheet1.Range("AQ2:AQ1000000")


'Worksheet function MAX returns the largest value in a range
For Each OrderID.ID In rngID.Rows
    dblMax = OrderID.Value
Next

 For Each OrderID.ID In rngID.Rows
     rngUnit.Rows = dblMax
 Next


 End Sub

Upvotes: 0

Views: 60

Answers (1)

Vinicius B
Vinicius B

Reputation: 162

try using to 2 loops, the first loop to identity the Order ID that have Unit numbers that are different from zero and store this Order ID in an array, and the second loop you change the Unit number if the Order ID is in the array

Upvotes: 1

Related Questions