Understanding VBA Macro

[PresidentEvil]

Hi,

I have a macro created by one of my former colleagues. It a bit complex macro with multiple steps which includes application of mutiple formula. He is no longer working for us, and we have decided to move to power automate with Office scripts. I have been decoding each step and converting them into office script however one such step is very difficult to understand (at least for me). Any help on this will be highly appreciated.

Below is a part of macro that he designed. Not able to understand what's going on in this...any explanation step by step would be really helpful

Sub test()
Range("J4:M4").Select 'Range J4 to M4 has formula
Range(Selection, Selection.End(xlDown)).Select
Range("Z2") = Selection.Row + Selection.Rows.Count - 1
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    'Selection.Replace What:="Check", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
      Selection.Replace What:="Check", Replacement:="", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    'Selection.SpecialCells(xlCellTypeBlanks).Select
    Application.CutCopyMode = False
    'Selection.FormulaR1C1 = "=R[1]C"
    
    Range(Range("Z3")).Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = _
            "=IF(ISNA(MATCH(CONCATENATE(C[-9],""Text""),C[4],0)),"""",R[1]C)"

 'mutiple formulas like the above
End Sub

 

[RoryA]

It replaces all the formulas in columns J:M with static values, then clears any cells that contain just the word "Check". Then it populates any blank cells in the range specified in Z3 with that MATCH formula.

 

[RobP]

Hi,

Maybe if you Step Through it (using F8) a line at a time, it will make sense for you.

Then you can see what you want to do with your new script.

cheers
Rob

 

[PresidentEvil]

It replaces all the formulas in columns J:M with static values, then clears any cells that contain just the word "Check". Then it populates any blank cells in the range specified in Z3 with that MATCH formula.

Thanks, I was confused with the last part. If it's not too much of an ask, can you please help me in converting the formula. I mean what it means by C[-09], Is it column A? Break it down for me please... -

"=IF(ISNA(MATCH(CONCATENATE(C[-9],""Text""),C[4],0)),"""",R[1]C)"

 

[PresidentEvil]

Hi,

Maybe if you Step Through it (using F8) a line at a time, it will make sense for you.

Then you can see what you want to do with your new script.

cheers
Rob

I did try that, that's how I converted half of it into office script. However, this one seemed more complex than I thought it would be. The entire code is a mess, the creator of this macro did make sure to make it very confusing for anyone breaking it in future. Don't know why.

 

[RoryA]

Thanks, I was confused with the last part. If it's not too much of an ask, can you please help me in converting the formula. I mean what it means by C[-09], Is it column A? Break it down for me please... -

"=IF(ISNA(MATCH(CONCATENATE(C[-9],""Text""),C[4],0)),"""",R[1]C)"

C[-9] means the column 9 to the left of the one with the formula in it. C[4] means the column 4 to the right.