(Solved) – Find and extract data from Excel sheet and paste it into related columns using VBA

  • by

I have the following objective:

  1. Loop through a huge excel sheet (200,000 rows)
  2. Find some data based on matching parameters (the original file is an xml file, so structured data… but I am using a Mac, where the XML parser is not supported)
  3. Copy the data between double quote related to each variable
  4. Paste the value in the relative column

Additional constraints I have to face:

  • Every value to copy is between double quotes (this is “good news”, helping me to identify the right data to copy and paste)
  • Imagine the txt. data as a list of data objects (=> it’s sequence of purchases, with related info, made by customers). The macro should be able to loop through the list and copy paste the data, starting a new row every time a new ID purchase comes up. Good news is that every purchase is marked by a unique ID.

I’m providing below an example of input and output. I would really be grateful if someone could help me on this.




//EXPECTED OUTPUT (by running the VBA macro)
enter image description here

Here you can find also my VBA attempt, I leveraged some VBA code I already found, but didn’t succeed.

Public Sub TextDataToColumn() 
Dim val As Variant val = "PurchaseSegment DayDateTime" // it would be great to have a list of paramaters here...
Set c = Cells.Find(val, LookIn:=xlValues, MatchCase:=False) 
If Not c Is Nothing Then 
MsgBox "Value of val is found at " & c.Address & vbCrLf & c.Offset(0, 1).Value & vbCrLf & c.Offset(0, 2).Value 
 Set c = Cells.FindNext(c) 
 Loop While Not c Is Nothing And c.Address <> firstaddress 
 End If 
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *