Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    When recording a macro doesn't work... (XP/SP2)

    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.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: When recording a macro doesn't work... (XP/SP2)

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

    <pre>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
    </pre>

    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: When recording a macro doesn't work... (XP/SP2)

    Legare,

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

    Regards...

  4. #4
    Star Lounger
    Join Date
    Aug 2001
    Location
    Lebanon/France
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: When recording a macro doesn't work... (XP/SP2)

    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

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: When recording a macro doesn't work... (XP/SP2)

    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.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •