Converting Indirect to Direct

[Norbury]

I have a series of different formulas which make use of the Indirect function, this works well when my spreadsheets are in daily use for logging and interpreting test data. I have two cells which are contain the row no's for the first and last cell of a range, and my indirect formulas look at these.
Example: =AVERAGE(INDIRECT("AP"&AT24):INDIRECT("AP"&AV24))

When the test comes to an end I would like to convert all my indirect references to direct with a macro. So if cell AT24 contained a value of 50, and cell AV24 contained a value of 100, then my final version should look like
=AVERAGE(AP50:AP100)

Can anyone tell me how to go about this, please?

 

[UniMord]

Welcome aboard the board!

I'm sorry to spoil your introduction to our lovely community with the following ugly piece of code, but I'm still a novice at VBA, and it's the best I can offer you at present. I sincerely hope somebody comes along and gives you something prettier, but it'll do in the meantime. The macro will run through all selected cells and convert them.

Sub convertIndirects()
    Dim formula$, colStart$, colEnd$, rowStart$, rowEnd$, rng As Range
 
    On Error Resume Next
    For Each rng In Selection
        formula = rng.formula
        rowStart = Range(Mid(formula, InStr(formula, "&") + 1, InStr(formula, "):") - InStr(formula, "&") - 1))
        rowEnd = Range(Mid(formula, InStrRev(formula, "&") + 1, InStrRev(formula, "))") - InStrRev(formula, "&") - 1))
        colStart = Mid(formula, InStr(formula, "(""") + 2, InStr(formula, """&") - InStr(formula, "(""") - 2)
        colEnd = Mid(formula, InStrRev(formula, "(""") + 2, InStrRev(formula, """&") - InStrRev(formula, "(""") - 2)
        rng.formula = "=AVERAGE(" & colStart & rowStart & ":" & colEnd & rowEnd & ")"
    Next rng
End Sub

 

[Norbury]

Thanks for the response, but that code doesn't seem to do anything. I'm trying to pull it apart and find the error, but it's taking me a while.

 

[UniMord]

Have you selected the range to be converted?
Does your workbook allow macros?
Have you tried stepping through the code with F8 to see where it goes astray?

If it will always be the same range, you can use the range's name instead of Selection. Here's a revised version of the macro, but this time it converts a range named "Convert_Range". Also, I just added a line at the end of the loop to reset the variables at the end of each iteration, so their values shouldn't be recycled if the next round encounters an error.

Sub convertIndirects()
    Dim formula$, colStart$, colEnd$, rowStart$, rowEnd$, rng As Range
 
    On Error Resume Next
    For Each rng In [Convert_Range]
        formula = rng.formula
        rowStart = Range(Mid(formula, InStr(formula, "&") + 1, InStr(formula, "):") - InStr(formula, "&") - 1))
        rowEnd = Range(Mid(formula, InStrRev(formula, "&") + 1, InStrRev(formula, "))") - InStrRev(formula, "&") - 1))
        colStart = Mid(formula, InStr(formula, "(""") + 2, InStr(formula, """&") - InStr(formula, "(""") - 2)
        colEnd = Mid(formula, InStrRev(formula, "(""") + 2, InStrRev(formula, """&") - InStrRev(formula, "(""") - 2)
        rng.formula = "=AVERAGE(" & colStart & rowStart & ":" & colEnd & rowEnd & ")"
        formula = "": colStart = "": colEnd = "": rowStart = "": rowEnd = "" 'Reset them in case of error.
    Next rng
End Sub

 

[Norbury]

I changed tack slightly, the equation I gave as an example is the first one in this sequence, I'm finding the start of the equation set (a cell with "1st row") and rewriting the equations relative to that. This seems to work, but I'm open to ideas as to how to make it more elegant.

Sub convertIndirects()
Dim lCount As Long
ScreenUpdating = False
    Columns("AS:AS").Select
    For lCount = 1 To WorksheetFunction.CountIf(Columns(45), "1st row")
        Selection.find(What:="1st row", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
            rowno = ActiveCell.Row
            colno = ActiveCell.Column
            firstrow = Cells(rowno, colno + 1)
            lastrow = Cells(rowno, colno + 3)
            toaverage = Cells(rowno + 1, colno + 3)
            SR = Right$(Str$(firstrow), Len(Str$(firstrow)) - 1)
            SR1 = Right$(Str$(toaverage), Len(Str$(toaverage - 1)) - 1) - 1
            Cells(rowno + 2, 46).formula = "=AVERAGE(AP" & SR & ":AP" & SR + SR1 & ")"
            LR = Right$(Str$(lastrow), Len(Str$(lastrow)) - 1)
            LR1 = Right$(Str$(toaverage), Len(Str$(toaverage - 1)) - 1) - 1
            Cells(rowno + 12, 46).formula = "=SLOPE(AP" & SR & ":AP" & LR & ",AC" & SR & ":AC" & LR & ")/1000"
            Cells(rowno + 12, 48).formula = "=INTERCEPT(AP" & SR & ":AP" & LR & ",AC" & SR & ":AC" & LR & ")"
            Cells(rowno + 13, 46).formula = "=SLOPE(AP" & SR & ":AP" & LR & ",AB" & SR & ":AB" & LR & ")/3.6E6"
            Cells(rowno + 14, 46).formula = "=AVERAGE(AP" & SR & ":AP" & LR & ")"
            Cells(rowno + 24, 46).formula = "=AVERAGE(AP" & LR - LR1 & ":AP" & LR & ")"
    Next lCount
    Cells(24, 45).Select

 

[Jonmo1]

What is the reason you want to convert the Indirect to normal references?
Are you trying to improve performance?

If so, there may be a better way without using indirect in the first place.

How are the row #s generated in AT24 and AV24?
Are they formulas? What are those formulas?

 

[Norbury]

The values in cells AT24 and AV24 are the line numbers relating to the start and end of the stage of the test, during the test more lines of data are added to the stage and so the last line no. in particular may change. Those cells are user editable. The equations are locked down so that users cannot edit them, so if they weren't indirect we would need to run a macro every time we added data to change them. (I left out the unprotect/protect lines from the code I pasted).

At the end of a test we want to change to direct references so that we can remove columns that we no longer need without messing up all those equations.