1

I'm trying to find all values in column W that contain a colon, remove the colon of the value in that cell, and note the XID in column A of the same row. Then see if there are any instances of the value within the strings in columns CT & CU in the rows that have that XID. If any instances in columns CT & CU remove said colon as well.

The thing about columns CT & CU is there are other colons in the strings, so the specific colon is to be removed.

Example: Say column W contains "Less: Than Minimum" and on the same row the XID in row A would be "562670-6". Now that the loop has noted the XID that has the occurrence of a colon (in this case "Less: Than Minimum") a smaller loop inside the big loop will look through all of the cells in columns CT & CU that have the same XID in column A to find any cells that CONTAIN "Less: Than Minimum" (which in the photo would be cell CT2 where it contains "PROP:LESS: THAN MINIMUM:THERE WILL BE.....") and remove the colon (so it would end up being "PROP:LESS THAN MINIMUM:THERE WILL BE.....").

Since there are multiple colons in each cell in columns CT & CU my idea is to look for ":Less: Than Minimum:" because there will always be a colon at the beginning and end of that string.

I've been trying to accomplish this task and got to this point

Option Explicit

Public Sub colonCheck()
Dim rng As Range, aCell As Range, bCell As Range, uRng As Range, uCell As Range
Dim endRange As Long
Dim opName As String, opName2 As String
Dim xid As String

endRange = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

Set rng = ActiveSheet.Range("W1:W" & endRange)

Set aCell = rng.Find(What:=":", LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)

