Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Precedents On Other Sheet Via Macro
The following macro selects precedents only if on the current sheet.
Sub Macro1() ActiveCell.Precedents.Select End Sub If the cell contains reference to some other sheet in the same book the error 1004 stating "No Cells Found" pops up. Any suggestion? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Precedents On Other Sheet Via Macro
One way would be to bring the formula in as a string and parse it to find the
precedents. Since programming a compiler for Excel is non-trivial, I would consider searching the Internet for existing code. -- Gary''s Student - gsnu200852 "FARAZ QURESHI" wrote: The following macro selects precedents only if on the current sheet. Sub Macro1() ActiveCell.Precedents.Select End Sub If the cell contains reference to some other sheet in the same book the error 1004 stating "No Cells Found" pops up. Any suggestion? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Precedents On Other Sheet Via Macro
Good suggestion Gary!
However, can you help me in designing a UDF to return the number of precedent cells/ranges present in a formula? Thanx again. "Gary''s Student" wrote: One way would be to bring the formula in as a string and parse it to find the precedents. Since programming a compiler for Excel is non-trivial, I would consider searching the Internet for existing code. -- Gary''s Student - gsnu200852 "FARAZ QURESHI" wrote: The following macro selects precedents only if on the current sheet. Sub Macro1() ActiveCell.Precedents.Select End Sub If the cell contains reference to some other sheet in the same book the error 1004 stating "No Cells Found" pops up. Any suggestion? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selecting Precedents On Other Sheet Via Macro
We can make simple UDFs for simple formulas and expand the code to include
more cases. For example, if the formula is extremely simple: =A1+B1 =A1-B1+C2 =(D45+C45)/AA12 then all we need to do is to count the Operators: Function ArgCounter(r As Range) As Integer Dim sFormula As String Dim seps() seps = Array("+", "-", "*", "/") sFormula = r.Formula l = Len(sFormula) ArgCounter = 0 For i = 1 To l characterr = Mid(sFormula, i, 1) For j = 0 To 3 If characterr = seps(j) Then ArgCounter = ArgCounter + 1 End If Next Next End Function This DOES work for simple case. But for: =SUM(A1:B9) It does not work. So we add : to the list of Operators Then we discover that: =A1+742 returns a two. We need to decide if "742" is an argument and adjust the code accordingly. Then we discover that: =++++A1+A3 is also a perfectly valid formula and we must adjust the code that case, etc...... -- Gary''s Student - gsnu200852 "FARAZ QURESHI" wrote: Good suggestion Gary! However, can you help me in designing a UDF to return the number of precedent cells/ranges present in a formula? Thanx again. "Gary''s Student" wrote: One way would be to bring the formula in as a string and parse it to find the precedents. Since programming a compiler for Excel is non-trivial, I would consider searching the Internet for existing code. -- Gary''s Student - gsnu200852 "FARAZ QURESHI" wrote: The following macro selects precedents only if on the current sheet. Sub Macro1() ActiveCell.Precedents.Select End Sub If the cell contains reference to some other sheet in the same book the error 1004 stating "No Cells Found" pops up. Any suggestion? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting sheet with VB | Excel Discussion (Misc queries) | |||
Range Address of Precedents and Dependents from other sheet. | Excel Discussion (Misc queries) | |||
Selecting data from database sheet | Excel Discussion (Misc queries) | |||
Selecting Last Sheet | Excel Worksheet Functions | |||
Selecting a sheet from a drop down box | Excel Discussion (Misc queries) |