Simple formula problem.... please help. ^^
Posted by Leo on April 11, 2001 10:43 AM
I don't know how to make a link to the value of specific cells in other worksheet with Macro.
However, the cells in other worksheet is NOT fully in a pattern. There are gaps between the vertical data:
Worksheet#2 Worksheet#4 Worksheet#6
24 12 32
40 14
50 20 15
17
20
30 11
23 3
35
21
In pure excel, that would be like "='Worksheet#2'!M8 ". However, using Macro would require <ActiveCell.Formula="'Worksheet#2'!R[-1]C[1]">. But R[-1]C[1] means it's relative to the location of ActiveCell. And it seems to be fixed. I can only change the value before running the Macro. But are these values changable to point to the 1st data after the gap while continuously searching for the gaps? Is there any method I can do it? Thank you very much.
Best regards
Leo
Posted by Dave Hawley on April 11, 2001 10:49 AM
Hi Leo
I'm really not too sure I understand, but to place in the formula shown above using VBA:
Sheets("Sheet1").Range("A1") = "='Worksheet#2'!M8"
Dave
OzGrid Business Applications
Posted by Leo on April 11, 2001 10:59 AM
sorry... read this please.
#2 #4 #6
24 12 56
40 14
50 20 71
17
20 15
30 3
23 11
52
This should be the correct graph for the numbers. #2, #4, #6 are worksheets.
Posted by Leo on April 11, 2001 11:13 AM
There are gaps between the vertical data: 30 11 23 3 35 35 21
Using Macro would require <ActiveCell.Formula="'Worksheet#2'!R[-1]C[1]">. But R[-1]C[1] means it's relative to the location of ActiveCell. And it seems to be fixed.
I have updated the above graph. In your suggestion, is "M8" changable to point to different cells while running the Macro? such as pointing to the cells after the gap in different worksheets... thanks very much.
Leo
