When recording a macro doesn’t work… (XP/SP2)

Topic

New Reply

Good day to all:

The issue I have is this: I have a large number of cells in a model that refer to other cells in the same workbook but on different worksheets. So, for example, in cell C40 on Sheet 1, the formula might be “=Sheet2!$E$350”. In cell C41, it might be “=Sheet3!$E$350”, and in C42 it might be “=Sheet4!$E$350”, and so on. So there is a pattern of referring to the same cell in different sheets. This pattern occurs again and again, so that in the D column, for example, each cell might refer to “=Sheet2!$F$350” and “=Sheet3!$F$350”, and so on.

What I need to do is to modify each of these formulas to add cell $E$353 from whatever sheet is referenced in the original formula. So, for example, in cell C40 on Sheet 1, the new formula should read “=Sheet2!$E$350+Sheet2!$E$353”, and in cell 41 it should read “=Sheet3!$E$350+Sheet3!$E$353”, and so on.

So what I did was to record a macro. I began in the first cell, started the macro recorder, and pressed F2 to activate the formula bar. Then I selected everything but the equal sign and copied it. Then I moved to the end of the formula, typed a plus sign and pasted in the original formula, then modified it to read the correct cell address (since they are all the same). However, instead of actually recording my keystrokes, the macro recorder simply created a one-line macro that copied the formula. So, obviously, recording a macro isn’t going to work for this problem. I need a new approach.

I would be grateful for any assistance on this.

Reply | Quote

Replies

The VBA subroutine below should do what you want to all of the cells in the current selection.

Public Sub FixFormula()
Dim oCell As Range
Dim strWk As String
    For Each oCell In Selection
        If oCell.Formula  "" Then
            oCell.Formula = oCell.Formula & "+" & _
                  Mid(oCell.Formula, 2, InStr(oCell.Formula, "!") - 1) & "$E$353"
        End If
    Next oCell
End Sub

Reply | Quote

Legare,

Thank you very much. I see how you approached this problem and I am impressed. Your solution works perfectly for me.

Regards…

Reply | Quote

Legare,
I’m an old newbee in VBA and I loved your solution as I discovered VBA equivalence of excel text functions.
I have two questions:
1) Is there a place where to find something as a table of equivalence saying for example in this case Mid = Mid ; Find = InStr ; etc…?
2) In your code I did not understood the reason to write the second line {Dim strWk As String}
Thanks

Reply | Quote

I do not know of any table of equivalence between worksheet functions and VBA functions and methods. Maybe someone else will jump in with something.

The second line in the code is not required. strWk was a variable I was using while testing the code and I forgot to delete that line after it was no longer needed.

Reply | Quote