"JLGWhiz" - thank you.
In Workbook_Open, the code is like...
Range("AOne").Select
Call SeeDiff
Range("BOne").Select
Call SeeDiff
Range("COne").Select
Call SeeDiff
...so the ActiveCell has to be, for example, Range("AOne").Select ...
directly above the ...Call SeeDiff.....can that cause an error? I can't see
any confusion to another Developer interpreting the flow of the
Procedure...or am I missing something?
Private Function SeeDiff()
Set t = ActiveCell
If (t.Value = "" Or IsNull(t.Value)) Then
t.Offset(2, 0).Value = "": Exit Function
End If
If ((t - t.Offset(0, -1).Value < 0) _
And Abs(t - t.Offset(0, -1).Value) > 9000) Then
If Len(t.Offset(0, -1)) = 4 Then
I = (10000 - t.Offset(0, -1).Value)
ElseIf Len(t.Offset(0, -1)) = 5 Then
I = (100000 - t.Offset(0, -1).Value)
ElseIf Len(t.Offset(0, -1)) = 6 Then
I = (1000000 - t.Offset(0, -1).Value)
ElseIf Len(t.Offset(0, -1)) = 7 Then
I = (10000000 - t.Offset(0, -1).Value)
End If
SeeDiff = t + 1
t.Offset(2, 0).Value = SeeDiff
Else
SeeDiff = (t - t.Offset(0, -1).Value)
t.Offset(2, 0).Value = SeeDiff
End If
End Function
"JLGWhiz" wrote:
> Bob, just to clear the air a little. In the case where you know where the
> active cell is, and you intend to use that as a reference point, you can set
> it to an object variable which will represent that specific cell until you
> re-set it or end the macro. What Peter was referring to was continuing to
> use ActiveCell, where it may be a different value or different cell as the
> macro progresses. That is not a good practice because it can not only
> confuse someone who is trying to understand what the macro is supposed to
> do, it can also confuse the creator of the macro and produce undesired
> results along with unnecessary errors. For what you were trying to do,
> setting the active cell to a variable is OK.
>
>
> "Bob Barnes" <(E-Mail Removed)> wrote in message
> news:2F91ADAF-249F-4075-8EF2-(E-Mail Removed)...
> > Peter T - Sorry you don't understand what I said. Some others here did.
> > I
> > thank you for your Input.
> >
> > I turned it over to Mgmt today, and they loved it.
> >
> > Thank you again, Bob
> >
> > "Peter T" wrote:
> >
> >> That's very good advice. If you recall, I had also told you I doubt you
> >> want
> >> the ActiveCell. I wasn't sure though as I didn't understand what you were
> >> trying to do. At the risk of repeating myself, you may get a more useful
> >> answer if you explain your objective in words, not your code which is
> >> highly
> >> ambiguous.
> >>
> >> Regards,
> >> Peter T
> >>
> >>
> >> "Bob Barnes" <(E-Mail Removed)> wrote in message
> >> news:F23AFC50-429B-45F0-BF9F-(E-Mail Removed)...
> >> > It works !! Thank you.
> >> >
> >> > Yesterday, I read this...
> >> >
> >> > I would strongly recommend that you not use ActiveCell in any
> >> > calculation, because you cannot predict where the active cell will be,
> >> > let alone what worksheet and workbook might be active when Excel
> >> > decides that it is time to calculate. If you need to get a reference
> >> > to the cell in which the function was called, use either
> >> > Application.Caller or Application.ThisCell. These will return a Range
> >> > object to points to the cell in which the function was called.
> >> >
> >> > Your thoughts on that? I don't know, but
> >> >> Dim t As Range
> >> >> Set t = ActiveCell
> >> >
> >> > ...is working. Thanks again, Bob
> >> >
> >> > "JLGWhiz" wrote:
> >> >
> >> >> Dim t As Range
> >> >> Set t = ActiveCell
> >> >>
> >> >>
> >> >> "Bob Barnes" <(E-Mail Removed)> wrote in message
> >> >> news:0EE39501-40D3-4350-9F10-(E-Mail Removed)...
> >> >> >I had 2 other threads here yesterday and found answers, but we still
> >> >> >need.
> >> >> > Need to code for the ActiveCell in the 1st line of
> >> >> > Private Sub SeeDiff() .. below.
> >> >> >
> >> >> > TIA - Bob
> >> >> >
> >> >> > Snippets from the 2 other threads from yesterday...
> >> >> > I'm going to run this code in Workbook_Open instead of
> >> >> > Worksheet_Change
> >> >> > (it
> >> >> > does work in Worksheet_Change). We've decided the Excel file will
> >> >> > be
> >> >> > essentially only a "snapshot" as all data will be maintained in the
> >> >> > Access
> >> >> > Database.
> >> >> >
> >> >> > So..Workbook_open will include code for each of the 72 Cells, IE...
> >> >> > Range("DNine").Select
> >> >> > Call SeeDiff
> >> >> > Range("ENine").Select
> >> >> > Call SeeDiff
> >> >> > ....
> >> >> >
> >> >> > Private Sub SeeDiff()
> >> >> > Set t = Application.Caller <---No "Target" here...how do I set the
> >> >> > "ActiveCell"?
> >> >> > ....I tried Application.Caller & Application.ThisCell suggested in
> >> >> > this
> >> >> > thread by Chip...
> >> >> > ...but that didn't work............................
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
|
Similar Threads
