Modify Formula with Macro

[Alphacsulb]

I have some formulas in cells that I need to change.

What I need is a quick way to change the formulas.

This is my current data:

Excel Workbook
	B
2017	CONLBL=C001,CONVPOS=(),
2018	CONLBL=C002,CONVPOS=(),
2019	CONLBL=C003,CONVPOS=(),
2020	CONLBL=C004,CONVPOS=(),
2021	CONLBL=C005,CONVPOS=(),
2022	CONLBL=C006,CONVPOS=(),
2023	CONLBL=C007,CONVPOS=(),
2024	CONLBL=C008,CONVPOS=(),
2025	CONLBL=C009,CONVPOS=(),
2026	CONLBL=C010,CONVPOS=(),
2027	CONLBL=C011,CONVPOS=(),
2028	CONLBL=C012,CONVPOS=(),
2029	CONLBL=C013,CONVPOS=(),
2030	CONLBL=C014,CONVPOS=(),
2031	CONLBL=C015,CONVPOS=(),
2032	CONLBL=C016,CONVPOS=(),
2033	CONLBL=C017,CONVPOS=(),
2034	CONLBL=C018,CONVPOS=(),
2035	CONLBL=C019,CONVPOS=(),
2036	CONLBL=C020,CONVPOS=(),
2037	CONLBL=C021,CONVPOS=(),
2038	CONLBL=C022,CONVPOS=(),
2039	CONLBL=C023,CONVPOS=(),
2040	CONLBL=C024,CONVPOS=(),
2041	CONLBL=C025,CONVPOS=(),
Sheet2

As you can see the pattern is that the cell below is 3 more than the previous one.

What I have been doing manually is changing each one...which takes a long time. For instance:

B2017 now needs to start with 1562 instead of 1484...therefore B2018 should now be 1565 instead of 1487. B2019 should now be 1568 instead of B1490....ect.

Id like to be able to make this macro run 25 times since that is the number of cells.

Any leads? I have a lot of these to do.

<table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"></table>

 

[dk]

Hi

When I looked at this I thought it should be easy, but it's actually a bit fiddly (or maybe I've just made it complicated). Anyway, this macro might work for you - give it a try and see if it helps.

Sub ChangeFormulas()
Dim shtTarget As Worksheet
Dim lStartRow As Long, lEndRow As Long, lRowLoop As Long, lColumn As Long
Dim lNumEndPos As Long, lNumStartPos As Long
Dim lOffset As Long, sFormula As String, lCurrentRowRef As Long

Set shtTarget = ActiveSheet 'change as necessary - you can put a loop in if you have multiple worksheets


lColumn = 2 'assumes column B
lStartRow = 2017 'assumes that the formulas start in row 2017
lEndRow = 2041 'assumes that the formulas finish in row 2041
lOffset = (1562 - 1484) 'how many rows do we want to add (or subtract) from the original row reference

For lRowLoop = lStartRow To lEndRow

    'store the formula string
    sFormula = shtTarget.Cells(lRowLoop, lColumn).Formula
        
    'now find where the numeric part of the formula starts and finishes
    lNumEndPos = InStrRev(sFormula, "&")
    lNumStartPos = InStrRev(sFormula, "$") + 1
    
    'this will be the currently referenced row in the formula
    lCurrentRowRef = CLng(Mid$(sFormula, lNumStartPos, lNumEndPos - lNumStartPos))
        
    'Reconstruct formula adding in the offset value to the current row referenced
    sFormula = Left$(sFormula, lNumStartPos - 1) & (lCurrentRowRef + lOffset) & Right$(sFormula, Len(sFormula) - lNumEndPos + 1)

    'and finally stick the formula back into the cell
    shtTarget.Cells(lRowLoop, lColumn).Formula = sFormula
    

Next lRowLoop

End Sub

The code is currently a little hard coded. If you have multiple workbooks with different ranges then it would not be too hard to make it more dynamic. Let us know how you get on and we'll go from them.



DK

 

[Alphacsulb]

I'll try this tomorrow morning.

Thanks.

 

[Alphacsulb]

WOW, that worked nicely!

Thank you so much!