+ Reply to Thread
Results 1 to 8 of 8

VB code to check entered number and copy per criteira

  1. #1
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,052

    VB code to check entered number and copy per criteira

    Hello:

    Please refer to attached file.
    I need VB code to do following

    Will enter number in cell A1.
    Once enter is pressed, then look for the same number from cell A10 down.
    If it finds it then goto that cell.
    If number is not found then add that number to first available cell exculding the row # defined in column I.

    Example :
    I am entering number 3443 in cell A1. In this case cursor needs to go to cell A11.
    Now i am entering 779, in this case it should enter 779 in cell A17.
    I hope this is clear.

    Please let me know if you have any questions.

    Thanks

    Riz
    Attached Files Attached Files

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    2007 Enterprise
    Posts
    4,565

    Re: VB code to check entered number and copy per criteira

    .
    From you description, shouldn't 779 be added to I6 ?


    Amendment : Disregard, I see the pattern you've set up now. What happens when a red colored cell is encountered ?
    If A46 is already full, should the next 'not found' number be entered into A48 ?
    Last edited by Logit; 06-10-2019 at 04:57 PM.

  3. #3
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,052

    Re: VB code to check entered number and copy per criteira

    Hello Logit:

    You are right, : If A46 is already full, number to be entered into A48

    If number is not found then add that number to first available cell excluding the row # defined in column I.

    Riz
    Last edited by rizmomin; 06-10-2019 at 06:34 PM.

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    3,170

    Cool Hi ! Try this ‼


    As a beginner starter, paste this code to the worksheet module :

    PHP Code: 
    Private Sub Worksheet_Change(ByVal Target As Range)
        
    Dim L&, Rg As Range
            
    If Target.Address <> "$A$1" Then Exit Sub
            
    If Target.Text "" Then Exit Sub
            L 
    Cells(Rows.Count1).End(xlUp).Row
            Set Rg 
    Range("A9:A" L).Find(Target.Value2, , xlValuesxlWhole)
        If 
    Rg Is Nothing Then
            Application
    .EnableEvents False
            Cells
    (IsNumeric(Application.Match(1Range("I2", [I1].End(xlDown)), 0)), 1).Value2 Target.Value2
            Application
    .EnableEvents True
        
    Else
            
    Rg.Select
            Set Rg 
    Nothing
        End 
    If
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon Add Reputation !

  5. #5
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,052

    Re: VB code to check entered number and copy per criteira

    Hello Marc:

    Tried but no result...
    Attached sheet with above code..

    Riz
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    3,170

    Arrow


    Your bad as it well works on my side ‼ So I guess you do not paste the code to the right place as written in my post

  7. #7
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,052

    Re: VB code to check entered number and copy per criteira

    Hello Marc:

    You are absolutely right.
    Pasted wrong, now it works.

    Thanks

    Riz

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    3,170

    Re: VB code to check entered number and copy per criteira


    You're welcome, thanks for the rep' !

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1