If Not aCell Is Nothing Then
    Set bCell = aCell
    opName = ":" & aCell.Value & ":"
    'Type mismatch on rng = Replace(rng, ":", "")
    rng = Replace(rng, ":", "")
    aCell = rng
    'set corrected value (sans-colon) to opName2
    opName2 = aCell.Value

    xid = ActiveSheet.Range("A" & aCell.Row).Value
    'Whatever we add here we need to repeat in the if statement after do
    'We have the option name and the xid associated with it
    'Now we have to do a find in the upcharges column to see if we find the opName
    'Then we do an if statement and only execute if the the Column A XID value matches
    'the current xid value we have now
    Set uRng = ActiveSheet.Range("W2:W" & endRange)

    Set uCell = uRng.Find(What:=opName, LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
    If Not uCell Is Nothing And ActiveSheet.Range("A" & uCell.Row).Value = xid Then
            uRng = Replace(uRng, opName, opName2)
            uCell = uRng
    End If
    'Above code was added

    Do
        Set aCell = rng.FindNext(After:=aCell)

        If Not aCell Is Nothing Then
            If aCell.Address = bCell.Address Then Exit Do
            'Repeat above code in here so it loops
            opName = ":" & aCell.Value & ":"
            rng = Replace(rng, ":", "")
            aCell = rng
            'set corrected value (sans-colon) to opName2
            opName2 = aCell.Value

            xid = ActiveSheet.Range("A" & aCell.Row).Value
            'Whatever we add here we need to repeat in the if statement after do
            'We have the option name and the xid associated with it
            'Now we have to do a find in the upcharges column to see if we find the opName
            'Then we do an if statement and only execute if the the Column A XID value matches
            'the current xid value we have now
            Set uRng = ActiveSheet.Range("W2:W" & endRange)
            Do
                Set uCell = uRng.FindNext(After:=uCell)
                If Not uCell Is Nothing Then
                    Set uCell = uRng.Find(What:=opName, LookIn:=xlValues, _
                        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False)
                    If Not uCell Is Nothing And ActiveSheet.Range("A" & uCell.Row).Value = xid Then
                        uRng = Replace(uRng, opName, opName2)
                        uCell = uRng
                    End If
                Else
                    Exit Do
                End If
            Loop
            'Above code was added
        Else
            Exit Do
        End If
    Loop
End If
End Sub

I receive a Type Mismatch Error at line 

rng = Replace(rng, ":", "")

I came across an answer on this question that said that "Replace works only with string variables," so I figure that might be what the issue is?

How could I edit the above code to accomplish what I'm looking to do? Is there a different approach (that is still accomplished via VBA). Here is a screenshot of the layout and values for a reference

Update/Revision

Okay, so I've progressed a bit by being able to successfully find and replace the first instance of the colon option "Less Than: Minimum" is changed to "Less Than Minimum" both in columns W & CT. The problem I face now is getting the Do loops to function correctly. Here is the point I've come to (I've included some comments in code to hopefully help guide anyone who wants to try and help)

Option Explicit

Public Sub MarkDuplicates()
Dim rng As Range, aCell As Range, bCell As Range, uRng As Range, uCell As Range, sCell As Range
Dim endRange As Long
Dim opName As String, opName2 As String
Dim xid As String

endRange = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

Set rng = ActiveSheet.Range("W1:W" & endRange)

Set aCell = rng.Find(What:=":", LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)

If Not aCell Is Nothing Then
    'bCell now holds the original cell that found
    Set bCell = aCell
    'Add colon to beginning and end of string to ensure we only find and replace the right portion over in upcharge column
    opName = ":" & aCell.Value & ":"
    'Correct the value in column W
    aCell = Replace(ActiveSheet.Range("W" & aCell.Row).Value, ":", "")
    'Set corrected value (sans-colon) to opName2 and add colon to beginning and end of string
    opName2 = ":" & aCell.Value & ":"
    'Note the XID of the current row so we can ensure we look for the right upcharge
    xid = ActiveSheet.Range("A" & aCell.Row).Value
    'We have the option name and the xid associated with it
    'Now we have to do a find in the upcharges column to see if we find the opName
    'Then we do an if statement and only execute if the the Column A XID value matches
    'the current xid value we have now
    Set uRng = ActiveSheet.Range("CT2:CU" & endRange)
    'Set uCell to the first instance of opName
    Set uCell = uRng.Find(What:=opName, LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
    'If there is an instance of opName and uCell has the value check if the xid matches to ensure we're changing the right upcharge
    If Not uCell Is Nothing And ActiveSheet.Range("A" & uCell.Row).Value = xid Then
        Set sCell = uCell
        'If so then replace the string in the upcharge with the sans-colon version of the string
        uCell = Replace(ActiveSheet.Range("CT" & uCell.Row).Value, opName, opName2)
    End If

    Do
        '>>>The .FindNext here returns Empty<<<
        Set aCell = rng.FindNext(After:=aCell)
        If Not aCell Is Nothing Then
            'if aCell and bCell match then we've cycled through all the instances of option names with colons so we exit the loop
            If aCell.Address = bCell.Address Then Exit Do
            'Add colon to beginning and end of string to ensure we only find and replace the right portion over in upcharge column
            opName = ":" & aCell.Value & ":"
            'Correct the value in column W (Option_Name)
            aCell = Replace(ActiveSheet.Range("W" & aCell.Row).Value, ":", "")
            'Set corrected value (sans-colon) to opName2 and add colon to beginning and end of string
            opName2 = ":" & aCell.Value & ":"
            'Note the XID of the current row so we can ensure we look for the right upcharge
            xid = ActiveSheet.Range("A" & aCell.Row).Value

            Do

                Set uCell = uRng.FindNext(After:=uCell)
                If Not uCell Is Nothing Then
                    'Check to make sure we haven't already cycled through all the upcharge instances
                    If uCell.Address = sCell.Address Then Exit Do
                    'Correct the value in column CT
                    uCell = Replace(ActiveSheet.Range("CT" & uCell.Row).Value, opName, opName2)
                Else
                    Exit Do
                End If
            Loop
        Else
            Exit Do
        End If
    Loop
End If
End Sub

As I commented in the code, I seem to be getting tied up at the very beginning of the first Do Loop at the line

Do
        '>>>The .FindNext here returns Empty<<<
        Set aCell = rng.FindNext(After:=aCell)

The .FindNext(After:=aCell) returns Empty for some reason even though I've place a colon in the cells with "Drop Shipments: - ....." & "SHOP:Drop Shipments: - ....."

Any idea why or any idea how I can fix this?

Improve this question
2
  • I think what you're looking to do may be easier using a formula rather than VBA. Is this an option? If so can you just use the replace function to replace the colons with "" Commented Jan 23, 2016 at 4:33
  • Unfortunately that is not an option. Also, simply using the replace function to remove the colons in the way you describe would remove colons that still need to remain in columns CT & CU Commented Jan 23, 2016 at 5:55

3 Answers 3

Reset to default
1

You should loop through all the cells like this:

For i = 1 To endRange
    If Not aCell Is Nothing Then

        opName = ":" & aCell.Value & ":"

        aCell = Replace(ActiveSheet.Range("W" & aCell.Row).Value, ":", "")

        opName2 = ":" & aCell.Value & ":"

        xid = ActiveSheet.Range("A" & aCell.Row).Value
        Set uRng = ActiveSheet.Range("CT2:CU" & endRange)
        Set uCell = uRng.Find(What:=opName, LookIn:=xlValues, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)

        If Not uCell Is Nothing And ActiveSheet.Range("A" & uCell.Row).Value = xid Then
            Set sCell = uCell

            uCell = Replace(ActiveSheet.Range("CT" & uCell.Row).Value, opName, opName2)
        End If
Next i

i is just the counter here but you could use it as row index:

Cells(i, "W") 'Cells(RowIndex, ColumnIndex) works great for single cells

If you want to do more in this loop, I would also recommend writing functions which you can call with certain parameters.

For example (not a good one):

Function Renaming(Cell as Range)
    Renaming = ":" Cell.Value ":"
End Function

Then you could call the function:

Call Renaming(aCell)

I believe this would help you a little.

Also you don't need to give the range of aCell to bCell as this will stay the same. If you want to save the value somewhere then you'll need to declare bCell as String and then do the following:

bCell = aCell.Value

Otherwise this part of the code is pretty useless as the range of your cell will not change until you're done with your code.

I'm a newby in VBA myself, but if any of the code works for you, don't hesitate to use it. If there are any suggestions for better code, I'd love to read the comments :)

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

You raised some very good points and had some very out of the box thinking, so I'm very grateful you took the time to share! You've helped me get the rest of the way to completing my final product, thank you!
Actually, I am very happy that it worked, thanks for your feedback!! :) I have only been working for around 3 months with vba but on mac this was so complicated that I had to google a looooot. So now if I can help, i will try to do so :)
Your input certainly did :) I, myself, have been working with vba on and off for the last few years and have really honed in on it the last year or so, so I feel I've come a long way (via googling and asking plenty of questions like yourself). I'm to the point where I usually need some sort of foundation for a problem, then I can build up around that. Hence, why your feedback helped me the rest of the way!
1

