Manipulating and populating the data from sheet X to sheet Y using dynamic VBA script

Tilly · Apr 23rd 2020

Hi guys, just wanted to ask for your suggestions on how to approach the 2 following issues in terms of writing the script that I need to populate data from sheet X into template within sheet Y.

1) Multiple items divided by “;” are in column B within sheet X and need to be split once moved to template Y, but in the separate row of the same column. E.g. x;g;h; in the cell B2 (sheet X) would need to be populated into template Y as B2 for x,B3 for g, B4 +h. How should I go about it?

2) I have a shortcut of signs in the identification section of the sheet X (lets say in column D), the values are in the following format (all in D2): (ID):1234;(IB): 321; (IC):213.

Now I need to move the following to template Y where ID = Identification; IB = Internet barcode and IC = Identification of court (as per the created lookup table). How do I write a code that reads from look up table and populates this data into new table as new expanded/ expressive form? E.g. (ID):1234;(IB): 321; (IC):213 in sheet X -> (Identification):1234;(Internet Barcode): 321; (Identification of Court):213 in sheet Y (e.g. also column D2)

3) This is related to issue 1..How do I copy multiple records from sheet X populating them into Y, if I want the next record to be populated into A column and the first (full) available row? Given the scenario in section 1 (i.e. according to example given above, the new record would start/be inserted in A5 as x;g;h was moved to B2-B4 in template Y).

PLEASE NOTE THIS IS JUST EXAMPLE AND THE FINAL SCRIPT SHOULD CATER FOR 1OOs OF RECORDS.

Thank you so much - Any guidance/help would be appreciated!

jolivanes · Apr 23rd 2020

Code

Sub AAAAA()
Dim c As Range, a, b, i As Long
For Each c In Sheets("Original file").Range("A2:A" & Sheets("Original file").Cells(Rows.Count, 1).End(xlUp).Row)
a = Split(c.Offset(, 1), ";")
b = Split(c.Offset(, 3), ";")
With Sheets("Template Y").Cells(Rows.Count, 2).End(xlUp).Offset(1)
.Offset(, -1) = c.Value
.Resize(UBound(a)) = Application.Transpose(a)
For i = LBound(b) To UBound(b)
.Offset(i, 2) = Sheets("Identification key").Columns(1).Find(Mid(Trim(b(i)), 2, 2), , , 1).Offset(, 1)
.Offset(i, 3) = Trim(Mid(b(i), InStr(b(i), ":") + 1))
Next i
End With
Next c
End Sub

Display More

jolivanes · Apr 25th 2020

Did it do what you wanted it to do?

Tilly · Apr 26th 2020

Hello Jolivanes!

First of all, thank you very much for your help. I am struggling to make it work, so let me be perhaps more complete in terms of my example! also, if you could write comments in the code it would help - thank you for that.

I need to transform a data input from the attached example as per follows and struggle to write a script that would cater for such needs. (Please see the attached).
The data shall be copied from "original" and the columns should be split as "transformed" based on the information in the "key" sheet.
The loop should then run row by row and next line shall be inserted into next available row as illustrated on example in attachment.
Many thanks for your guidance - I really appreciate it!

Regards,

Tilly

dangelor · Apr 26th 2020

Possibly...

Code

Sub transform()
    Dim rg As Range, d As Object
    Dim v As Variant, s As Variant, t As Variant
    Dim i As Long, lRow As Long

    With ThisWorkbook
        Set d = CreateObject("Scripting.Dictionary")
        Set rg = .Worksheets("Key").UsedRange
        For i = 2 To rg.Rows.Count
            d.Add rg(i, 1).Value, rg(i, 2).Value
        Next i
        v = .Worksheets("original").UsedRange
        With .Worksheets("transformed")
            .UsedRange.Offset(1).Clear
            For i = LBound(v) + 1 To UBound(v)
                lRow = .Cells(.Rows.Count, 3).End(xlUp).Row + 1
                .Cells(lRow, 1) = v(i, 1)
                s = Split(v(i, 2), ";")
                For Each t In s
                    .Cells(lRow, 2) = d(Mid(t, 2, InStr(2, t, "}") - 2))
                    .Cells(lRow, 3) = Right(t, Len(t) - InStrRev(t, "}"))
                    lRow = lRow + 1
                Next t
            Next i
        End With
    End With
End Sub

Display More

Manipulating and populating the data from sheet X to sheet Y using dynamic VBA script

Participate now!

Share

Tags