Replace using a macro helper column

[pto160]

I have spreadsheets with lot of external linking. The row references change ever so often. I get the new row references using a match formula. Is there a way for example by highlighting range B2 to B5, the macro would pull the row reference values from D2 to D5 and change the row references in B2 to B5 to equal the values in D2 to D5. Column C is showing the result that I want.

<title>Excel Jeanie HTML</title>Sheet1

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 128px;"> <col style="width: 142px;"> <col style="width: 144px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </td> <td>B</td> <td>C</td> <td>D</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td> </td> <td style="font-weight: bold;">Change to</td> <td style="font-weight: bold;">Match Row</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td style="text-align: right;">='Cust List'!B1</td> <td style="text-align: right;">='Cust List'!B12</td> <td style="text-align: right;">12</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td style="text-align: right;">='Cust List'!B2</td> <td style="text-align: right;">='Cust List'!B13</td> <td style="text-align: right;">13</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td style="text-align: right;">='Cust List'!B3</td> <td style="text-align: right;">='Cust List'!B14</td> <td style="text-align: right;">14</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td style="text-align: right;">='Cust List'!B4</td> <td style="text-align: right;">='Cust List'!B15</td> <td style="text-align: right;">15</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>B2</td> <td>='Cust List'!B1</td></tr> <tr> <td>C2</td> <td>='Cust List'!B12</td></tr> <tr> <td>B3</td> <td>='Cust List'!B2</td></tr> <tr> <td>C3</td> <td>='Cust List'!B13</td></tr> <tr> <td>B4</td> <td>='Cust List'!B3</td></tr> <tr> <td>C4</td> <td>='Cust List'!B14</td></tr> <tr> <td>B5</td> <td>='Cust List'!B4</td></tr> <tr> <td>C5</td> <td>='Cust List'!B15</td></tr></tbody></table></td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Sal Paradise]

Can't you just use indirect?

Excel Workbook
	B	C	D
1		Change to	Match Row
2	=INDIRECT("'Cust List'!B"&D2)	='Cust List'!B12	12
3	=INDIRECT("'Cust List'!B"&D3)	='Cust List'!B13	13
4	=INDIRECT("'Cust List'!B"&D4)	='Cust List'!B14	14
5	=INDIRECT("'Cust List'!B"&D5)	='Cust List'!B15	15
Sheet1

You can even change the "D2" in the indirect to the match formula.

 

[pto160]

In this case no. Most of the formulas are linking to external workbooks, which are closed when I update it. I need a quick way to just change the row references and I thought a macro would be best. I usually use indirect for formulas within a workbook but not for external linking.

 

[Sal Paradise]

This worked for me with your sample data:

Sub Test()
    Dim i As Integer
    Dim j As Integer
    Dim intLength As Integer
    Dim strChange As String
    
    For i = 2 To Range("C1").End(xlDown).Row
        intLength = Len(Worksheets("Sheet1").Cells(i, 1).Value)
        For j = intLength To 1 Step -1
            If Not Mid(Cells(i, 1).Value, j, 1) Like "*[1234567890]*" Then
                MsgBox (Mid(Cells(i, 1).Value, 1, j))
                strChange = Mid(Cells(i, 1).Value, 1, j)
                Cells(i, 2).Formula = "'" & strChange & Cells(i, 3).Value
                Exit For
            End If
        Next j
    Next i
    
End Sub

 

[pto160]

Thanks. I ran the macro and made a slight change and it gave me this result.

<title>Excel Jeanie HTML</title> <table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"><tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td> </td> <td> </td> <td style="font-weight: bold;">Change to</td> <td style="font-weight: bold;">Match Row</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td>New York</td> <td>New York12</td> <td style="text-align: right;">='Cust List'!B12</td> <td style="text-align: right;">12</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td>Boston</td> <td>Boston13</td> <td style="text-align: right;">='Cust List'!B13</td> <td style="text-align: right;">13</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>Chicago</td> <td>Chicago14</td> <td style="text-align: right;">='Cust List'!B14</td> <td style="text-align: right;">14</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td>LA</td> <td>LA15</td> <td style="text-align: right;">='Cust List'!B15</td> <td style="text-align: right;">15</td></tr></tbody></table>
I changed Cells(i, 2).Formula = "'" & strChange & Cells(i, 3).Value to Cells(i, 2).Formula = "'" & strChange & Cells(i, 4).Value and it pulled the row numbers from D2 to D5. I want to leave the formula =CustList'!B in B2 to B5.
So B2 would be ='CustList"!B12, B3=CustList"!B13.

I have been trying for the longest time to get a macro like this.
Is there any way to leave the formula in the cell?
Thanks for all your help.

 

[Sal Paradise]

Please explain a little more clearly what you want. My code takes your original example, uses column B as a base, and adds the row numbers from column D and deposits the results in column C.

In other words, C = B - Row# + D

What are you looking for?

 

[pto160]

I inserted a module and ran the macro? Is this the correct process?
I want to change formula itself showing the row numbers so the first example is ='Cust List'!B1, the macro would go to D2 and pull the value 12 and go back to B2 and change the formula ='Cust List'!B12.

When I ran the macro as is I get the following result

<title>Excel Jeanie HTML</title>Sheet1

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 63px;"> <col style="width: 70px;"> <col style="width: 72px;"> <col style="width: 77px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td> </td> <td>A</td> <td>B</td> <td>C</td> <td>D</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td> </td> <td> </td> <td style="font-weight: bold;">Change to</td> <td style="font-weight: bold;">Match Row</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td>New York</td> <td>New York0</td> <td style="text-align: right;">0</td> <td style="text-align: right;">12</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td>Boston</td> <td>Boston0</td> <td style="text-align: right;">0</td> <td style="text-align: right;">13</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>Chicago</td> <td>Chicago0</td> <td style="text-align: right;">0</td> <td style="text-align: right;">14</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td>LA</td> <td>LA0</td> <td style="text-align: right;">0</td> <td style="text-align: right;">15</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>C2</td> <td>='Cust List'!B12</td></tr> <tr> <td>C3</td> <td>='Cust List'!B13</td></tr> <tr> <td>C4</td> <td>='Cust List'!B14</td></tr> <tr> <td>C5</td> <td>='Cust List'!B15</td></tr></tbody></table></td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4


It is taking the values from A2 to A5 and putting them in B2 to B5.
I would like to actually change the formula itself.
Once again, thanks for all your help.