Linking two cells

[Raman325]

Hi,

I have two cells in two different worksheets. One cell is referenced by a range variable called wagNDCell. The second cell is referenced by a different range variable called wagDetCell. I would like to link wagDetCell to wagNDCell, so I tried wagDetCell.Formula = wagNDCell but that didn't work because wagNDCell returns the value of the cell. How do I create this link? Thanks in advance.

Raman

[Simon Letten]

Use the Set keyword:
Set wagDetCell = wagNDCell
will mean that wagDetCell references the same cell as wagNDCell
--
Thanks

Simon


"Raman325" wrote:

>
> Hi,
>
> I have two cells in two different worksheets. One cell is referenced by
> a range variable called wagNDCell. The second cell is referenced by a
> different range variable called wagDetCell. I would like to link
> wagDetCell to wagNDCell, so I tried wagDetCell.Formula = wagNDCell but
> that didn't work because wagNDCell returns the value of the cell. How
> do I create this link? Thanks in advance.
>
> Raman
>
>
> --
> Raman325
> ------------------------------------------------------------------------
> Raman325's Profile: http://www.excelforum.com/member.php...o&userid=24748
> View this thread: http://www.excelforum.com/showthread...hreadid=396511
>
>

[Raman325]

That will change the location of wagDetCell though. I would just like the value of the cell represented by wagDetCell to be determined by the value of the cell represented by wagNDCell. I would like to have an actual link there so that if the value of wagNDCell changes, so does the value of wagDetCell. Sorry for the misunderstanding, and thanks for your help.

[Raman325]

After searching Google for a while, I finally found the following snippet of code at http://www.freevbcode.com/ShowCode.asp?ID=4303

Function ColumnLetter(ColumnNumber As Integer) As String
  If ColumnNumber > 26 Then

    ' 1st character:  Subtract 1 to map the characters to 0-25,
    '                 but you don't have to remap back to 1-26
    '                 after the 'Int' operation since columns
    '                 1-26 have no prefix letter

    ' 2nd character:  Subtract 1 to map the characters to 0-25,
    '                 but then must remap back to 1-26 after
    '                 the 'Mod' operation by adding 1 back in
    '                 (included in the '65')

    ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
                   Chr(((ColumnNumber - 1) Mod 26) + 65)
  Else
    ' Columns A-Z
    ColumnLetter = Chr(ColumnNumber + 64)
  End If
End Function

This code allows me to get the column letter based on the column number so I can use the Column and Row property of wagNDCell to determine the cell name. Thanks agian for your help.

[Simon Letten]

You could also use this:
wagDetCell.Formula="=" & wagNDCell.Address(External:=True)
--

Simon


"Raman325" wrote:

>
> After searching Google for a while, I finally found the following
> snippet of code at http://www.freevbcode.com/ShowCode.asp?ID=4303
>
>
> Code:
> --------------------
> Function ColumnLetter(ColumnNumber As Integer) As String
> If ColumnNumber > 26 Then
>
> ' 1st character: Subtract 1 to map the characters to 0-25,
> ' but you don't have to remap back to 1-26
> ' after the 'Int' operation since columns
> ' 1-26 have no prefix letter
>
> ' 2nd character: Subtract 1 to map the characters to 0-25,
> ' but then must remap back to 1-26 after
> ' the 'Mod' operation by adding 1 back in
> ' (included in the '65')
>
> ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
> Chr(((ColumnNumber - 1) Mod 26) + 65)
> Else
> ' Columns A-Z
> ColumnLetter = Chr(ColumnNumber + 64)
> End If
> End Function
> --------------------
>
>
> This code allows me to get the column letter based on the column number
> so I can use the Column and Row property of wagNDCell to determine the
> cell name. Thanks agian for your help.
>
>
> --
> Raman325
> ------------------------------------------------------------------------
> Raman325's Profile: http://www.excelforum.com/member.php...o&userid=24748
> View this thread: http://www.excelforum.com/showthread...hreadid=396511
>
>

[Raman325]

Ah, that is exactly what I was looking for. Thanks for your help, I like that one better.