VBA / Macro / Formula Advice

modstop

New Member
Joined
Aug 18, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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?

1615580672343.png


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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
That is telling you that you do not have a worksheet in that workbook with the name "HELINFO".
Is it in the same workbook?
Are you sure that is the exact name?
Double-click on the name in the tab so it goes into edit, and see if there are any extra characters anywhere (i.e. special blank spaces, etc).
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
If you cannot figure it out, run this little macro, and paste the results of the Message Box here:
VBA Code:
Sub ListTabName()

    Dim ws As Worksheet
    Dim str As String
    
    For Each ws In Worksheets
        str = str & ws.Name & "," & vbCrLf
    Next ws
    
    MsgBox str
    
End Sub
 

modstop

New Member
Joined
Aug 18, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
So i got it to run correctly however it just copied "product code" into each box where i needed the actual Product code to go
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
The code was written for column A, not column I. So I think we just need to make this change:
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 I with data on main sheet (ws1)
    lr = ws1.Cells(Rows.Count, "I").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 I on sheet 1 is "black"
        If ws1.Cells(r1, "I") = "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
Don't forget to change the ws1 and ws2 references in the code to match your sheet names.
 
Solution

modstop

New Member
Joined
Aug 18, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

1615998128178.png


this is the current reset from it
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
this is the current reset from it
OK, it looks like you changed another important detail on me!

In your original example, you were looking for "black". But in your example posted above, you have "Black".
It matters!

Update the VBA code to look for "Black" instead of "black".

If you may have a mix of "black" and "Black", and want both cases to change the value being brought black, then change that line of code to:
VBA Code:
If UCase(ws1.Cells(r1, "I")) = "BLACK" Then
 

modstop

New Member
Joined
Aug 18, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

My apologies Joe,

Will this stop the code from bring back the result that's in A1 and work its way down the list now?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
Will this stop the code from bring back the result that's in A1 and work its way down the list now?
Yes. Try it and see!

If it does not work properly, then please post the following:
1. Image of the first ten rows of your first sheet, being sure to expand column M far enough so we can see what is in it.
2. Image of the first ten rows of your second sheet.
 

modstop

New Member
Joined
Aug 18, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
It worked! you have no idea how much time you have saved me, i assume i can reuse this bit of VBA code for other spreadsheets if it fits the same parameters?

Appreciate the help!

you are a lifesaver
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
It worked! you have no idea how much time you have saved me, i assume i can reuse this bit of VBA code for other spreadsheets if it fits the same parameters?

Appreciate the help!

you are a lifesaver
You are welcome.

Yes, you should be able to re-use this code for similar problems, with a few minor edits.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,483
Messages
5,636,601
Members
416,927
Latest member
BNM8V6

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top