Error handling in vba or change to " if condition "

  • I have the below code but I need a bit of help changing it:


    Currently, it looks for a string in column A in sheet 2, if it finds it, it puts a value in column B on that same row....but it also puts some random values at the end of the list because my error handler is wrong...


    What I need is:


    IF you find it, then put a value in the adjacent cell, then exit macro and
    IF the item doesn't exist, then put the item at the end of the list


    Any help would be appreciated.


  • Re: Error handling in vba or change to " if condition "


    Hi Jarko


    Here's one possibility



    Edit: code would need modifying if more then one find of f was possible.

  • Re: Error handling in vba or change to " if condition "


    Coolies smallman, I learn some more tricks from you :)


    Code
    Sub test3()
        Dim r As Range
        On Error GoTo YoError
        Set r = Range("A:A").Find("12348")(, 2) ' <<<===
        r = "X"
        Exit Sub
    YoError:     Range("A65536").End(xlUp)(2).Resize(, 2) = Array("a", "b")
    End Sub
  • Re: Error handling in vba or change to &quot; if condition &quot;


    Gentlemen,


    thanks for your help thus far...


    i am getting an error when trying to adopt the code...i want the value of "y" variable to be the item that I click on, so if I click on cell A2, and the value is "12345", i would like that to be the search value...when i do that, i get a variable not defined error:



    what am i doing wrong?

  • Re: Error handling in vba or change to &quot; if condition &quot;


    hoycow, just tried that before i saw your post, but still get the same error...

  • Re: Error handling in vba or change to &quot; if condition &quot;


    Dood


    This code was not designed for that purpose, I could post a sample workbook to show my code runs faultlessly but i suspect you already know that. Your additonal request is a departure from your original request. How about, thanks but I have an additonal request? You make a bold assumption that we are both guys helping you.


    Smallman


    EDIT


    Always declare your variables.


    This


  • Re: Error handling in vba or change to &quot; if condition &quot;


    Smallman, I actually am very very naive to vba, so i did not know it was not designed for that purpose, or that it was an additional request...


    sorry for the confusion.

  • Re: Error handling in vba or change to &quot; if condition &quot;


    Smallman,


    I have attached the file, when you click on sheet 1 on an item userform1 pops up.


    the item that is clicked is variable "y"


    it should look for "y" on sheet 2, if found, put value of the textbox and put it in column B in sheet 2 next to the found item...


    if not found, then it would populate both item and note in sheet 2 at the next empty row...


    thankyou and sorry for the confusion...


    the code is in the save button userform module

  • Re: Error handling in vba or change to &quot; if condition &quot;


    Smallman, Holycow:


    i just figured it out based on what I need it to do...thank you again for your time and effort.


  • Re: Error handling in vba or change to &quot; if condition &quot;


    Jarko


    I can see some inefficiencies in your file so have dealt with them. There are a few things you will want to include in this process. You will want to isolate the Selection Change event to Col 1 and you will need to clear the Userform each time you run it, otherwise the last entry remains the next time you run the process. Seems to go OK for me as I tested it.


    Take care


    Smallman

  • Re: Error handling in vba or change to &quot; if condition &quot;


    great point smallman, i will make the adjustments as you mentioned. thanks again.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!