@If

[Jafwiz]

I am not very good at Excel and this Forum has helped me in the past so i am asking another question. I have a file that is created automatically for our Bar Code software and i have to modify the file before i can use it. It has a column with a cost and every cell has a different cost. Some of the cells need to be modified. Example If the cell says $8.99 I need it to be $9.99. The dollar signs do not show on the sheet just shows 8.99. I know there must be a way to do this faster than one cell at a time copy and paste.

 

[Joe4]

There are not 2 files generated I made the 2nd file with the new prices it was a text file so I can use anything that has the price adjustments

OK, but the VBA code still needs to know how to access these "rules".
So how exactly do you want to do that?
We can hard-code them into the code, but that means that if they ever change, you would need to change the code.

Or we can store them in worksheet form in the Excel file that will hold the VBA code that we are going to create.
Then they would be much easier to update, as you just update the worksheet in the workbook.

 

[Jafwiz]

OK, but the VBA code still needs to know how to access these "rules".
So how exactly do you want to do that?
We can hard-code them into the code, but that means that if they ever change, you would need to change the code.

Or we can store them in worksheet form in the Excel file that will hold the VBA code that we are going to create.
Then they would be much easier to update, as you just update the worksheet in the workbook.

I am open to try whatever is easy for us to modify if needed.

 

[Joe4]

OK, here is what I did.

First, we want to create your file that has the list of changes to make and your VBA code.
Make sure this Excel file only has one sheet, and list your changes on that sheet in columns A and B in this format:

Now, go into VBA, insert a new module in the VB Editor (see here for directions on how to do that: Insert and run VBA macros in Excel - step-by-step guide.), and place this VBA code in that new Module:

Sub MyDataUpdater()

    Dim mcrWB As Workbook
    Dim datWB As Workbook
    Dim strFile As Variant
    Dim lr As Long, r As Long
    Dim oldVal As String, newVal As String
    Dim lr2 As Long
    Dim rng As Range
    
'   Capture macro workbook
    Set mcrWB = ThisWorkbook
    
'   Find last row in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Browse to open CSV file
    strFile = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv", Title:="Choose a CSV file to open", MultiSelect:=False)
    
'   Check selection and proceed accordingly
    If strFile <> False Then
'       Open and capture workbook
        Set datWB = Application.Workbooks.Open(strFile)
    Else
'       Exit if operation cancelled
        MsgBox "Operation cancelled", vbOKOnly
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
'   Reformat column D to show all 12 digits on data file
    Columns("D:D").NumberFormat = "000000000000"
    
'   Find last row in column G with data
    lr2 = Cells(Rows.Count, "G").End(xlUp).Row
    
'   Set range to replace
    Set rng = Range("G1:G" & lr2)
    
'   Loop through all prices changes in macro file listing
    For r = lr To 2 Step -1
'       Get old and new price values
        mcrWB.Activate
        oldVal = Round(Cells(r, "A").Value, 2)
        newVal = Round(Cells(r, "B").Value, 2)
'       Replace values in column G on data file
        datWB.Activate
        rng.Replace What:=oldVal, Replacement:=newVal, LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    Next r
    
'   Re-Save file and close
    datWB.Save
    datWB.Close
    
    MsgBox "Macro complete!"
    
    Application.ScreenUpdating = True
    
End Sub

Then, run the VBA code from the Macro menu. It will prompt you to browse/select your CSV data file.
The VBA code will then:
- open the data file
- re-format column D
- do all your replacements in column G
- re-save your CSV file
- close your CSV file

Take a look at your CSV file to see the changes.
NOTE: When viewing a CSV file, NEVER use Excel to view it if you want to see what the data actually looks like. Excel will perform its own conversions automatically on the data (which is why you are seeing the problems with column D). Always use a Text Editor like NotePad to view the CSV file, if you want to see what the unaltered data in it really looks like!

 

[Joe4]

One minor update.

This is only guaranteed to work if the values on your change list are sorted in ascending order. We can guarantee that by adding a line to sort those values before looping through them, like this:

Sub MyDataUpdater()

    Dim mcrWB As Workbook
    Dim datWB As Workbook
    Dim strFile As Variant
    Dim lr As Long, r As Long
    Dim oldVal As String, newVal As String
    Dim lr2 As Long
    Dim rng As Range
    
'   Capture macro workbook
    Set mcrWB = ThisWorkbook
    
'   Sort price change data in ascending order
    Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
    
'   Find last row in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Browse to open CSV file
    strFile = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv", Title:="Choose a CSV file to open", MultiSelect:=False)
    
'   Check selection and proceed accordingly
    If strFile <> False Then
'       Open and capture workbook
        Set datWB = Application.Workbooks.Open(strFile)
    Else
