• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup VBA automation [SOLVED]

I

inbp

Member
Hi all respected!


thank you for reading the post.


i hope you are all fine. :)


i am not well know about VBA code. i need your help for this.


below is the link of my file. Sheet "MES" column A2 (and so on) is lookup value.

table array is on sheet "COA". i have entered vlook up from Column i to column N.


can any one make vba that when i entered code in column A the results of vlookup will automatically appears in column i to N. that look value from COA table array.


file is attached below


https://www.dropbox.com/s/zk83ihvtvqbjs9g/Macro%20for%20Vlook%20Up.xlsx


COA sheet (table array )range of rows is 500.


while rows of MES (main sheet) is up to last row.


Regards,

Shakeel
 
dear Muhammad Shakeel


except macro why don't you use a formula, go through the link below may be it help you...

https://www.dropbox.com/s/e621b5vzywy4501/Macro%20for%20Vlook%20Up.xlsx


regards

sudipta
 
Hi Shakeel!


In addition to Sudipta..

There was nothing more difference between Formula & VBA.. Just the way you use this..


and I think, which is (in any way) possible by Formula, No need to go for VBA..


still plese check the below Code..


Right Click on SheetName.. and go to View Code.. paste the below code there..

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim deb As String
If Not Intersect(Target(1), Range("A:A")) Is Nothing Then
deb = Sheets("Chart of Accounts").Cells(1, 1).CurrentRegion.Address(external:=True)
Target.Offset(, 8).Resize(, 6).Formula = _
"=VLookup(" & Target.Address & ", " & deb & ", Column(B$2), 0)"
End If
End Sub
[/pre]

Let us know the feedback

Try to add IFERROR in the code.. for better view..:)


Regards,

Deb
 
Hi


use the below link Run the Macro using (Alt+F8) and run the macroname Vlookup


https://www.dropbox.com/s/zbyrw0tz0mf3sk0/Macro%20for%20Vlook%20Up.xls
 
Thank you all for reply :)


i conclude i should use formula than macro or VBA.


Thanx Deb Bhai,Webmax & Sudipta
 
But if someone can guide that in VBA when i just enter code in column A all actions performs automatically for vlookup..it means not to run VBA for that?
 
Hi Shakeel,


Asallam Wallekum.. My Code is doing the same.. :)


https://dl.dropboxusercontent.com/u/78831150/Excel/Macro%20for%20Vlook%20Up%20Automatically.xlsb


Regards,

Deb
 
Deb your option didn't work...when i run run macro nothing showed can you guide me what to do??
 
@ Shakeel!


You just enter code in column A all actions performs automatically for vlookup..it means not to run VBA for that


@ Webmax..

In VBA, There are some Event Base Code available.. Event base.. means, they only trigger in certain Event.. i.e If Something changes in a worksheet, or Click your Mouse, or Down Key Pressed..

These are only can be written in Excel Objects Section, not in any separate Module..

If you look at the code.. I have used a event called Worksheet_Changed will trigger if any of the cell in a mentioned Sheet, changes..


@ both :)


Please have a look, in the attached, you will realized.. how its working.. :)


Regards,

Deb
 
Deb Bro!


i want one thing more when i enter the code and my required data appears in required columns than formula should not showed their? is their any why for this.
 
Hi Bhaijaan,


Re-download the same file.. :)

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim deb As String
If Not Intersect(Target(1), Range("A:A")) Is Nothing Then
deb = Sheets("Chart of Accounts").Cells(1, 1).CurrentRegion.Address(external:=True)
Target.Offset(, 8).Resize(, 6) = _
Evaluate("=VLookup(" & Target.Address & ", " & deb & ", Column(B2:G2), 0)")
End If
End Sub
[/pre]

Regards,

Deb
 
Hi Bhaijaan,


Re-download the same file.. :)

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim deb As String
If Not Intersect(Target(1), Range("A:A")) Is Nothing Then
deb = Sheets("Chart of Accounts").Cells(1, 1).CurrentRegion.Address(external:=True)
Target.Offset(, 8).Resize(, 6) = _
Evaluate("=Iferror(VLookup(" & Target.Address & ", " & deb & ", Column(B2:G2), 0),"""")")
End If
End Sub
[/pre]

Regards,

Deb
 
Hi Bhai..


Thank you very much for this. this is really bis thing for me. Live Long Be Happy :)


Regards,

Shakeel
 
You must log in or register to reply here.
Back
Top