Macro to adjust formula in a range

[bjnockle]

Looking for VBA macro to adjust formula range based on row break. The main formula cover the entire range (=INDEX($AB$2:$AB$151,MATCH(U2,$Y$2:$Y$151,0))) for data; I then added a row break and would like it to automatically adjust for each row break instead of manually changing the formula as the datasets is huge. See sample data. Desired outcome is in column AG and AH. Formula without row break is in AD and AE.

Thanks.

[Mumps1]

Change all your formulae in columns AD and AE to look like this: =IFERROR(INDEX($AB$2:$AB$151,MATCH(U2,$Y$2:$Y$151,0)),"")
Then select the range in column AD from row 2 to row 151. Next click on "Conditional Formatting" in the HOME tab in the top menu. Click "Use formula to determine which cells to format". Paste this formula in the "Format values ...." box: =AD2="" then click the "Format" button to select your color. Repeat the process for column AE and use this formula: =AE2=""

[bjnockle]

Mumps1: The desired result result is not to color but apply VBA to update the formula to accurately adjust the formula for each range. Your approach did not work. Not looking to color the output results. Thanks.

[Andy Pope]

try

Please Login or Register  to view this content.

[bjnockle]

Andy Pope: works for my sample file. My original file Desired outcome is in column AH and Ai. Are you able to modify the code to cover AH and AI? Also, datasets is over 200,000 rows. Will this VBA code cover over 200, 000 rows? If no, please modify as well. Thanks.

[Andy Pope]

It already locates the last row. There is a limit on the number of areas in a single block, but I can not tell whether your actual data will reach that limit. If it does you would need to alter the code to get 1 block at a time and modify the formula.

Please Login or Register  to view this content.

You just need to adjust the offset and relative reference.

[bjnockle]

Andy Pope: I am getting Run Time Error 1004. Application-defined or object defined error. Any fix? Thanks.

[Andy Pope]

Can you confirm the number of blocks of data you have.

Or post a sanitised workbook that has the same layout.

[bjnockle]

Andy Pope: Total data rows is 263,841 and column is A2:Ai263841. Desired outcome is in column AH2 and Ai263841 (similar to the line breaks in the sample file. Thanks

[Andy Pope]

That does not tell me the number of areas in the data.

And there is no need to PM me your reply

[bjnockle]

Andy Pope: That does not tell me the number of areas in the data. See attached file. That is a sample copy of my original with limited data. And there is no need to PM me your reply - noted - I apologize. Thanks.

[Andy Pope]

Please Login or Register  to view this content.

As it used specialcells to locate blocks of constants it failed as column AB in this data set has formula.

And as the file still only contains ~150 records I can not say it will fail on your actual data

[bjnockle]

Andy Pope: Code is running (due to large datasets). Did not give the error code this time around. Will update you. Thanks alot.

[bjnockle]

Andy Pope: the code works like a charm. You are simply the best.