vba loop to adjust formulas

inscoder

New Member
Joined
Oct 21, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Hello,

Fairly new to VBA and working on a macro that will update 5 cells with values calculated through a formula given a dynamic range of values.

For example, the input range the formula cells would draw from on worksheet achLinkage could be AB2:AC5, or it could be AB2:AC10. I've developed this code to trap this range (there are formula blanks under, so I've had to modify it slightly to only trap actual values):

VBA Code:
Dim rngBegin, rng As Range
Dim wb As Workbook: Set wb = ThisWorkbook
Dim achWS As Worksheet

Set achWS = wb.Sheets("ACHLinkage")
Set rngBegin = achWS.Range("AB2")
lRow = achWS.Columns("AB").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row
lCol = rngBegin.SpecialCells(xlCellTypeLastCell).Column
Set rng = Range(Cells(2, 28), Cells(lRow, lCol))

As of right now, there are 5 values (all with different formulas) on a separate sheet (call it sheet B) that use the first row of inputs from the range above. Let's say these values are located in the range R31:R35, and their respective formulas are below:

=IF(ACHLinkage!AB2 = "", "", IF(ACHLinkage!AB2="N",ROUNDDOWN(References!$S$30*ACHLinkage!AC2,2),ROUNDUP(References!$S$30*ACHLinkage!AC2,2)))
=IF(ACHLinkage!AB2 = "", "", IF(ACHLinkage!AB2 = "N", ROUNDDOWN(References!$S$28*ACHLinkage!AC2,2), ROUNDUP(References!$S$28*ACHLinkage!AC2,2)))
=IF(ACHLinkage!AB2 = "", "", IF(ACHLinkage!AB2="N",ROUNDDOWN(USERFORM!$F$26*ACHLinkage!AC2,2),ROUNDUP(USERFORM!$F$26*ACHLinkage!AC2,2)))
=IF(ACHLinkage!AB2 = "", "", IF(ACHLinkage!AB2="N",ROUNDDOWN(USERFORM!$G$26*ACHLinkage!AC2,2),ROUNDUP(USERFORM!$G$26*ACHLinkage!AC2,2)))
=IF(ACHLinkage!AB2 = "", "", IF(ACHLinkage!AB2="N",ROUNDDOWN(USERFORM!$H$26*ACHLinkage!AC2,2),ROUNDUP(USERFORM!$H$26*ACHLinkage!AC2,2)))

The ultimate goal of the macro is to use a loop to first copy sheet B to a new workbook, update these formulas in the original workbook with the next row of the dynamic table (AB3, AC3), copy the updated worksheet to a new workbook, and so on until there is an empty input row. I've done research and haven't found anything of use and am curious if I maybe need to go about this in a different way. Thanks!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Forum statistics

Threads
1,176,122
Messages
5,901,507
Members
434,898
Latest member
dez091

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top