Page 1 of 2 12 LastLast
Results 1 to 16 of 18

Thread: Excel 2003

  1. #1
    HEXUS.social member Disturbedguy's Avatar
    Join Date
    Nov 2006
    Location
    Manchester
    Posts
    5,110
    Thanks
    841
    Thanked
    481 times in 356 posts
    • Disturbedguy's system
      • Motherboard:
      • Asus Rog Strix Z370-H Gaming
      • CPU:
      • i7 8700K
      • Memory:
      • 16GB Corsair something or other
      • Storage:
      • 1 x Samsung 960 EVO (250GB) 1 x Samsung 850 EVO (500GB)
      • Graphics card(s):
      • GTX 1080Ti
      • Operating System:
      • Windows 7 Ultimate
      • Monitor(s):
      • 32inch Samsung TV
      • Internet:
      • Crap

    Excel 2003

    Hello all,

    just looking for some quick help, im not to great with excel so was rather happy when I got a search button/ box working so that if and when a person enters a word in the search box and clicks search, it searches the current sheet for matching words. This works fine.

    However, if it doesnt find a matching word, it throws up a debug error at the moment. I would like to, if possible to make it, so that if a word isnt found, it displays a message rather than a debug error.

    A message saying something like "The word you searched for was not found within the document", basically just somewhere I can put a message.

    Can anyone help please?
    The quick the better
    Quote Originally Posted by TAKTAK View Post
    It didn't fall off, it merely became insufficient at it's purpose and got a bit droopy...

  2. #2
    HEXUS webmaster Steve's Avatar
    Join Date
    Nov 2003
    Location
    Bristol
    Posts
    14,269
    Thanks
    286
    Thanked
    829 times in 469 posts
    • Steve's system
      • CPU:
      • Intel i3-350M 2.27GHz
      • Memory:
      • 8GiB Crucial DDR3
      • Storage:
      • 320GB HDD
      • Graphics card(s):
      • Intel HD3000
      • Operating System:
      • Ubuntu 11.10

    Re: Excel 2003

    What's the code you use to do the search?
    PHP Code:
    $s = new signature();
    $s->sarcasm()->intellect()->font('Courier New')->display(); 

  3. #3
    Not a good person scaryjim's Avatar
    Join Date
    Jan 2009
    Location
    Manchester
    Posts
    15,175
    Thanks
    1,225
    Thanked
    2,282 times in 1,867 posts
    • scaryjim's system
      • Motherboard:
      • Dell Inspiron
      • CPU:
      • Core i5 8250U
      • Memory:
      • 1x 8GB DDR4 2400
      • Storage:
      • 128GB M.2 SSD + 1TB HDD
      • Graphics card(s):
      • Radeon R5 230
      • PSU:
      • Battery/Dell brick
      • Case:
      • Dell Inspiron 5570
      • Operating System:
      • Windows 10
      • Monitor(s):
      • 15" 1080p laptop panel

    Re: Excel 2003

    Without knowing the actual code you've already written, it's hard to tell what is causing your error, so it's hard to suggest a solution!

    I'm assuming you've written the search in VBA? If not, ignore all of the following!

    The easiest way would be to write an error handler and call it by adding "On Error Goto ..." at the beginning of your script. This would simply run a particular bit of code any time an error occurred, regardless of the reason for that error. Search for "On Error Goto" in the Excel VBA help and you should be able to find some samples that you can crib - erm, I mean, learn from

    Better practice, however, would be to check the error that's being returned, work out why you're getting that error, and deal with it in the code. My best guess would be that you're trying to do something with a variable that has been set to "Nothing", which will generally throw errors left, right and centre So work out what's causing it, and add a check for Nothing to your code!

  4. #4
    HEXUS.social member Disturbedguy's Avatar
    Join Date
    Nov 2006
    Location
    Manchester
    Posts
    5,110
    Thanks
    841
    Thanked
    481 times in 356 posts
    • Disturbedguy's system
      • Motherboard:
      • Asus Rog Strix Z370-H Gaming
      • CPU:
      • i7 8700K
      • Memory:
      • 16GB Corsair something or other
      • Storage:
      • 1 x Samsung 960 EVO (250GB) 1 x Samsung 850 EVO (500GB)
      • Graphics card(s):
      • GTX 1080Ti
      • Operating System:
      • Windows 7 Ultimate
      • Monitor(s):
      • 32inch Samsung TV
      • Internet:
      • Crap

    Re: Excel 2003

    Code:
    Dim fixsearch As String
    
    Private Sub Search_Click()
    
    fixsearch = SearchBox.Value
    
    Sheets("Knowledgebase").Select
    
    
    Cells.Find(fixsearch, after:=ActiveCell).Activate
    
    End Sub
    
    Private Sub SearchBox_Change()
    
    End Sub
    Would provide more information, but on a mission to beat the boss xD
    Quote Originally Posted by TAKTAK View Post
    It didn't fall off, it merely became insufficient at it's purpose and got a bit droopy...

  5. #5
    PHP Geek Flash477's Avatar
    Join Date
    Dec 2008
    Location
    Devon
    Posts
    822
    Thanks
    51
    Thanked
    72 times in 65 posts

    Re: Excel 2003

    Code:
    Dim fixsearch As String
    
    Private Sub Search_Click()
    
    fixsearch = SearchBox.Value
    
    Sheets("Knowledgebase").Select
    
    on error goto notfound:
    Cells.Find(fixsearch, after:=ActiveCell).Activate
    Exit Sub
    
    notfound:
    MsgBox("Sorry, nothing matched your search")
    
    End Sub
    
    Private Sub SearchBox_Change()
    
    End Sub
    Try that
    Last edited by Flash477; 12-03-2010 at 04:43 PM.

  6. Received thanks from:

    Disturbedguy (12-03-2010)

  7. #6
    HEXUS.social member Disturbedguy's Avatar
    Join Date
    Nov 2006
    Location
    Manchester
    Posts
    5,110
    Thanks
    841
    Thanked
    481 times in 356 posts
    • Disturbedguy's system
      • Motherboard:
      • Asus Rog Strix Z370-H Gaming
      • CPU:
      • i7 8700K
      • Memory:
      • 16GB Corsair something or other
      • Storage:
      • 1 x Samsung 960 EVO (250GB) 1 x Samsung 850 EVO (500GB)
      • Graphics card(s):
      • GTX 1080Ti
      • Operating System:
      • Windows 7 Ultimate
      • Monitor(s):
      • 32inch Samsung TV
      • Internet:
      • Crap

    Re: Excel 2003

    Cheers, that worked. This may be going a bit far and probably wont be a 2 second job, but is there a way of making it, so the message box displays the word searched for, so using your example above.


    "Sorry, your search for "boobies" returned no results. Please try again"

    ?
    Quote Originally Posted by TAKTAK View Post
    It didn't fall off, it merely became insufficient at it's purpose and got a bit droopy...

  8. #7
    Asleep
    Join Date
    Jan 2009
    Posts
    344
    Thanks
    27
    Thanked
    38 times in 34 posts
    • Repressor's system
      • Motherboard:
      • Asus M4A89GTD PRO
      • CPU:
      • AMD Phenom II X6 1090T
      • Memory:
      • 12GB Corsair
      • Storage:
      • 500GB Samsung HD502HJ
      • Graphics card(s):
      • ATI HD5850
      • PSU:
      • 620W Seasonic
      • Case:
      • Antec 300
      • Operating System:
      • Windows 7
      • Monitor(s):
      • Dell U2311H X3

    Re: Excel 2003

    Code:
    Dim fixsearch As String
    
    Private Sub Search_Click()
    
    fixsearch = SearchBox.Value
    
    Sheets("Knowledgebase").Select
    
    on error goto notfound:
    Cells.Find(fixsearch, after:=ActiveCell).Activate
    Exit Sub
    
    notfound:
    MsgBox("Sorry, your search for " & fixsearch & " returned no results. Please try again")
    
    End Sub
    
    Private Sub SearchBox_Change()
    
    End Sub
    I believe.

  9. Received thanks from:

    Disturbedguy (12-03-2010)

  10. #8
    PHP Geek Flash477's Avatar
    Join Date
    Dec 2008
    Location
    Devon
    Posts
    822
    Thanks
    51
    Thanked
    72 times in 65 posts

    Re: Excel 2003

    Quote Originally Posted by Repressor View Post
    Code:
    Dim fixsearch As String
    
    Private Sub Search_Click()
    
    fixsearch = SearchBox.Value
    
    Sheets("Knowledgebase").Select
    
    on error goto notfound:
    Cells.Find(fixsearch, after:=ActiveCell).Activate
    Exit Sub
    
    notfound:
    MsgBox("Sorry, your search for """ & fixsearch & """ returned no results. Please try again")
    
    End Sub
    
    Private Sub SearchBox_Change()
    
    End Sub
    I believe.
    Made a small change to the MsgBox line to give you the search string in quotes

  11. Received thanks from:

    Disturbedguy (12-03-2010)

  12. #9
    HEXUS.social member Disturbedguy's Avatar
    Join Date
    Nov 2006
    Location
    Manchester
    Posts
    5,110
    Thanks
    841
    Thanked
    481 times in 356 posts
    • Disturbedguy's system
      • Motherboard:
      • Asus Rog Strix Z370-H Gaming
      • CPU:
      • i7 8700K
      • Memory:
      • 16GB Corsair something or other
      • Storage:
      • 1 x Samsung 960 EVO (250GB) 1 x Samsung 850 EVO (500GB)
      • Graphics card(s):
      • GTX 1080Ti
      • Operating System:
      • Windows 7 Ultimate
      • Monitor(s):
      • 32inch Samsung TV
      • Internet:
      • Crap

    Re: Excel 2003

    Cheers to both, working fine now.

    Just got to get on with actually finishing it lol!
    Quote Originally Posted by TAKTAK View Post
    It didn't fall off, it merely became insufficient at it's purpose and got a bit droopy...

  13. #10
    HEXUS.social member Disturbedguy's Avatar
    Join Date
    Nov 2006
    Location
    Manchester
    Posts
    5,110
    Thanks
    841
    Thanked
    481 times in 356 posts
    • Disturbedguy's system
      • Motherboard:
      • Asus Rog Strix Z370-H Gaming
      • CPU:
      • i7 8700K
      • Memory:
      • 16GB Corsair something or other
      • Storage:
      • 1 x Samsung 960 EVO (250GB) 1 x Samsung 850 EVO (500GB)
      • Graphics card(s):
      • GTX 1080Ti
      • Operating System:
      • Windows 7 Ultimate
      • Monitor(s):
      • 32inch Samsung TV
      • Internet:
      • Crap

    Re: Excel 2003

    Me, back again looking for more help.

    I am trying to get a formula to read one column of data and for example look for ac42 and then look at another column for yes.

    I think this has to be done as an Array formula, but I have no clue how.

    Anyone able to help? Please?
    Quote Originally Posted by TAKTAK View Post
    It didn't fall off, it merely became insufficient at it's purpose and got a bit droopy...

  14. #11
    jim
    jim is offline
    HEXUS.clueless jim's Avatar
    Join Date
    Sep 2008
    Location
    Location: Location:
    Posts
    11,406
    Thanks
    610
    Thanked
    1,621 times in 1,292 posts
    • jim's system
      • Motherboard:
      • Asus Maximus IV Gene-Z
      • CPU:
      • i5 2500K @ 4.5GHz
      • Memory:
      • 8GB Corsair Vengeance LP
      • Storage:
      • 1TB Sandisk SSD
      • Graphics card(s):
      • ASUS GTX 970
      • PSU:
      • Corsair AX650
      • Case:
      • Silverstone Fortress FT03
      • Operating System:
      • 8.1 Pro
      • Monitor(s):
      • Dell S2716DG
      • Internet:
      • 10 Mbps ADSL

    Re: Excel 2003

    What do you want it to do when it finds the "yes"?

  15. Received thanks from:

    Disturbedguy (18-03-2010)

  16. #12
    HEXUS.social member Disturbedguy's Avatar
    Join Date
    Nov 2006
    Location
    Manchester
    Posts
    5,110
    Thanks
    841
    Thanked
    481 times in 356 posts
    • Disturbedguy's system
      • Motherboard:
      • Asus Rog Strix Z370-H Gaming
      • CPU:
      • i7 8700K
      • Memory:
      • 16GB Corsair something or other
      • Storage:
      • 1 x Samsung 960 EVO (250GB) 1 x Samsung 850 EVO (500GB)
      • Graphics card(s):
      • GTX 1080Ti
      • Operating System:
      • Windows 7 Ultimate
      • Monitor(s):
      • 32inch Samsung TV
      • Internet:
      • Crap

    Re: Excel 2003

    Add one.

    So basically it will find ac42 and then when it finds a yes it will increment the value in a cell by 1.

    If have been trying and keep getting either VALUE or NAME errors..and like I have said, im not great with Excel so im learning as I go.

    Cheers,
    Quote Originally Posted by TAKTAK View Post
    It didn't fall off, it merely became insufficient at it's purpose and got a bit droopy...

  17. #13
    jim
    jim is offline
    HEXUS.clueless jim's Avatar
    Join Date
    Sep 2008
    Location
    Location: Location:
    Posts
    11,406
    Thanks
    610
    Thanked
    1,621 times in 1,292 posts
    • jim's system
      • Motherboard:
      • Asus Maximus IV Gene-Z
      • CPU:
      • i5 2500K @ 4.5GHz
      • Memory:
      • 8GB Corsair Vengeance LP
      • Storage:
      • 1TB Sandisk SSD
      • Graphics card(s):
      • ASUS GTX 970
      • PSU:
      • Corsair AX650
      • Case:
      • Silverstone Fortress FT03
      • Operating System:
      • 8.1 Pro
      • Monitor(s):
      • Dell S2716DG
      • Internet:
      • 10 Mbps ADSL

    Re: Excel 2003

    I've used an array formula like this before:

    Code:
    =AVERAGE(IF($C$2:$C$14="B",D$2:D$14))
    Whereby if a cell in column C has a B in it, it takes the corresponding value from the D2 : D14 array and then takes the overall average of those values. I've had a quick stab at yours, but need a bit more time... I doubt I'm any better than you with Excel, but I have dabbled with arrays before. Will have a look tomorrow again if nobody comes in before me

  18. #14
    HEXUS.social member Disturbedguy's Avatar
    Join Date
    Nov 2006
    Location
    Manchester
    Posts
    5,110
    Thanks
    841
    Thanked
    481 times in 356 posts
    • Disturbedguy's system
      • Motherboard:
      • Asus Rog Strix Z370-H Gaming
      • CPU:
      • i7 8700K
      • Memory:
      • 16GB Corsair something or other
      • Storage:
      • 1 x Samsung 960 EVO (250GB) 1 x Samsung 850 EVO (500GB)
      • Graphics card(s):
      • GTX 1080Ti
      • Operating System:
      • Windows 7 Ultimate
      • Monitor(s):
      • 32inch Samsung TV
      • Internet:
      • Crap

    Re: Excel 2003

    Hahah cheers...i forgot to mention, the area im pulling/ looking at the information on is on seperate sheet but in the same work book
    Quote Originally Posted by TAKTAK View Post
    It didn't fall off, it merely became insufficient at it's purpose and got a bit droopy...

  19. #15
    jim
    jim is offline
    HEXUS.clueless jim's Avatar
    Join Date
    Sep 2008
    Location
    Location: Location:
    Posts
    11,406
    Thanks
    610
    Thanked
    1,621 times in 1,292 posts
    • jim's system
      • Motherboard:
      • Asus Maximus IV Gene-Z
      • CPU:
      • i5 2500K @ 4.5GHz
      • Memory:
      • 8GB Corsair Vengeance LP
      • Storage:
      • 1TB Sandisk SSD
      • Graphics card(s):
      • ASUS GTX 970
      • PSU:
      • Corsair AX650
      • Case:
      • Silverstone Fortress FT03
      • Operating System:
      • 8.1 Pro
      • Monitor(s):
      • Dell S2716DG
      • Internet:
      • 10 Mbps ADSL

    Re: Excel 2003

    What is the formula you're using at present - the ones you're having difficulties with? I've got a couple of rough ideas but they are far from ideal.

  20. #16
    HEXUS.social member Disturbedguy's Avatar
    Join Date
    Nov 2006
    Location
    Manchester
    Posts
    5,110
    Thanks
    841
    Thanked
    481 times in 356 posts
    • Disturbedguy's system
      • Motherboard:
      • Asus Rog Strix Z370-H Gaming
      • CPU:
      • i7 8700K
      • Memory:
      • 16GB Corsair something or other
      • Storage:
      • 1 x Samsung 960 EVO (250GB) 1 x Samsung 850 EVO (500GB)
      • Graphics card(s):
      • GTX 1080Ti
      • Operating System:
      • Windows 7 Ultimate
      • Monitor(s):
      • 32inch Samsung TV
      • Internet:
      • Crap

    Re: Excel 2003

    Hmm i gave up on the complicated formula, it was =sumif(*crap*) etc.

    Ive got it working in a dirty coding way, in one sheet i have it counting in a column per person, these columns are hidden, then on the other sheet it totals them up.

    I have it counting, so im not in too much rush for it to work properly and "cleanly" so to speak.

    but cheers
    Quote Originally Posted by TAKTAK View Post
    It didn't fall off, it merely became insufficient at it's purpose and got a bit droopy...

Page 1 of 2 12 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 27-08-2008, 09:20 AM
  2. .NET Reading Multiple Excel Files
    By Shadow_101 in forum Software
    Replies: 5
    Last Post: 23-11-2007, 03:22 PM
  3. Who can't wait for Forza 2?
    By Veles in forum Console
    Replies: 159
    Last Post: 30-05-2007, 11:07 AM
  4. Insurance Group 1 Cars?
    By joshwa in forum Automotive
    Replies: 20
    Last Post: 08-12-2006, 12:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •