VBA / Macro / Formula Advice

[modstop]

Hi All,

So, I'm currently putting together a CSV file which is around 50k lines

I am having to copy and paste product codes manually into a certain number of cells and then move down to the next product code.

Does anyone have any advise on how to write a formula or is able to show me some vba or macro that i could use to enable it to copy and past down automatically and then know when to swap to the next product code?

So the new product code renews every time the column on the left says black.

Any help would be very much appreciated as this is taking a huge amount of time up

 

[Joe4]

How does it know what to change it to?
Where exactly are these product codes stored?
Are there exactly the same number of records listed for each product code?
If you have two defined lists, and want to produce the Cartesian Product of them, that should be able to be done without too much trouble using VBA.

 

[modstop]

How does it know what to change it to?
Where exactly are these product codes stored?
Are there exactly the same number of records listed for each product code?
If you have two defined lists, and want to produce the Cartesian Product of them, that should be able to be done without too much trouble using VBA.

Hi Joe,

So the list is stored in another Excel Workbook -

Its just a standard list of products like this

I know nothing about VBA hence the question - But i can easily move the list to the same workbook if that makes life easier

Thanks for the response!

 

[modstop]

Hi Joe,

So the list is stored in another Excel Workbook -

Its just a standard list of products like this

View attachment 34241

I know nothing about VBA hence the question - But i can easily move the list to the same workbook if that makes life easier

Thanks for the response!

9 times out of 10 there is the same amount of records - but there wil lalways be something that it can use to know when to change, be it colour, price, amount of pictures etc

 

[Joe4]

Yes, it would be easier to do if both lists were in the same file (they can be on different sheets).

Can you let me know where this data resides on sheet?
So, in your original post with pictures, what cell does the first entry occur on, and what cell are we putting the first product code in?

And on the sheet with the different product codes, what cell does that data start on?

Lastly, is it possible to run out of product codes before we get through all of our data?
If so, what do we do in that situation?

 

[modstop]

Hi Joe,

Apologies for the late reply, so the product code is in column A of Spreadsheet 2 but i can move to the same spreadsheet just a different page

And the product code need to go into column M of Spreadsheet 1

Starts from A2 in Spreadsheet 2

And it is possible yes but i can copy the remainder of the data down as long as needed to be

Thanks

 

[Joe4]

Try this VBA code:

Sub MyMacro()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim lr As Long
    Dim r1 As Long
    Dim r2 As Long
    
    Application.ScreenUpdating = False
    
'   Set worksheet variables
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
    
'   Find last row in column A with data on main sheet (ws1)
    lr = ws1.Cells(Rows.Count, "A").End(xlUp).Row
    
'   Initalize r2
    r2 = 1

'   Loop through all rows on sheet1
    For r1 = 1 To lr
'       Check to see in entry in column A on sheet 1 is "black"
        If ws1.Cells(r1, "A") = "black" Then
'           Increment row counter on sheet 2 by 1
            r2 = r2 + 1
        End If
'       Populate column M on ws1 with code from ws2
        ws1.Cells(r1, "M") = ws2.Cells(r2, "A")
    Next r1
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub

 

[modstop]

Hi Joe,

So just tried the above and its tell me that it cannot run due to the line that says - Set WS1 = Sheets ( " ")

i have set the names correctly i think

So basically

WS1
Column I has the colours in
Column M Is where the product code needs to go

WS2
Column A is where the product code is that needs to pull across

Apologies for this, my first ever experiance with macro's

 

[Joe4]

Are you getting an error message?
If so, what exactly does it say?

It sounds like you have not entered the correct name of the sheet in the code.
What are the names of your two sheets?

 

[modstop]

HI Joe,

Sheet with product codes is called "HELINFO TEST"
Sheets with colours in - "hel export test (version 1) - AutoRecovered)