| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 :) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 :) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| PASTE LINK option not available when I select PASTE SPECIAL to link an image in Excel to a Word document. | Links and Linking in Excel | |||
| if i sort cell that has link to another page how to keep link | Excel Discussion (Misc queries) | |||
| if i sort cell that has link to another page how to keep link | Excel Discussion (Misc queries) | |||
| Link to external link | Excel Worksheet Functions | |||
| Q: Changing a link without link validation | Excel Programming | |||
Linear Mode
