Well, that helps a lot. There's over twenty subscripts in that code.
How about showing the code you're testing with a comment to tell where the error is occurring?
Printable View
There's been numerous 'discussions' about using Option Explicit and explicitly Dim-ing all variables with a specific Type when ever possible
Option Explicit tell VBA that all variable have to be Dim-ed and to me that helps eliminate silly errors
With out Option Explicit this will return nothing when you run the macro since 'asn' is NOT 'ans'
With Option Explicit that would generate an error that could be fixed before running the macroCode:ans = 2 x A(1,2)
MsgBox asn
Code:Option Explicit
Dim ans as Long
ans = 2 x A(1,2)
MsgBox asn ' <<<<<< Compile time error
MsgBox ans ' <<<<<< OK
Same error proofing applies to always Dim-ing variables with the type that the are
It's easy to just Dim everything as Variant, or just leave off the 'As String' part, but if the macro is relying on a variable being a number and you accidently assign a string to it, many lines later
In 'One' the error doesn't show up until many likes after the bad input
In 'Two' the error shows up on the line that generated the error
In 'Three' the code works the first time, but fails the second time
To me, it's easier to debug a problem with Option Explicit and accurate Dim-ing. Maybe eventually I'll get so good I don't make errors like that but intel then, I like to use them
Code:Option Explicit
Sub One()
Dim a, b, c As Variant
a = "asfsadf"
b = 123
'many. many, many lines
c = b * a ' run time Type mis-match error
MsgBox c
End Sub
Sub Two()
Dim a As Long, b As Long, c As Long
a = "asfsadf" ' run time Type mis-match error
b = 123
'many. many, many lines
c = b * a
MsgBox c
End Sub
Sub Three()
Dim a As Long, b As Long, c As Long
Range("A1").Value = 10
a = Range("A1").Value ' No run time Type mis-match error
b = 123
'many. many, many lines
c = b * a
MsgBox c
'later
Range("A1").Value = "asdfasdf"
a = Range("A1").Value ' run time Type mis-match error
b = 123
'many. many, many lines
c = b * a
MsgBox c
End Sub
That was really helpful.
Now I understand better about Option Explicit, Dim-ing, Variants
Thanks a ton Paul.
Select the entire sheet.Quote:
option to recompute entire sheet?
I thought "re-compute" meant re-process and your code processed the Selection.
I mean that I don't want to select the range and excel should automatically check last used row and fill in the results
Let us call what Paul's code does a "Process," or "Processing" so we can avoid confusion.
Applicable Range is that Range that was Processed, and/or needs Processing. I think what you meant when you said, "Entire sheet."
As I understand, the Process converts formulas to values.
One can set up Paul's code to Process: A Selection; The entire applicable Range; Or only the unprocessed, (new,) Cells in the applicable Range.
Code:Set Start = Range("A:A").Find(What:="=", LookIn:=xlFormulas)
Fabulous!!!
Marking as Solved