Your type mismatch is, I think, because you are attempting to use replace (which operates on strings) across a range. You will instead need to loop through each element of the range and perform the replace. So something like:

Dim i As Integer
i=1
While i <= endRange
  Replace(ActiveSheet.Cells(i,23).Value, ":", "")
  i=i+1
Wend
Improve this answer

2 Comments

The issue with that is I can't loop through and correct all the values of column W that need correcting at once since if you look below the first replace there are two more replaces that need to be done for each instance. Unless I'm missing something obvious. In theory, do you think that your idea could be applied in a way that I could place all the code starting from right before the first replace statement? Do you think you could help me out with that?
I'm not sure I entirely understand what you are asking. You can definitely do more stuff inside that loop though. That is basically just doing down all of your rows, so where I have put the replace you can put more on other lines to do other cells or anything else needed on that row at the same time. Just reference the other columns where I have the 23 (which is W).
1

With some trial and error (and help from @Kathara in pointing out a few loose ends to clean up and suggesting a way to go about my loop) I've finally come to a fully working solution. However, instead of looping through the option column and then looping through the upcharge criteria 1 and upcharge criteria 2 columns each time I come across an option name with a colon I went with the Find() method since I know that each time I find the first value from the top of the Option Name column the value will be one of the first few that are found looking from the top down from the upcharge columns. I've also decided to split uRng into two ranges (uRng1 for upcharge criteria 1 and uRng2 for upcharge criteria 2) and check uRng2 right after each time I check uRng1, ensuring that I replace the option name in both columns. I removed the bCell & sCell range variables because as Kathara pointed out, they are not vital to the Sub. In fact, there were simply there in an example I used to build my Sub from, so that's where they came from (good eye Kathara!). I also realized with the help of @andrewf that I was not implementing the Replace() function correctly as I was providing a range inside it and not the value of the current cell of that range. Finally, before anyone says that I should I kept the Option Compare Text in my code, I realized that it would not fly later on in my overall project since this is one sub that will be combined with about 10 others to make my final product. So, in place of that I fell upon the UCase() function, which fits the bill for exactly what I need to accomplish. So, without further ado, below is completed code. If anyone in the future can take any bit of knowledge or is able to use any tidbit from my work to help them I will be happy knowing I was able to help in any way.

Sub dupOpCheck()
Dim rng As Range, aCell As Range, uRng1 As Range, uRng2 As Range, uCell As Range
Dim endRange As Long
Dim opName As String, opName2 As String
Dim xid As String

endRange = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

Set rng = ActiveSheet.Range("W1:W" & endRange)

Set aCell = rng.Find(What:=":", LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)

