Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Ref question
Good Afternoon,
I have a macro that is writing from one workbook to another. After it has finished opening and writing it's initial data, it calls this macro and writes a special cell value to the front page. Though this works fine, I want it to be a cell reference (linked value) vs. static. Ive tried a couple ways unsuccessfully and was wondering if you might have some ideas on how to accomplish this. Thank you - Roger Sub WritetoMainPage() Dim irow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") irow = ws.Cells(1, 12) _ .End(xlDown).Offset(1, 0).Row ws.Cells(irow, 12) = ActiveSheet.Range("h5").Value <<<This works but I want a cell ref (link) vs. static value Worksheets("sheet1").Select End sub Was this post helpful to you? Why should I |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Ref question
Roger,
Maybe something like this would work (haven't tested it): Sub WritetoMainPage() Dim irow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") irow = ws.Cells(1, 12) _ .End(xlDown).Offset(1, 0).Row 'ws.Cells(irow, 12) = ActiveSheet.Range("h5").Value we.Cells(irow, 12).Formula = "='" & ActiveSheet.Name & "'!H5" Worksheets("sheet1").Select End Sub HTH, Conan "Roger" wrote in message ... Good Afternoon, I have a macro that is writing from one workbook to another. After it has finished opening and writing it's initial data, it calls this macro and writes a special cell value to the front page. Though this works fine, I want it to be a cell reference (linked value) vs. static. Ive tried a couple ways unsuccessfully and was wondering if you might have some ideas on how to accomplish this. Thank you - Roger Sub WritetoMainPage() Dim irow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") irow = ws.Cells(1, 12) _ .End(xlDown).Offset(1, 0).Row ws.Cells(irow, 12) = ActiveSheet.Range("h5").Value <<<This works but I want a cell ref (link) vs. static value Worksheets("sheet1").Select End sub Was this post helpful to you? Why should I |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Ref question
You could record a macro when you do
Edit|copy followed by Edit|paste special|paste Link Or you could just build the formula yourself: ws.Cells(irow, 12).Formula _ = "=" & ActiveSheet.Range("h5").Address(external:=True) Roger wrote: Good Afternoon, I have a macro that is writing from one workbook to another. After it has finished opening and writing it's initial data, it calls this macro and writes a special cell value to the front page. Though this works fine, I want it to be a cell reference (linked value) vs. static. Ive tried a couple ways unsuccessfully and was wondering if you might have some ideas on how to accomplish this. Thank you - Roger Sub WritetoMainPage() Dim irow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") irow = ws.Cells(1, 12) _ .End(xlDown).Offset(1, 0).Row ws.Cells(irow, 12) = ActiveSheet.Range("h5").Value <<<This works but I want a cell ref (link) vs. static value Worksheets("sheet1").Select End sub Was this post helpful to you? Why should I -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Ref question
It would work nicely if the sheets weren't in different workbooks.
Conan Kelly wrote: Roger, Maybe something like this would work (haven't tested it): Sub WritetoMainPage() Dim irow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") irow = ws.Cells(1, 12) _ .End(xlDown).Offset(1, 0).Row 'ws.Cells(irow, 12) = ActiveSheet.Range("h5").Value we.Cells(irow, 12).Formula = "='" & ActiveSheet.Name & "'!H5" Worksheets("sheet1").Select End Sub HTH, Conan "Roger" wrote in message ... Good Afternoon, I have a macro that is writing from one workbook to another. After it has finished opening and writing it's initial data, it calls this macro and writes a special cell value to the front page. Though this works fine, I want it to be a cell reference (linked value) vs. static. Ive tried a couple ways unsuccessfully and was wondering if you might have some ideas on how to accomplish this. Thank you - Roger Sub WritetoMainPage() Dim irow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") irow = ws.Cells(1, 12) _ .End(xlDown).Offset(1, 0).Row ws.Cells(irow, 12) = ActiveSheet.Range("h5").Value <<<This works but I want a cell ref (link) vs. static value Worksheets("sheet1").Select End sub Was this post helpful to you? Why should I -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Ref question
Hi Dave,
That works just terrific Thanks. Say, can I tap your brain one last time? How can I expand on that formula to add nothing if cell H5 is empty. Right now it links and puts the value in the front page, but I dont want to show a value in the H5 has nothing in its cell? Thanks so much for your review and thoughts - Roger "Dave Peterson" wrote: You could record a macro when you do Edit|copy followed by Edit|paste special|paste Link Or you could just build the formula yourself: ws.Cells(irow, 12).Formula _ = "=" & ActiveSheet.Range("h5").Address(external:=True) Roger wrote: Good Afternoon, I have a macro that is writing from one workbook to another. After it has finished opening and writing it's initial data, it calls this macro and writes a special cell value to the front page. Though this works fine, I want it to be a cell reference (linked value) vs. static. Ive tried a couple ways unsuccessfully and was wondering if you might have some ideas on how to accomplish this. Thank you - Roger Sub WritetoMainPage() Dim irow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") irow = ws.Cells(1, 12) _ .End(xlDown).Offset(1, 0).Row ws.Cells(irow, 12) = ActiveSheet.Range("h5").Value <<<This works but I want a cell ref (link) vs. static value Worksheets("sheet1").Select End sub Was this post helpful to you? Why should I -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Ref question
You have a couple of choices...
Either use a different formula that makes the cell look blank if that cell is empty: Option Explicit Sub WritetoMainPage() Dim irow As Long Dim myAddr As String Dim ws As Worksheet Set ws = Worksheets("Sheet1") irow = ws.Cells(1, 12) _ .End(xlDown).Offset(1, 0).Row myAddr = ActiveSheet.Range("h5").Address(external:=True) ws.Cells(irow, 12).Formula _ = "=if(" & myAddr & " = """",""""," & myAddr & ")" ws.Select End Sub Or you could avoid populating the cell with any formula: Option Explicit Sub WritetoMainPage() Dim irow As Long Dim ws As Worksheet Dim myAddr As String Set ws = Worksheets("Sheet1") irow = ws.Cells(1, 12) _ .End(xlDown).Offset(1, 0).Row With ActiveSheet.Range("h5") If IsEmpty(.Value) Then 'do nothing 'or empty that cell??? 'ws.Cells(irow, 12).ClearContents Else myAddr = .Address(external:=True) ws.Cells(irow, 12).Formula _ = "=if(" & myAddr & " = """",""""," & myAddr & ")" End If End With ws.Select End Sub Roger wrote: Hi Dave, That works just terrific Thanks. Say, can I tap your brain one last time? How can I expand on that formula to add nothing if cell H5 is empty. Right now it links and puts the value in the front page, but I dont want to show a value in the H5 has nothing in its cell? Thanks so much for your review and thoughts - Roger "Dave Peterson" wrote: You could record a macro when you do Edit|copy followed by Edit|paste special|paste Link Or you could just build the formula yourself: ws.Cells(irow, 12).Formula _ = "=" & ActiveSheet.Range("h5").Address(external:=True) Roger wrote: Good Afternoon, I have a macro that is writing from one workbook to another. After it has finished opening and writing it's initial data, it calls this macro and writes a special cell value to the front page. Though this works fine, I want it to be a cell reference (linked value) vs. static. Ive tried a couple ways unsuccessfully and was wondering if you might have some ideas on how to accomplish this. Thank you - Roger Sub WritetoMainPage() Dim irow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") irow = ws.Cells(1, 12) _ .End(xlDown).Offset(1, 0).Row ws.Cells(irow, 12) = ActiveSheet.Range("h5").Value <<<This works but I want a cell ref (link) vs. static value Worksheets("sheet1").Select End sub Was this post helpful to you? Why should I -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell Ref question
Both worked just great.
Thanks once again for all your help - Roger "Dave Peterson" wrote: You have a couple of choices... Either use a different formula that makes the cell look blank if that cell is empty: Option Explicit Sub WritetoMainPage() Dim irow As Long Dim myAddr As String Dim ws As Worksheet Set ws = Worksheets("Sheet1") irow = ws.Cells(1, 12) _ .End(xlDown).Offset(1, 0).Row myAddr = ActiveSheet.Range("h5").Address(external:=True) ws.Cells(irow, 12).Formula _ = "=if(" & myAddr & " = """",""""," & myAddr & ")" ws.Select End Sub Or you could avoid populating the cell with any formula: Option Explicit Sub WritetoMainPage() Dim irow As Long Dim ws As Worksheet Dim myAddr As String Set ws = Worksheets("Sheet1") irow = ws.Cells(1, 12) _ .End(xlDown).Offset(1, 0).Row With ActiveSheet.Range("h5") If IsEmpty(.Value) Then 'do nothing 'or empty that cell??? 'ws.Cells(irow, 12).ClearContents Else myAddr = .Address(external:=True) ws.Cells(irow, 12).Formula _ = "=if(" & myAddr & " = """",""""," & myAddr & ")" End If End With ws.Select End Sub Roger wrote: Hi Dave, That works just terrific Γ’¬ Thanks. Say, can I tap your brain one last time? How can I expand on that formula to add nothing if cell H5 is empty. Right now it links and puts the value in the front page, but I donΓ’¬’t want to show a value in the H5 has nothing in its cell? Thanks so much for your review and thoughts - Roger "Dave Peterson" wrote: You could record a macro when you do Edit|copy followed by Edit|paste special|paste Link Or you could just build the formula yourself: ws.Cells(irow, 12).Formula _ = "=" & ActiveSheet.Range("h5").Address(external:=True) Roger wrote: Good Afternoon, I have a macro that is writing from one workbook to another. After it has finished opening and writing it's initial data, it calls this macro and writes a special cell value to the front page. Though this works fine, I want it to be a cell reference (linked value) vs. static. Ive tried a couple ways unsuccessfully and was wondering if you might have some ideas on how to accomplish this. Thank you - Roger Sub WritetoMainPage() Dim irow As Long Dim ws As Worksheet Set ws = Worksheets("Sheet1") irow = ws.Cells(1, 12) _ .End(xlDown).Offset(1, 0).Row ws.Cells(irow, 12) = ActiveSheet.Range("h5").Value <<<This works but I want a cell ref (link) vs. static value Worksheets("sheet1").Select End sub Was this post helpful to you? Why should I -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting question (if cell = 0, wrap cell in quotes) | New Users to Excel | |||
Cell question | Excel Worksheet Functions | |||
Excel cell question | Excel Discussion (Misc queries) | |||
Yet another cell color question. | Excel Discussion (Misc queries) | |||
empty cell question | Excel Worksheet Functions |