PDA

View Full Version : VBA to perform lookup for individual lookup range



supraman
11-16-2019, 12:18 AM
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.

SamT
11-17-2019, 09:28 AM
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.

supraman
11-17-2019, 10:30 AM
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.

SamT
11-17-2019, 11:47 AM
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

supraman
11-17-2019, 12:57 PM
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

SamT
11-17-2019, 02:02 PM
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.

supraman
11-17-2019, 09:21 PM
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.

paulked
11-18-2019, 10:00 PM
@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!

supraman
11-20-2019, 06:43 AM
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.