Find & Replace Sub

  • Hey everyone,


    I wrote this sub this morning to set a new Min / Max Value based off the Users Range they Input in a couple of cells to update the formula


    A lot of trial and error got me a functioning sub, I also wanted to update the formula on a different sheet "Daily Input" the code seems to process but nothing changes on the Daily Input sheet, just the status board.


    Now that I'm typing this I'm wondering if its because the code was written into the sheet and not a module?


    Other then that issue, I'd like some feedback on suggestions or improvements to the code, I'd say I'm in the beginning stages of learning how to use VBA so all feedback is welcomed!


    I thought about creating a userform to display the information or get the new range inputs using a msgbox



    The Code is looking at the Min-Max Values in G and F
    and replacing those values in the formula below


    Code
    =SUMPRODUCT(MAX((ROW(Data!$C$2:$C$3000))*(Data!$C$2:$C$3000=$B5)*(Data!$D$2:$D$3000=$AD$5)))



    I tried this code, and it was returning the correct Row without the need to adjust the ranges, but the Index was not returning the correct value when using this Code. Even though both the sumporducts returned the same Row Number, and the same index formula would return different results.

    Code
    =SUMPRODUCT(MAX((ROW(INDIRECT("Data!C1:C"&ROW(INDEX(Data!C:C,COUNTA(Data!C:C))))))*(INDIRECT("Data!C1:C"&ROW(INDEX(Data!C:C,COUNTA(Data!C:C))))=$B5)*(INDIRECT("Data!D1:D"&ROW(INDEX(Data!C:C,COUNTA(Data!C:C))))=AD$5)))


    was returning incorrect data when using the Indirect version.

    Code
    =INDEX(Data!$I:$I,'Status Board'!D5)


    Thank you!


Participate now!

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