+ Reply to Thread
Results 1 to 3 of 3

rename cells with a macro

  1. #1
    Registered User
    Join Date
    06-13-2006
    Posts
    76

    rename cells with a macro

    Hi,

    I have a worksheet in Excel. In column B I have merged cells in pairs of 3. It starts with 3to5 then 6to8 and so on.
    In these cells there are formulas referring to a worksheet called "Begroting Calc Won". The formulas are:
    =ROW('Begroting Calc Won'!K11) in cells 3to5
    =ROW('Begroting Calc Won'!K12) in cells 6to8
    and so on.
    I have a lot of these merged cells with formulas and tried to write a macro to change the worksheet to which it refers from "Begroting Calc Won" to "Begroting Calc Uti".
    My code is below, but it won't work. The part with "i + j" is wrong I think.
    Can someone solve this problem? Thanks in advance!

    Please Login or Register  to view this content.

  2. #2
    Tom Ogilvy
    Guest

    RE: rename cells with a macro

    did you turn on the macro recorder, then select the range and do

    Edit=>replace

    what: 'Begroting Calc Won'
    with: 'Begroting Calc Uti'

    then turn off the macro recorder and look at the recorded code. Modify it
    so it is more general and suits your needs.

    --
    Regards,
    Tom Ogilvy


    "leonidas" wrote:

    >
    > Hi,
    >
    > I have a worksheet in Excel. In column B I have merged cells in pairs
    > of 3. It starts with 3to5 then 6to8 and so on.
    > In these cells there are formulas referring to a worksheet called
    > "Begroting Calc Won". The formulas are:
    > =ROW('Begroting Calc Won'!K11) in cells 3to5
    > =ROW('Begroting Calc Won'!K12) in cells 6to8
    > and so on.
    > I have a lot of these merged cells with formulas and tried to write a
    > macro to change the worksheet to which it refers from "Begroting Calc
    > Won" to "Begroting Calc Uti".
    > My code is below, but it won't work. The part with "i + j" is wrong I
    > think.
    > Can someone solve this problem? Thanks in advance!
    >
    >
    > Code:
    > --------------------
    > Sub EigenschappenComboBoxAanpassen()
    >
    > Dim ws As Worksheet
    > Dim i As Long
    >
    > Set ws = ActiveSheet
    >
    > For i = 3 To 300 Step 3
    > For j = 8 To -192 Step -2
    > On Error Resume Next
    > Range("B" & i & ":B" & i + 2).UnMerge
    > Call LinkCombo(ws.Range("B" & i), "K" & i + j)
    > Range("B" & i & ":B" & i + 2).Merge
    > Next j
    > Next i
    >
    > End Sub
    >
    > Private Sub LinkCombo(pRange As Range, pLink As String)
    >
    > Const MyFormula As String = "=ROW('Begroting Calc Uti'!"
    > With pRange
    > .Formula = MyFormula & pLink & ")"
    > End With
    >
    > End Sub
    > --------------------
    >
    >
    > --
    > leonidas
    > ------------------------------------------------------------------------
    > leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375
    > View this thread: http://www.excelforum.com/showthread...hreadid=558857
    >
    >


  3. #3
    Registered User
    Join Date
    06-13-2006
    Posts
    76

    Thanks!

    Hi Tom,

    Thanks for your advice! The replace-function works fine!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1