Macro Help

  • Is there a way to create a macro that creates

    "AC_VOLUME_4:Assets:HR Images:7:75:752:752388_V1.PSD"

    in column B from


    in column A?

    “AC_VOLUME_4:Assets:HR Images:” is standardized. The rest of the file path is based off the file name. The folder structure is always broken down to the first (3) digits of the file name.

    Thank your help in advance.

    [TABLE="width: 836"]





  • Re: Macro Help

    Does it need to be a macro?

    Could you not just use:

    ="AC_VOLUME_4:Assets:HR Images:" & "ReferenceWhereverTheNextBitComesFrom" & "752388_V1" & ".PSD"

    As a formula? Or is there a reason for using code?

  • Re: Macro Help

    No reason for using code except I thought it had to be done that way. This is for a list of numbers.

    Input Column is

    Desired Output Column is
    AC_VOLUME_4:Assets:HR Images:1:12:123:123456.PSD
    AC_VOLUME_4:Assets:HR Images:6:65:654:654321.PSD

    Does that make more sense?

  • Re: Macro Help

    Yes it makes more sense now.

    try this:

    ="AC_VOLUME_4:Assets:HR Images:" & left(InputCellRef, 1) & ":" & left(InputCellRef, 2) & ":" & left(InputCellRef, 3) & ":" & InputCellRef & ".PSD"

  • Re: Macro Help

    I should have confirmed, hopefully obvious though, that InputCellRef will need replaced with the cell you have input the reference data to.

  • Re: Macro Help

    No problem glad to help.

    As a general rule, if you only need to cut up the string in order to construct a new string and the location of the characters is consistent, then you can usually cobble something together quite easily using the substring commands (Left, Mid, Right) in a formula.

    If you need to start interpreting, altering the string in order to produce something that doesn't exist currently then start to veer towards the code angle.

    Code is more powerful, but a lot of people use it to over engineer their solutions which adds complexity and overheads where it's not needed.

    But if you have the time then by all means go for it, you need to try it to learn it.

    Just noticed it may be missing a period before the last part of the string "PSD" should be ".PSD". I've changed it up in the original post now.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!