Help with link
 

Hi,
I need some help with copying and pasting special (link) the value of one
cell into another.

This is part of the code that I'm using. I've tried almost everything with
the link and is not working

For Each c In Range(ActiveCell.Offset(0, 3).Address, lastC & i + 15)
If c.Interior.ColorIndex = 8 Then
Cells(DestRow, DestCol + 1).Value = c.Value
'I need to link the destination cell with c.value
Cells(DestRow, DestCol + 1).NumberFormat = "0.000"
Cells(DestRow, DestCol + 1).Interior.ColorIndex = 8
End If

Next

Any help would be more than appreciated.
Thanks
--
gaba :)

Help with link
 

Try something like this (untested, not sure if copy/paste sheet is same or
different)

For Each c In Range(ActiveCell.Offset(0, 3).Address, lastC & i + 15)
If c.Interior.ColorIndex = 8 Then
Range(Address(c)).Copy
Cells(DestRow, DestCol + 1).Select
ActiveSheet.Paste Link:=True
Application.CutCopyMode = False
Cells(DestRow, DestCol + 1).NumberFormat = "0.000"
Cells(DestRow, DestCol + 1).Interior.ColorIndex = 8
End If

Next

Mike F

"gaba" wrote in message
...
Hi,
I need some help with copying and pasting special (link) the value of one
cell into another.

This is part of the code that I'm using. I've tried almost everything with
the link and is not working

For Each c In Range(ActiveCell.Offset(0, 3).Address, lastC & i + 15)
If c.Interior.ColorIndex = 8 Then
Cells(DestRow, DestCol + 1).Value = c.Value
'I need to link the destination cell with c.value
Cells(DestRow, DestCol + 1).NumberFormat = "0.000"
Cells(DestRow, DestCol + 1).Interior.ColorIndex = 8
End If

Next

Any help would be more than appreciated.
Thanks
--
gaba :)

Help with link
 

Hi Mike.
Thanks for your answer. I'm getting a compile error (Sub of Function not
define) about the Address. I'll keep trying something else

Gaba

"Mike Fogleman" wrote:

Try something like this (untested, not sure if copy/paste sheet is same or
different)

For Each c In Range(ActiveCell.Offset(0, 3).Address, lastC & i + 15)
If c.Interior.ColorIndex = 8 Then
Range(Address(c)).Copy
Cells(DestRow, DestCol + 1).Select
ActiveSheet.Paste Link:=True
Application.CutCopyMode = False
Cells(DestRow, DestCol + 1).NumberFormat = "0.000"
Cells(DestRow, DestCol + 1).Interior.ColorIndex = 8
End If

Next

Mike F

"gaba" wrote in message
...
Hi,
I need some help with copying and pasting special (link) the value of one
cell into another.

This is part of the code that I'm using. I've tried almost everything with
the link and is not working

For Each c In Range(ActiveCell.Offset(0, 3).Address, lastC & i + 15)
If c.Interior.ColorIndex = 8 Then
Cells(DestRow, DestCol + 1).Value = c.Value
'I need to link the destination cell with c.value
Cells(DestRow, DestCol + 1).NumberFormat = "0.000"
Cells(DestRow, DestCol + 1).Interior.ColorIndex = 8
End If

Next

Any help would be more than appreciated.
Thanks
--
gaba :)

Help with link
 

Maybe:

Option Explicit
Sub testme01()

Dim c As Range
Dim lastC As String
Dim i As Long
Dim DestRow As Long
Dim DestCol As Long

'testing only
lastC = "E"
i = 3
DestRow = 3
DestCol = 18

For Each c In Range(ActiveCell.Offset(0, 3), Cells(i + 15, lastC))
If c.Interior.ColorIndex = 8 Then
With Cells(DestRow, DestCol + 1)
.Value = c.Value
'I need to link the destination cell with c.value
.NumberFormat = "0.000"
'or maybe???
.NumberFormat = c.NumberFormat
.Interior.ColorIndex = 8
End With
End If
Next c
End Sub



gaba wrote:

Hi,
I need some help with copying and pasting special (link) the value of one
cell into another.

This is part of the code that I'm using. I've tried almost everything with
the link and is not working

For Each c In Range(ActiveCell.Offset(0, 3).Address, lastC & i + 15)
If c.Interior.ColorIndex = 8 Then
Cells(DestRow, DestCol + 1).Value = c.Value
'I need to link the destination cell with c.value
Cells(DestRow, DestCol + 1).NumberFormat = "0.000"
Cells(DestRow, DestCol + 1).Interior.ColorIndex = 8
End If

Next

Any help would be more than appreciated.
Thanks
--
gaba :)

--

Dave Peterson

Help with link
 

Hi Dave. Glad to see you are around :)
I've tried your changes and still not linking. Is not even taking the value
of c...
Any ideas?

"Dave Peterson" wrote:

Maybe:

Option Explicit
Sub testme01()

Dim c As Range
Dim lastC As String
Dim i As Long
Dim DestRow As Long
Dim DestCol As Long

