VBA - Difficult issue to solve for - Any help/direction GREATLY appreciated!

[Nordicrx8]

Hey All!

I've been trying to wrap my head around how to solve for this file conversion and I keep running into dead ends. I'm hoping someone with more knowledge is able to come up with a creative solution! I made an example book to upload, but it doesn't look like I can attach a file so I posted screenshots.

So what I need to do is transform the "Pricing" tab/file into a new format. Its a time consuming process that I'm looking to automate.

Basically for every GIS number (in the match column) it has to be duplicated based on if there is data in column S (in orange, labeled "1x Weekly 2nd")

I wrote some VBA to help with this, and I have the output in the "Conversion" tab, but now I need a way to fill in the data based on the Pricing tab.

Most of the data will be duplicated, with exception of Item 10 (Blue header) as for the first entry of the GIS Number will need column R, and the second entry will need Column S.

To further complicate things, sometimes the values are not in column S or R, and can appear anywhere in "Item 10" (In blue) on the pricing tab. If it only has data in one of the columns in blue, then it only needs one line entry for the GIS number. But if any of the blue items have more than one data value per column, per row, then the GIS number needs a separate row with the second value.

Last but not least, the first GIS number data line needs to be called "Base" (In the orange cell named "Varies" on the conversion tab) and the second entry for the cells in blue needs to be called "Add On Base".

I know this was a lot, and I've been searching for days on how to get this to work. I've been trying to stich some VBA together, but it has been a flop. Any help or direction would be really appreciated!

I can also send over the example file I created if that makes things easier. I hope I explained the issue well enough, but if you need more information or something wasn't clear, please let me know!

Thank you so much!!


Tab Name: Pricing

Tab Name: Conversion

 

[MCLIFTO8]

You just need to break down the problem into manageable chunks.
It helps to think logically about it step by step.

Also take a look at debugging VBA code with breakpoints, the locals window and debug.Print.

Psuedo code

'name prefix = var type, string, long, etc
Public Const shtNamePricing As String = "Pricing"
Public Const shtNameConversion As String = "Conversion"

Dim lngCurrentRow As Long, lngCurrentColumn As Long
Dim strVaries As String
Dim boolFirstGISEntry As Boolean
Dim lngLastConvShtRow As Long

Application.ScreenUpdating = False
For lngCurrentRow = 1 to Range("A" & Rows.Count).End(xlUp).Row
    strVaries = "Base"
    boolFirstGISEntry = true

    For lngCurrentColumn = 16 to 23 ' Item 10 in columns P to W
        If Cells(lngCurrentRow,lngCurrentColumn) <> "" Then 'if current item 10 cell =/= "" (empty string)
            If Not boolFirstGISEntry Then Varies = "Add On Base"
            boolFirstGISEntry = false
            'can do this part many ways, most popularly with arrays and cell by cell. Array's are faster but will take a while to learn so I'll put down a simple cell method and you can decide whether you want to replace this loop with an array.
            'Insert a row into the conversion sheet with the GIS number, items 5-9, the [header of the cell that had data] and the [data of the cell that had data].
            With Sheets (shtNameConversion)
                lngLastConvShtRow = .Range("A" & Rows.Count).End(xlUp).Row
                .Range(lngLastConvShtRow,"B").Value2 = Sheets(shtNamePricing).Range(lngCurrentRow,"G").Value2 'GIS
                .Range(lngLastConvShtRow,"G").Value2 = Sheets(shtNamePricing).Range(lngCurrentRow,"O").Value2 'Item 9
                .Range(lngLastConvShtRow,"I").Value2 = Sheets(shtNamePricing).Range(lngCurrentRow,"M").Value2 'Item 7
                .Range(lngLastConvShtRow,"L").Value2 = Sheets(shtNamePricing).Range(1,lngCurrentColumn).Value2 'Header Item 10
                .Range(lngLastConvShtRow,"M").Value2 = Sheets(shtNamePricing).Range(lngCurrentRow,lngCurrentColumn).Value2 'Item 10
                .Range(lngLastConvShtRow,"S").Value2 = Sheets(shtNamePricing).Range(lngCurrentRow,"L").Value2 'Item 6
                .Range(lngLastConvShtRow,"U").Value2 = strVaries 'Varies
                .Range(lngLastConvShtRow,"V").Value2 = Sheets(shtNamePricing).Range(lngCurrentRow,"K").Value2 'Item5
            End With
        End If
    Next lngCurrentColumn
Next lngCurrentRow
Application.ScreenUpdating = True

Yeah something like that should get you pretty close.