If Not aCell Is Nothing Then
    'Add colon to beginning and end of string to ensure we only find and replace the right
    'portion over in upcharge column
    opName = ":" & aCell.Value & ":"
    'Correct the value in column W
    aCell = Replace(ActiveSheet.Range("W" & aCell.Row).Value, ":", "")
    'Set corrected value (sans-colon) to opName2 and add colon to beginning and
    'end of string
    opName2 = ":" & aCell.Value & ":"
    'Note the XID of the current row so we can ensure we look for the right upcharge
    xid = ActiveSheet.Range("A" & aCell.Row).Value
    'We have the option name and the xid associated with it
    'Now we have to do a find in the upcharges column to see if we find the opName
    'Then we do an if statement and only execute if the the Column A XID value matches
    'the current xid value we have now
    Set uRng1 = ActiveSheet.Range("CT1:CT" & endRange)
    Set uRng2 = ActiveSheet.Range("CU1:CU" & endRange)
    'Convert uRng1 & uRng2 to all uppercase just to make sure they will be detected when using Find

    'Set uCell to the first instance of opName
    Set uCell = uRng1.Find(What:=UCase(opName), LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
    'If there is an instance of opName and uCell has the value check if the xid matches
    'to ensure we 're changing the right upcharge
    If Not uCell Is Nothing Then
        If ActiveSheet.Range("A" & uCell.Row).Value = xid Then
             'If so then replace the string in the upcharge with the sans-colon version of the string
             uCell = Replace(UCase(ActiveSheet.Range("CT" & uCell.Row).Value), UCase(opName), UCase(opName2))
        End If
        'Now we look in upcharge_criteria_2 column
        Set uCell = uRng2.Find(What:=UCase(opName), LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        If Not uCell Is Nothing Then
            If ActiveSheet.Range("A" & uCell.Row).Value = xid Then
                'If so then replace the string in the upcharge with the sans-colon version of the string
                uCell = Replace(UCase(ActiveSheet.Range("CU" & uCell.Row).Value), UCase(opName), UCase(opName2))
            End If
        End If
    Else
        'Now we just look in upcharge_criteria_2 column since we didn't find an instance in upcharge_criteria_1 column
        Set uCell = uRng2.Find(What:=UCase(opName), LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        If Not uCell Is Nothing Then
            If ActiveSheet.Range("A" & uCell.Row).Value = xid Then
                'If so then replace the string in the upcharge with the sans-colon version of the string
                uCell = Replace(UCase(ActiveSheet.Range("CU" & uCell.Row).Value), UCase(opName), UCase(opName2))
            End If
        End If
    End If

    Do
        'Check for Options
        'Instead of After:=aCell we have to make a start of before aCell or maybe just start back at row 1?
        'What:=":", After:=aCell
        Set aCell = rng.Find(What:=":", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        If Not aCell Is Nothing Then
            'Add colon to beginning and end of string to ensure we only find and
            'replace the right portion over in upcharge column
            opName = ":" & aCell.Value & ":"
            'Correct the value in column W (Option_Name)
            aCell = Replace(ActiveSheet.Range("W" & aCell.Row).Value, ":", "")
            'Set corrected value (sans-colon) to opName2 and add colon to
            'beginning and end of string
            opName2 = ":" & aCell.Value & ":"
            'Note the XID of the current row so we can ensure we look for the right upcharge
            xid = ActiveSheet.Range("A" & aCell.Row).Value
            Do
                On Error GoTo D1
                'Check the upcharges
                Set uCell = uRng1.Find(What:=UCase(opName), LookIn:=xlValues, _
                        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False)
                If Not uCell Is Nothing Then
                    'Check to make sure we haven't already cycled through all
                    'the upcharge instances
                    If ActiveSheet.Range("A" & uCell.Row).Value = xid Then
                        'Correct the value in column CT
                        uCell = Replace(UCase(ActiveSheet.Range("CT" & uCell.Row).Value), UCase(opName), UCase(opName2))
                    End If
                    'Now we look in upcharge_criteria_2 column
                    Set uCell = uRng2.Find(What:=UCase(opName), LookIn:=xlValues, _
                        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False)
                    If Not uCell Is Nothing Then
                        If ActiveSheet.Range("A" & uCell.Row).Value = xid Then
                            'If so then replace the string in the upcharge with the sans-colon version of the string
                            uCell = Replace(UCase(ActiveSheet.Range("CU" & uCell.Row).Value), UCase(opName), UCase(opName2))
                        End If
                    End If
                Else
                    Set uCell = uRng2.Find(What:=UCase(opName), LookIn:=xlValues, _
                        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False)
                    If Not uCell Is Nothing Then
                    'Check to make sure we haven't already cycled through all
                    'the upcharge instances
                        If ActiveSheet.Range("A" & uCell.Row).Value = xid Then
                            'Correct the value in column CT
                            uCell = Replace(UCase(ActiveSheet.Range("CU" & uCell.Row).Value), UCase(opName), UCase(opName2))
                        End If
                    Else
D1:
                        Exit Do
                    End If
                End If
            Loop
        Else
            Exit Do
        End If
    Loop
End If
End Sub
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.