PDA

View Full Version : Update data from mapping table to another worksheet



Kartyk
04-27-2016, 01:13 AM
Hi,,

I need help urgently on a macro to fill a spreadsheet based on a mapping table.

Mapping sheet givves us the mapping table. Column header marked in yellow is the primary key.


Destination will have this header in any column of the worksheet. VBA should identify the
the column and then values under those and systematicallyy fill the rest of the columns
again spread anywere in the destination sheet.


For eg. Outright should have NDF, FX & Cash on the same row under respective headers.


Attaching file for better understanding.


Thanks
16025

Paul_Hossler
04-27-2016, 06:07 AM
1. Please rely on using the forum, and not Prive Messages

2. If I understand, something like this



Option Explicit
Sub test()
Dim wsDest As Worksheet, wsMap As Worksheet
Dim i As Long, hcell As Range, HeadersOne As Range
Dim colInstrumentType As Long, colProductType As Long, colProductSubType As Long, colProductCode As Long
Set wsDest = Sheets("Destination")
Set wsMap = Sheets("Mapping")

Application.ScreenUpdating = False

With Application.WorksheetFunction
colInstrumentType = .Match("Instrument Type", wsDest.Rows(1), 0)
colProductType = .Match("Product Type", wsDest.Rows(1), 0)
colProductSubType = .Match("Product sub Type", wsDest.Rows(1), 0)
colProductCode = .Match("Product Code", wsDest.Rows(1), 0)
End With


With wsDest
For i = 2 To wsMap.Cells(1, 1).CurrentRegion.Rows.Count
.Cells(i, colProductCode).Value = wsMap.Cells(i, 1).Value
.Cells(i, colInstrumentType).Value = wsMap.Cells(i, 2).Value
.Cells(i, colProductType).Value = wsMap.Cells(i, 3).Value
.Cells(i, colProductSubType).Value = wsMap.Cells(i, 4).Value
Next i
End With

Application.ScreenUpdating = True
End Sub



3. I always like to use meaningful names for variables, like wsDest instead of Sh1 since it helps me to follow my own code after 6 months.

4. If you need something else, just ask

Kartyk
04-27-2016, 06:19 AM
Thanks for the response.

However, not sure if the macro addresses the issue fully. Columns can be anywhere in the destination worksheet and there can be any number of rows for the primary key.

So, macro has to first identify the primary key column and read its value. Based on that it has to fill in other columns as well.

Paul_Hossler
04-27-2016, 06:39 AM
OK, your test data only used a primary key one time in Mapping sheet so it was easy to map to Destimation sheet

Maybe you could post a more realistic example with the amounts and types of data that are expected