Restrict Replace Module

[joeny0706]

Hi

I have a macro "Replaces" within the xlsm file "File attached called FlexibakeInvoice1.xlsm" and it replaces text. It has a different excel file it uses to know what to replace. I am having an issue that it is replacing items it should not. I have attached the xls files it uses to know what to replace and the raw data file that it searches. I copy the raw data into the xlsm file to do the conversions "by running the RunAll macro".

The issue is it is searching all the columns. I only need it to search column D for "Call ReplaceAllSheets(Worksheets("Data").Range("A1"))". Then for both "Call ReplaceAllSheets(Worksheets("Data").Range("D1")) and Call ReplaceAllSheets(Worksheets("Data").Range("G1")) it only needs to look in column C. It is replacing items in the wrong column. How can I restrict it to only search column D for products and only search column C for customers?

I attached the module exported within the .zip. I also attached the XLSM file that I paste all the raw data into and then run the macros.

Thanks all

[joeny0706]

Hi All


I have done some research but each time I try I get debug errors. Does anyone have any ideas how this can be done?

Thanks All

[Paul_Hossler]

Not sure about any other errors, but you can't name a sub the same as a module; Excel gets confused. The error message was pretty clear.

Capture1.JPG


Changing the names of 4 modules at least allows it to compile error free

Capture.JPG


I only ran ReplaceAll to the point is was looking to open the CSV

[joeny0706]

Thanks Paul


I must have attached a bad copy. The xlsm works good without any errors. I attached a copy that works good. Normally I will copy and paste the data I am converting into the xlsm file then I run the runall macro. The problem is the replaces module is searching the complete document to replace items. I need to restrict it to search only specific columns for specific items.
Example. I have a item number of 80. 80 should get converted into "Restaurant Breads:Ciabatte Italian" The module will search the worksheet and if it finds a 80 it will change it into the replacement value. I need it to only search the item column when replacing items.

I need it to only search column D when it is using Call ReplaceAllSheets(Worksheets("Data").Range("A1"))
So instead of searching the worksheet data it only needs to seach column D.

As of now it is searching the whole document.

The item is 80 that should be converted into

Restaurant Breads:Ciabatta Italian

But if it finds an 80 within the invoice number "Column B" it converts that also. so the invoice number 12380 gets changed into

123Restaurant Breads:Ciabatta Italian

So if it only searched column D when converting items that will stop that from happening.


When it is preforming this
for both "Call ReplaceAllSheets(Worksheets("Data").Range("D1")) and Call ReplaceAllSheets(Worksheets("Data").Range("G1"))

I need it to only look in column C.


I ran into debug errors when I was trying to adjust the replaces macro on my own. I was trying to find help online but was not able to fix it on my own.

[Paul_Hossler]

Not tested, but try changing these lines

    'do the replaces
    Call ReplaceAllSheets(Worksheets("Data").Range("A1"), 4)    '   <<<<<<<<<<<<<<<
    Call ReplaceAllSheets(Worksheets("Data").Range("D1"), 3)
    Call ReplaceAllSheets(Worksheets("Data").Range("G1"), 3)

--------------------------------------------------------------------------------------




'this sub is Private so that it's only usable in this module
Private Sub ReplaceAllSheets(R As Range, SearchColNum As Long)  '   <<<<<<<<<<<



'..........................


            ws.UsedRange.Columns(SearchColNum).Cells.Replace What:=r1.Cells(i, 1).Value, Replacement:=r1.Cells(i, 2).Value, _
                LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                SearchFormat:=False, ReplaceFormat:=False
        Next i

[joeny0706]

Paul


I have not had a chance to test that yet but I have another question that might be very simple for someone like you.

I need a module that will delete the row if it finds a negative number in column H. If that is possible and something easy for you I would appreciate if you could help me out.

As I have done testing that will not work. This issue is getting more complicated as I go. I can try to explain it again after I do more testing. For now I need to disregard this question.


I will be testing your above changes soon and let you know.

[joeny0706]

Paul

That seems to be working. Thanks for the help!!



I have figured out what I need for the other question I started above. The issue is when the sale price is more than the item price. I have a module that calculates discount for when the sale price is less but does not work and fails when it is more. So to fix the issue I need a module that will compare column F and G. If the number in column F is greater than the number in column G it needs to replace what is in column F with what is in column G.
Ya that gets confusing. But at the same time doing that will also make it so the discount is calculated correctly.

So below you will see row 92 column F is 3.75. Column G is 3. I would need to replace 3.75 with 3.

Is that possible and easy to do in a single module?

Should I start a new thread for this?


Thanks all


This is a great forum. It has helped me complete many needed jobs I have needed in my daily task. I would like to say thanks to all who have helped me over the years!!!

[Paul_Hossler]

So below you will see row 92 column F is 3.75. Column G is 3. I would need to replace 3.75 with 3.

Is that possible and easy to do in a single module?

Should I start a new thread for this?

1. Don't see any 'below'

2. Probably

3. No really needed

[joeny0706]

I currently have an xlsm file with 8 modules I run. If I had another that just did this task I could add it in and that would be great.


below you will see row 92 column F is 3.75. Column G is 3. I would need to replace 3.75 with 3. I would need this to do the same for all rows where column F is larger than column G

[Paul_Hossler]