'testing only
lastC = "E"
i = 3
DestRow = 3
DestCol = 18

For Each c In Range(ActiveCell.Offset(0, 3), Cells(i + 15, lastC))
If c.Interior.ColorIndex = 8 Then
With Cells(DestRow, DestCol + 1)
.Value = c.Value
'I need to link the destination cell with c.value
.NumberFormat = "0.000"
'or maybe???
.NumberFormat = c.NumberFormat
.Interior.ColorIndex = 8
End With
End If
Next c
End Sub



gaba wrote:

Hi,
I need some help with copying and pasting special (link) the value of one
cell into another.

This is part of the code that I'm using. I've tried almost everything with
the link and is not working

For Each c In Range(ActiveCell.Offset(0, 3).Address, lastC & i + 15)
If c.Interior.ColorIndex = 8 Then
Cells(DestRow, DestCol + 1).Value = c.Value
'I need to link the destination cell with c.value
Cells(DestRow, DestCol + 1).NumberFormat = "0.000"
Cells(DestRow, DestCol + 1).Interior.ColorIndex = 8
End If

Next

Any help would be more than appreciated.
Thanks
--
gaba :)

--

Dave Peterson

Help with link
 

Do you really want a link? Just a formula that points back at that other cell?

With Cells(DestRow, DestCol + 1)
.formula = "=" & c.address(external:=true)
end with

I'm not sure how the destrow/destcol get changed, though.



gaba wrote:

Hi Dave. Glad to see you are around :)
I've tried your changes and still not linking. Is not even taking the value
of c...
Any ideas?

"Dave Peterson" wrote:

Maybe:

Option Explicit
Sub testme01()

Dim c As Range
Dim lastC As String
Dim i As Long
Dim DestRow As Long
Dim DestCol As Long

'testing only
lastC = "E"
i = 3
DestRow = 3
DestCol = 18

For Each c In Range(ActiveCell.Offset(0, 3), Cells(i + 15, lastC))
If c.Interior.ColorIndex = 8 Then
With Cells(DestRow, DestCol + 1)
.Value = c.Value
'I need to link the destination cell with c.value
.NumberFormat = "0.000"
'or maybe???
.NumberFormat = c.NumberFormat
.Interior.ColorIndex = 8
End With
End If
Next c
End Sub



gaba wrote:

Hi,
I need some help with copying and pasting special (link) the value of one
cell into another.

This is part of the code that I'm using. I've tried almost everything with
the link and is not working

For Each c In Range(ActiveCell.Offset(0, 3).Address, lastC & i + 15)
If c.Interior.ColorIndex = 8 Then
Cells(DestRow, DestCol + 1).Value = c.Value
'I need to link the destination cell with c.value
Cells(DestRow, DestCol + 1).NumberFormat = "0.000"
Cells(DestRow, DestCol + 1).Interior.ColorIndex = 8
End If

Next

Any help would be more than appreciated.
Thanks
--
gaba :)

--

Dave Peterson


--

Dave Peterson

Help with link
 

Dave, Thank you so much!
I wish one day I can write my code in such simple yet elegant way
Gaba

"Dave Peterson" wrote:

Do you really want a link? Just a formula that points back at that other cell?

With Cells(DestRow, DestCol + 1)
.formula = "=" & c.address(external:=true)
end with

I'm not sure how the destrow/destcol get changed, though.



gaba wrote:

Hi Dave. Glad to see you are around :)
I've tried your changes and still not linking. Is not even taking the value
of c...
Any ideas?

"Dave Peterson" wrote:

Maybe:

Option Explicit
Sub testme01()

Dim c As Range
Dim lastC As String
Dim i As Long
Dim DestRow As Long
Dim DestCol As Long

'testing only
lastC = "E"
i = 3
DestRow = 3
DestCol = 18

For Each c In Range(ActiveCell.Offset(0, 3), Cells(i + 15, lastC))
If c.Interior.ColorIndex = 8 Then
With Cells(DestRow, DestCol + 1)
.Value = c.Value
'I need to link the destination cell with c.value
.NumberFormat = "0.000"
'or maybe???
.NumberFormat = c.NumberFormat
.Interior.ColorIndex = 8
End With
End If
Next c
End Sub



gaba wrote:

Hi,
I need some help with copying and pasting special (link) the value of one
cell into another.

This is part of the code that I'm using. I've tried almost everything with
the link and is not working

For Each c In Range(ActiveCell.Offset(0, 3).Address, lastC & i + 15)
If c.Interior.ColorIndex = 8 Then
Cells(DestRow, DestCol + 1).Value = c.Value
'I need to link the destination cell with c.value
Cells(DestRow, DestCol + 1).NumberFormat = "0.000"
Cells(DestRow, DestCol + 1).Interior.ColorIndex = 8
End If

Next

Any help would be more than appreciated.
Thanks
--
gaba :)

--

Dave Peterson


--

Dave Peterson