Moving multiple pieces of information by PO numbers

[Davealot]

Greetings friends,
I've learned greatly from you all and I'm hoping to obtain your assistance yet again. I've self taught myself quite a bit in VBA coding, but this one is past my expertise. I've attached a workbook to show data that I pull straight off of a supply portal. I'm loading this into access in a specific format, I will be doing this daily once everything is up and running so I need to find some way to do this to alleviate any manual errors and to save time. I need to see if it's possible to have it scroll through Column A until it comes to a PO Number, Once it comes to a PO Number, take the due date in Column B "DDD : XX-XX-XXXX", store those all together on a second worksheet with the part number in column A, The PO number in column B Due Date in Column C and Qty Ordered from Column E, to be placed in Column D. I added in a second worksheet to workbook to show hopefully the finished product, Please help if you can and I appreciate the time and consideration greatly. Thank you gents/ladies!

 

[Marc L]

Hi !

All threads created in this forum need some assistance !
So it's better to write a title according to the need, the difficulty, …
it can much more motivate someone to read the thread !
​

 

[Davealot]

Done and done, Thank you kindly, Regards

 

[Marc L]

Try this demonstration amending the active worksheet :​

Sub Demo()
     Const PON = "PO Number: "
     Dim Rg As Range, D&, L&, V
     Set Rg = ActiveSheet.UsedRange.Columns(1).Find(PON, , xlValues, xlPart, , xlPrevious)
Do Until Rg Is Nothing
     L = Rg.End(xlDown).Row
     If D Then Rows(L + 1 & ":" & D).Delete Else Range("D1,F1:G1").EntireColumn.Delete
   Range(Rg(3, 2), Cells(L, 2)).Value = Split(Rg.Value, PON)(1)
     V = Split(Split(Rg(1, 2).Value, "DDD : ")(1), "-")
   Range(Rg(3, 3), Cells(L, 3)).Value = DateSerial(V(2), V(0), V(1))
     D = Rg.Row + 1
     Set Rg = ActiveSheet.UsedRange.Columns(1).Find(PON, Rg(0), , , , xlPrevious)
      If Rg.Row + 1 = D Then Exit Do
Loop
  If Not Rg Is Nothing Then Rows("1:" & D).Delete: Set Rg = Nothing
End Sub

Do you like it ? So thanks to click on bottom right Like !

Note for developers :
I use DateSerial function for an international environment.
As my date settings do not match with column B dates …
​

 

[Davealot]

Thank you for your assistance, I'm getting an error message on the "V" portion in which it states that "Variable not defined". Am I needing to declare V as something else?

 

[Marc L]

yes 'cause you use Option Explicit, declare V as Variant

or just add ,V at end of Dim codeline like in post #4 updated …​

 

[Davealot]

yes 'cause you use Option Explicit, declare V as Variant

or just add ,V at end of Dim codeline like in post #4 updated …​

Dude, you are the man! Thank you very much I am much obliged.