'       Exit if operation cancelled
        MsgBox "Operation cancelled", vbOKOnly
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
'   Reformat column D to show all 12 digits on data file
    Columns("D:D").NumberFormat = "000000000000"
    
'   Find last row in column G with data
    lr2 = Cells(Rows.Count, "G").End(xlUp).Row
    
'   Set range to replace
    Set rng = Range("G1:G" & lr2)
    
'   Loop through all prices changes in macro file listing
    For r = lr To 2 Step -1
'       Get old and new price values
        mcrWB.Activate
        oldVal = Round(Cells(r, "A").Value, 2)
        newVal = Round(Cells(r, "B").Value, 2)
'       Replace values in column G on data file
        datWB.Activate
        rng.Replace What:=oldVal, Replacement:=newVal, LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Next r
    
'   Re-Save file and close
    datWB.Save
    datWB.Close
    
    MsgBox "Macro complete!"
    
    Application.ScreenUpdating = True
    
End Sub

 

[Jafwiz]

One minor update.

This is only guaranteed to work if the values on your change list are sorted in ascending order. We can guarantee that by adding a line to sort those values before looping through them, like this:

Sub MyDataUpdater()

    Dim mcrWB As Workbook
    Dim datWB As Workbook
    Dim strFile As Variant
    Dim lr As Long, r As Long
    Dim oldVal As String, newVal As String
    Dim lr2 As Long
    Dim rng As Range
   
'   Capture macro workbook
    Set mcrWB = ThisWorkbook
   
'   Sort price change data in ascending order
    Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
   
'   Find last row in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Browse to open CSV file
    strFile = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv", Title:="Choose a CSV file to open", MultiSelect:=False)
   
'   Check selection and proceed accordingly
    If strFile <> False Then
'       Open and capture workbook
        Set datWB = Application.Workbooks.Open(strFile)
    Else
'       Exit if operation cancelled
        MsgBox "Operation cancelled", vbOKOnly
        Exit Sub
    End If
   
    Application.ScreenUpdating = False
   
'   Reformat column D to show all 12 digits on data file
    Columns("D:D").NumberFormat = "000000000000"
   
'   Find last row in column G with data
    lr2 = Cells(Rows.Count, "G").End(xlUp).Row
   
'   Set range to replace
    Set rng = Range("G1:G" & lr2)
   
'   Loop through all prices changes in macro file listing
    For r = lr To 2 Step -1
'       Get old and new price values
        mcrWB.Activate
        oldVal = Round(Cells(r, "A").Value, 2)
        newVal = Round(Cells(r, "B").Value, 2)
'       Replace values in column G on data file
        datWB.Activate
        rng.Replace What:=oldVal, Replacement:=newVal, LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Next r
   
'   Re-Save file and close
    datWB.Save
    datWB.Close
   
    MsgBox "Macro complete!"
   
    Application.ScreenUpdating = True
   
End Sub

They are not as you can see from the sheet that i posted . That can't be sorted because it comes out based on product type which is on the server it groups it before it generates the file that you see.

 

[Joe4]

They are not as you can see from the sheet that i posted . That can't be sorted because it comes out based on product type which is on the server it groups it before it generates the file that you see.

I am not talking about the data file. I am talking about the price change list, this part:

Column A needs to be in ascending order, for the reasons I explained earlier.

 

[Jafwiz]

I am not talking about the data file. I am talking about the price change list, this part:
View attachment 97703

Column A needs to be in ascending order, for the reasons I explained earlier.

Sorry that can be anyway it needs to be as i said i just created that in notepad

 

[Joe4]

Sorry that can be anyway it needs to be as i said i just created that in notepad

Yes, I know that you said that previously.
At the top of post 23, I mentioned exactly how this needs to be set-up in order to work with the code I created.
Since you need an Excel file to hold the VBA code anyway (unless you were to put it in your Personal Macro Workbook), I elected to put that data in the same file.
You can make any changes you need right there, whenever, as long as you maintain the same format as I described.

 

[Jafwiz]

Yes, I know that you said that previously.
At the top of post 23, I mentioned exactly how this needs to be set-up in order to work with the code I created.
Since you need an Excel file to hold the VBA code anyway (unless you were to put it in your Personal Macro Workbook), I elected to put that data in the same file.
You can make any changes you need right there, whenever, as long as you maintain the same format as I described.

I am trying to save the code you posted after i copied and pasted in the module it says to save it and click No? I do that but the save as screen does not open up? Also does this have to be done every time there is a new file to change?

 

[Joe4]

You should only have to save the file with the VBA once, when you first create it.
And then again whenever you change any of the values in the price mapping (if you want to save them).
Otherwise, you do not need to save it every time you run the code.