Posts by KjBox

    What error do you get, and on which line of code?

    It all works for me, but then I had to hard code for the path to a picture.

    I could not update the paths in the data table because then refreshing the pivot caused an error because the power query tried to refresh too, but of course it could not.

    Could you send me a file containing all the images you have links to in the data table, and remove the power query from the data/pivot file.

    I can then do a full test of the code.


    Do you mean that the SSN can start off as more that a 4 digit number, and you need only the last 4 digits to display with all zeros that may be present in those last 4 digits?

    It would be best if you could give me some examples of what the SSN entries could be.

    A loop-less solution is possible, but because it has to be an object based code that has to make calls to the worksheet for each "Replacement". Also with object based code it is not possible to combine multiple "Replace" functions into a single line of code and have it case insensitive.

    An alternative is to use a single For....Next loop with an array based code.

    I am attaching 2 files with the same data, one uses the loop-less code and the other the array looping code.

    Personally I think the array based looping code is preferable. Not only is it likely to be slightly faster (depends on the number of rows of data), it is also more versatile for any future changes.

    Codes assigned to the buttons are:


    Array loop:

    Note that in both cases I have applied "General" format to the entire data range, that is to replace all Text formatting, which is a problem waiting to happen and should be avoided whenever possible.

    Hi, welcome to Ozgrid!

    When posting code you must place it in a "Code box". To do so, copy your code, then click the "</>" icon in the reply box menu bar, and paste code into the code box that will appear. I have done this for you in this instance.

    As for your code.

    Using "If......ElseIf.......End If" statements is far more than separate "If.....End If" statements even with End in each If statement, but better still is "Select Case.....Case....End Select"

    I have modified your first code to use Select case

    You can parse the required row into your other two codes by using Target.Row so there is no need to use ActRow = ActiveCell.Row.

    I have done that and improved your "With.....End With" statements. Further improvements could well be possible but I would need to see your workbook to know for sure.