You'll have to customize this, sheet names, etc.

Sub SaleDiscount()
    Dim i As Long
    
    For i = 2 To ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count
        If ActiveSheet.Cells(i, 6).Value > ActiveSheet.Cells(i, 7).Value Then
            ActiveSheet.Cells(i, 6).Value = ActiveSheet.Cells(i, 7).Value
        End If
    Next i




End Sub

[joeny0706]

Thanks Paul


That works perfect how you have it.

I do have an issue though that I did not think about. I have a module that adds the price per item in column G it then calculates the discount. The issue is I need it change the price like your module does but it needs to do it after it adds the price to column G and before it calculates the discount. I tried to add it in the middle but I dont have much knowledge with VBA.

I will keep trying but if you have time to take a look I would appreciate it.

Thanks

I discount module is attached.

[joeny0706]

I think I go it

Can you takee a quick look
I input your code. It is bold

Option Explicit

Sub AddDiscount()
    Dim wbItem As Workbook
    Dim wsInput As Worksheet
    Dim rData As Range, rData1 As Range, rLast As Range, rTemp As Range
    Dim iRow As Long, iItem As Long, i As Long
    Dim dDiscount As Double
    Dim vItems As Variant, vPrices As Variant
    
    Application.ScreenUpdating = False

    'get normal prices
    Workbooks.Add "C:\FlexibakeConversions\Flexitem prices.xlsx"   '   <<<<<<<<<<<<< change WB path
    Set wbItem = ActiveWorkbook
    
    Set rTemp = wbItem.Worksheets("Sheet1").Range("C1")
    Set rTemp = Range(rTemp, rTemp.End(xlDown))
    vItems = Application.WorksheetFunction.Transpose(rTemp)
    Set rTemp = wbItem.Worksheets("Sheet1").Range("E1")
    Set rTemp = Range(rTemp, rTemp.End(xlDown))
    vPrices = Application.WorksheetFunction.Transpose(rTemp)
    
    wbItem.Close False
    
    'set data
    Set wsInput = Worksheets("Input")        '   <<<<< Change WS name
    Set rLast = wsInput.Cells(1, wsInput.Columns.Count).End(xlToLeft)
    Set rData = Range(wsInput.Cells(1, 1), rLast).EntireColumn
    Set rData = Intersect(rData, wsInput.Cells(1, 1).CurrentRegion.EntireRow)
    Set rData1 = rData.Cells(2, 1).Resize(rData.Rows.Count - 1, rData.Columns.Count)
    
    'add Normal Prices
    With wsInput
        .Cells(1, 8).Value = "Discount Price"
        .Cells(1, 9).Value = "line class"
        For iRow = 2 To rData.Rows.Count
            iItem = 0
            On Error Resume Next
            iItem = Application.WorksheetFunction.Match(.Cells(iRow, 4).Value, vItems, 0)
            On Error GoTo 0
            
            If iItem > 0 Then .Cells(iRow, 7).Value = vPrices(iItem)
        Next iRow
    End With
    
    With wsInput

        For i = 2 To ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count
            If ActiveSheet.Cells(i, 6).Value > ActiveSheet.Cells(i, 7).Value Then
                ActiveSheet.Cells(i, 6).Value = ActiveSheet.Cells(i, 7).Value
            End If
        Next i

    End With
    
    'sort by invoice data and invoice number
    With wsInput.Sort
        .SortFields.Clear
        .SortFields.Add Key:=rData1.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add2 Key:=rData1.Columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange rData
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    'go up and add "20 Sales & Discount" to col D after invoice change
    With wsInput
        For iRow = rData.Rows.Count To 2 Step -1
            If .Cells(iRow + 1, 2).Value <> .Cells(iRow, 2).Value Then
                .Rows(iRow + 1).Insert
                .Cells(iRow + 1, 4).Value = "20 Sales & Discount"
                .Cells(iRow + 1, 9).Value = "2.5 Sales Promotional Discount"
            End If
        Next iRow
    End With

    'go down and calc discount and fill in data
    dDiscount = 0#
    With wsInput
        Set rData = .Cells(1, 1).CurrentRegion
        For iRow = 2 To rData.Rows.Count
            If Len(.Cells(iRow, 1).Value) > 0 Then
                dDiscount = dDiscount + .Cells(iRow, 5).Value * (.Cells(iRow, 7).Value - .Cells(iRow, 6).Value)
            
            Else
                .Cells(iRow, 1).Value = .Cells(iRow - 1, 1).Value
                .Cells(iRow, 2).Value = .Cells(iRow - 1, 2).Value
                .Cells(iRow, 3).Value = .Cells(iRow - 1, 3).Value
                .Cells(iRow, 8).Value = dDiscount
                dDiscount = 0#
            End If
        Next iRow
    End With

    'cleanup
    Application.ScreenUpdating = True
End Sub

[Paul_Hossler]

I think you'd want to remove the 'ActiveSheet.' from within the For/Next loop so that .Cells(1,1).... would be applied to wsInput nd NOT whatever the active sheet happens to be

With wsInput
    For i = 2 To .Cells(1, 1).CurrentRegion.Rows.Count
        If .Cells(i, 6).Value > .Cells(i, 7).Value Then
             .Cells(i, 6).Value = .Cells(i, 7).Value
        End If
    Next i
End With