The most pointless combination of functions

[Cbrine]

How about this one, following in Norie's footsteps....simple with absolutly no effect
=""

 

[Oorang]

Well uhm I feel like I might be nitpicking here, but I'll point out that it changes the value from "Empty" to a zero length string... Which is occasionally desirable. I have actually intentionally used ="" with copy/pastespecialvalues to ensure that when I saved the file as a delimited file, those cells would not be imported as null. (Empty cells on the right of a used range do not get delimiters the data just ends. And many programs that import them then go "Oh it's a null field")

 

[jproffer]

I'd tried the formula FIND(carkeys,TRUNC(A911)) but all I got was the error message
#GOHOME?

I realise what I've done wrong though, I haven't got a 911.

That complex of a command is only possible in VBA. Try:

Sub Find_Keys
With ActiveHouse
   Set x =.Find(CarKeys, lookin:=xlCouchCushions)
If Not x IsNothing Then
 Range(x.Address).Copy
 Range(MyHand.Address).PasteSpecial(xlPasteAll)
y=MsgBox("Keys have been found")
End Sub

 

[Andrew Fergus]

How about

=A1*0

I have seen legitmate variations of this where the first half of the formula is somewhat more complicated than "A1". For instance, if you have a calculated value that you wish to eliminate the effect of, without losing the formula, then this is one way of doing it - particularly where there are multiple references to that value. So whilst the impact may be pointless, it can still be a useful technique.

Andrew

 

[xlHammer]

That complex of a command is only possible in VBA. Try:

Sub Find_Keys
With ActiveHouse
   Set x =.Find(CarKeys, lookin:=xlCouchCushions)
If Not x IsNothing Then
 Range(x.Address).Copy
 Range(MyHand.Address).PasteSpecial(xlPasteAll)
y=MsgBox("Keys have been found")
End Sub

This is very useful, thanks. However I have Wife 2.0 installed on my system so I can't access the ActiveHouse without first unloading the Dishwasher array. That said I have on more than one occasion found the CarKeys in explicably stored in the HandBag cache (?!!) so it might be worth inserting a Call Missus() module first.

 

[Lewiy]

Perhaps this will solve your problem:

Sub Find_CarKeys()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Dim Missus As Object, CarKeys As Object, Home As Object, <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:City w:st="on"><st1:place w:st="on">Mobile</st1:place></st1:City> As Object<o:p></o:p>
Dim Door As Object, DishWasher As Object, Dishes() As Object<o:p></o:p>
Dim Nagging As Long, c As Long, i As Long, Patience As Long<o:p></o:p>
Dim AcceptableTimeToAsk As Boolean<o:p></o:p>
Dim Response As Long<o:p></o:p>
Set Home = ActiveHouse<o:p></o:p>
Set Door = Home.FrontDoor<o:p></o:p>
Set Missus = Home.CurrentOccupant<o:p></o:p>
Set <st1:City w:st="on"><st1:place w:st="on">Mobile</st1:place></st1:City> = WorksheetFunction.Large(PocketContents.Items, 1)<o:p></o:p>
<o:p> </o:p>
Do<o:p></o:p>
    With Door<o:p></o:p>
        If .Locked = True Then<o:p></o:p>
            Do<o:p></o:p>
                Mobile.Dial ("555-1234")<o:p></o:p>
            Loop Until Mobile.Answer.Object = Missus<o:p></o:p>
            Application.Wait (TimeValue(Len(Nagging)))<o:p></o:p>
        End If<o:p></o:p>
    End With<o:p></o:p>
Loop Until Door.Locked = False<o:p></o:p>
Set DishWasher = ActiveDishWasher<o:p></o:p>
If DishWasher.Finished = True Then<o:p></o:p>
    c = DishWasher.Items.Count<o:p></o:p>
    ReDim Dishes(c)<o:p></o:p>
    For i = 1 To c<o:p></o:p>
        Dishes(i) = DishWasher.Items(i)<o:p></o:p>
    Next i<o:p></o:p>
    For i = 1 To c<o:p></o:p>
        With Dishes(i)<o:p></o:p>
            .Remove<o:p></o:p>
            .xlPutAway<o:p></o:p>
        End With<o:p></o:p>
    Next i<o:p></o:p>
End If<o:p></o:p>
With Missus<o:p></o:p>
    Do<o:p></o:p>
        AcceptableTimeToAsk = False<o:p></o:p>
        If Len(Nagging) <= Patience Then<o:p></o:p>
            AcceptableTimeToAsk = True<o:p></o:p>
        End If<o:p></o:p>
    Loop Until AcceptableTimeToAsk = True<o:p></o:p>
End With<o:p></o:p>
Response = Missus.Questions.Ask("Do you know where my car keys are?", vbYesNo)<o:p></o:p>
If reponse = vbNo Then<o:p></o:p>
    CarKeys.Lost = True<o:p></o:p>
    Mobile.Dial(vbTaxi) Destination:= Office<o:p></o:p>
    Exit Home<o:p></o:p>
    Else<o:p></o:p>
        CarKeys.Found = True<o:p></o:p>
End If<o:p></o:p>
End Sub

 

[xlHammer]

I nominate Lewiy to be the winner.

 

[Patience]

Dim Nagging As Long

Priceless! Thank you for making my day.

 

[RoryA]

Dim Missus As Object

You're a brave man to put that in writing...

 

[Lewiy]

You're a brave man to put that in writing...

It's ok, in a previous module I already did:

Dim Lewiy as Brave