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 a
=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

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 a
=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

Hi Tom,

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


--
leonidas
------------------------------------------------------------------------
leonidas's Profile: http://www.excelforum.com/member.php...o&userid=35375
View this thread: http://www.excelforum.com/showthread...hreadid=558857