Consulting

Results 1 to 9 of 9

Thread: VBA to perform lookup for individual lookup range

  1. #1
    VBAX Regular
    Joined
    Jun 2019
    Posts
    21
    Location

    VBA to perform lookup for individual lookup range

    Hi All,

    i am a beginner and want to create lookup code for my file where in one specific column lets say in Column A i am going to input my lookup value and after code it will provide the data for respective columns. if i go with static way i need to select Column A4 where my lookup value will be , and then lookup range declare from Master sheet $A$1:$O$606,and col_index no (2,3,4 etc.. depends upon the column).

    I want to change the lookup value field for VBA from a default way to Column -1, if i Provide data in A4 and place the cursor in B4, i want VBA to take lookup value from A4 but without define the Lookup value cell no. so that when i want to lookup for A5 with some other data it will run smooth.

    Basically i want to lookup every row individually without define the lookup value range, so that if i place my cursor in B4 cell it will allow me to do the lookup on basis of A4's value, and complete the lookup till G column, offset 1 row (H) and again start looking up for A4's value till column P. once lookup completed it will copy the data first for that row (B4:G4) paste special as value offset 1 row copy the data from I4:P4 paste value. and so on for rest of the cells B5 selected then lookup from A5, B6 then A6 and so on.


    Can you please help me on this.
    Attached Files Attached Files

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,006
    Location
    There are a dozen ways to code this. The easiest involve you moving the End Date column next to the Will Rejoin column.

    While it is possible to Trigger the code when you select an empty cell in column B, IMO, the best way is when you add a new Emp ID to column A.

    Let me know what you think.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    Jun 2019
    Posts
    21
    Location
    Thanks Sam,
    you are correct, please move the End date before "Will rejoin" column and do it accordingly,
    Also can you please add one more sheet on the file and create a loop for if no data matching/found then it is going to prompt me to input a alphanumeric value like "D180000000000089409282019" and will search from the other sheet which is having same kind of format as master sheet. only change will be instead of master sheet it will have ID.

    Can you please do it for me. thaks a lot.

  4. #4
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,006
    Location
    Paste this into the code page for sheet3
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("A:A")) Is Nothing Then GetEmpData Target
    End Sub
    
    Private Sub GetEmpData(ByVal Target As Range)
    Dim Cel As Range
    Dim Found As Range
    Dim Tmp1 As Range 'due to peculiarities of Intersect
    Dim Tmp2 As Range 'due to peculiarities of Intersect
    If Target.Count > 1 Then Exit Sub
    
    Application.EnableEvents = False 'To prevent Worksheet_Change from triggering
    Set Cel = Target
    
    With Sheets("Master")
        Set Found = .Range("A:A").Find(Cel)
            If Found Is Nothing Then
                Cel.Offset(0, 1) = "Emp Number Not Found; Try again"
            Else
                Set Tmp1 = Found.EntireRow
                Set Tmp2 = Found.CurrentRegion
                Intersect(Tmp1, Tmp2).Copy Cel
            End If
    End With
    
    Application.EnableEvents = True
    End Sub
    Enter a new value into the Emp ID column
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  5. #5
    VBAX Regular
    Joined
    Jun 2019
    Posts
    21
    Location

    thanks SAM for your help

    As i asked you in case data is not found i need to look up with the another sheet which is ID here, attached file is having demo of my original file.

    Please look into Master tab as few column is not going to be in the same position , also red color cell need to lookup separately from master sheet's AE:AI column.

    This is the version my original file is having where i can not do any change.

    grateful for your support
    Attached Files Attached Files

  6. #6
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,006
    Location
    That is a completely different book than the last one.

    Now we know that you are the kind of person that will change the requirements once you find someone who will give you working code for free.

    Maybe someone else will play your game.

    Good luck.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  7. #7
    VBAX Regular
    Joined
    Jun 2019
    Posts
    21
    Location
    LOL.. thanks for the backout, i knew it it was not your cup of tea.

    and the requirement you only prompt for change as you can only do it in the easiest way. lets forgot about the second file, didn't i asked in the first one that it will go and copy till a certain column and then offset one. if you can't do it its OK, but don't dare to say this thing to any one.

    One more thing your free code is no use of mine.

    anyways thanks man, nothing personal.
    Last edited by supraman; 11-17-2019 at 11:30 PM.

  8. #8
    VBAX Expert paulked's Avatar
    Joined
    Apr 2006
    Posts
    533
    Location
    @supraman

    Sam did his best to interpret what you wanted, and then gave you suggestions. After your reply asking him to do menial work for you and then code what you think you wanted, I'm surprised he replied at all!

    Then you move the goalposts!

    Sam's patience far outweighs mine (and a lot of people's in this forum) and what he said was correct. I would have said worse if it were me in Sam's shoes.

    As for you asking for the copy,
    Basically i want to lookup every row individually without define the lookup value range, so that if i place my cursor in B4 cell it will allow me to do the lookup on basis of A4's value, and complete the lookup till G column, offset 1 row (H) and again start looking up for A4's value till column P. once lookup completed it will copy the data first for that row (B4:G4) paste special as value offset 1 row copy the data from I4:P4 paste value. and so on for rest of the cells B5 selected then lookup from A5, B6 then A6 and so on.
    I had to read it 3 times before I could make any sense of it, never mind putting it into code.

    As for his 'cup of tea' I doubt you know what his tipple is in VBA. He will get stuck into anything (look at his post count) and do his best to help people, and with great success.

    I think your attitude is not conducive to being helped, I'm with Sam!
    Semper in excretia sumus; solum profundum variat.

  9. #9
    VBAX Regular
    Joined
    Jun 2019
    Posts
    21
    Location
    Dear sir, with Due respect.

    I know nothing and here i am not for a debate.

    Just wanted to quote a line from Sam "Now we know that you are the kind of person that will change the requirements once you find someone who will give you working code for free.".

    I am also a human, i have a feelings also.

    I am here to get a solution for my problem, i tried to make it simple so that i can code it from the reference what SAM or any one going to provide.

    I have no issue to apologize to sam or any one in the world, as I am wrong but i am definitely not that kind of person who want to tease to any one.

    Yes i change the file as what i want is not there, i might not able to make him understand, hence shared the file.

    Regret for my lines over there SAM.

Posting Permissions

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