Re: $$Excel-Macros$$ to find particular column and insert one column and perform Vlookup using vba
Thanks a ton to one all really helped me alot in this concern.
On Mon, Jul 18, 2011 at 11:30 PM, Mahesh parab wrote:
> Hi
>
> For Vlookup you can use static Name range which is predetermined
> RANGE
> =SBUMapping!$A$1:$D$789
> OR
> dynamic Name range range which resize dynamically
> RANGE
>
> =OFFSET(SBUMapping!$A$1,0,0,COUNTA(SBUMapping!$A:$A),COUNTA(SBUMapping!$1:$1))
>
>
> Thanks
> Mahesh
>
>
> On Mon, Jul 18, 2011 at 11:05 PM, Mahesh parab wrote:
>
>> Hi Prathima
>>
>> Assuming your Data 1,2 & 3 columns contain data
>>
>> Try :
>>
>> Sub test()
>> Dim LR As Long
>> Sheets("Summary").Select
>>
>> Set Found = Sheets("Summary").Rows(1).Find(what:="Data 1",
>> LookIn:=xlValues, lookat:=xlWhole)
>> LR = Cells(Rows.Count, Found.Column).End(xlUp).Row
>> Found.Offset(, 1).EntireColumn.Insert
>> Cells(1, Found.Column + 1).Value = "SBU"
>> Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).Formula =
>> Application.VLookup(Range(Cells(2, Found.Column), Cells(LR, Found.Column)),
>> Range("RANGE"), 2, False)
>>
>> Set Found = Sheets("Summary").Rows(1).Find(what:="Data2",
>> LookIn:=xlValues, lookat:=xlWhole)
>> LR = Cells(Rows.Count, Found.Column).End(xlUp).Row
>> Found.Offset(, 1).EntireColumn.Insert
>> Cells(1, Found.Column + 1).Value = "Region"
>> Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).Formula =
>> Application.VLookup(Range(Cells(2, Found.Column), Cells(LR, Found.Column)),
>> Range("RANGE"), 3, False)
>>
>> Set Found = Sheets("Summary").Rows(1).Find(what:="Data3",
>> LookIn:=xlValues, lookat:=xlWhole)
>> LR = Cells(Rows.Count, Found.Column).End(xlUp).Row
>> Found.Offset(, 1).EntireColumn.Insert
>> Cells(1, Found.Column + 1).Value = "Division"
>> Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).Formula =
>> Application.VLookup(Range(Cells(2, Found.Column), Cells(LR, Found.Column)),
>> Range("RANGE"), 4, False)
>> End Sub
>>
>> Thanks
>> Mahesh
>> On Sun, Jul 17, 2011 at 12:02 PM, Prathima R wrote:
>>
>>> hi experts,
>>>
>>> Please help me in this concern.
>>>
>>> i need to perform vlookup function using VBA
>>>
>>> i have attached sample work book for referance
>>>
>>> From the attached workbook i need update summary sheet using the company
>>> code as base from SBU Mapping sheet.
>>>
>>> i need to insert one column after Data1 , Data2, Data3 Columns and pull
>>> the SBU,DIVISION, REGION
>>>
>>> Please provide the vba code code to find the data1 column and then insert
>>> one column after it.then i need to pull data using vlookup.
>>>
>>> like wise i will be updating for all rest.
>>>
>>> Thanks,
>>> Prathima.
>>>
>>> --
>>>
>>> --
>>> Some important links for excel users:
>>> 1. Follow us on TWITTER for tips tricks and links :
>>> http://twitter.com/exceldailytip
>>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>>
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> <><><><><><><><><><><><><><><><><><><><><><>
>>> Like our page on facebook , Just follow below link
>>> http://www.facebook.com/discussexcel
>>>
>>
>>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ to find particular column and insert one column and perform Vlookup using vba
Hi
For Vlookup you can use static Name range which is predetermined
RANGE
=SBUMapping!$A$1:$D$789
OR
dynamic Name range range which resize dynamically
RANGE
=OFFSET(SBUMapping!$A$1,0,0,COUNTA(SBUMapping!$A:$A),COUNTA(SBUMapping!$1:$1))
Thanks
Mahesh
On Mon, Jul 18, 2011 at 11:05 PM, Mahesh parab wrote:
> Hi Prathima
>
> Assuming your Data 1,2 & 3 columns contain data
>
> Try :
>
> Sub test()
> Dim LR As Long
> Sheets("Summary").Select
>
> Set Found = Sheets("Summary").Rows(1).Find(what:="Data 1",
> LookIn:=xlValues, lookat:=xlWhole)
> LR = Cells(Rows.Count, Found.Column).End(xlUp).Row
> Found.Offset(, 1).EntireColumn.Insert
> Cells(1, Found.Column + 1).Value = "SBU"
> Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).Formula =
> Application.VLookup(Range(Cells(2, Found.Column), Cells(LR, Found.Column)),
> Range("RANGE"), 2, False)
>
> Set Found = Sheets("Summary").Rows(1).Find(what:="Data2", LookIn:=xlValues,
> lookat:=xlWhole)
> LR = Cells(Rows.Count, Found.Column).End(xlUp).Row
> Found.Offset(, 1).EntireColumn.Insert
> Cells(1, Found.Column + 1).Value = "Region"
> Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).Formula =
> Application.VLookup(Range(Cells(2, Found.Column), Cells(LR, Found.Column)),
> Range("RANGE"), 3, False)
>
> Set Found = Sheets("Summary").Rows(1).Find(what:="Data3", LookIn:=xlValues,
> lookat:=xlWhole)
> LR = Cells(Rows.Count, Found.Column).End(xlUp).Row
> Found.Offset(, 1).EntireColumn.Insert
> Cells(1, Found.Column + 1).Value = "Division"
> Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).Formula =
> Application.VLookup(Range(Cells(2, Found.Column), Cells(LR, Found.Column)),
> Range("RANGE"), 4, False)
> End Sub
>
> Thanks
> Mahesh
> On Sun, Jul 17, 2011 at 12:02 PM, Prathima R wrote:
>
>> hi experts,
>>
>> Please help me in this concern.
>>
>> i need to perform vlookup function using VBA
>>
>> i have attached sample work book for referance
>>
>> From the attached workbook i need update summary sheet using the company
>> code as base from SBU Mapping sheet.
>>
>> i need to insert one column after Data1 , Data2, Data3 Columns and pull
>> the SBU,DIVISION, REGION
>>
>> Please provide the vba code code to find the data1 column and then insert
>> one column after it.then i need to pull data using vlookup.
>>
>> like wise i will be updating for all rest.
>>
>> Thanks,
>> Prathima.
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
>
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ to find particular column and insert one column and perform Vlookup using vba
Hi Prathima
Assuming your Data 1,2 & 3 columns contain data
Try :
Sub test()
Dim LR As Long
Sheets("Summary").Select
Set Found = Sheets("Summary").Rows(1).Find(what:="Data 1", LookIn:=xlValues,
lookat:=xlWhole)
LR = Cells(Rows.Count, Found.Column).End(xlUp).Row
Found.Offset(, 1).EntireColumn.Insert
Cells(1, Found.Column + 1).Value = "SBU"
Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).Formula =
Application.VLookup(Range(Cells(2, Found.Column), Cells(LR, Found.Column)),
Range("RANGE"), 2, False)
Set Found = Sheets("Summary").Rows(1).Find(what:="Data2", LookIn:=xlValues,
lookat:=xlWhole)
LR = Cells(Rows.Count, Found.Column).End(xlUp).Row
Found.Offset(, 1).EntireColumn.Insert
Cells(1, Found.Column + 1).Value = "Region"
Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).Formula =
Application.VLookup(Range(Cells(2, Found.Column), Cells(LR, Found.Column)),
Range("RANGE"), 3, False)
Set Found = Sheets("Summary").Rows(1).Find(what:="Data3", LookIn:=xlValues,
lookat:=xlWhole)
LR = Cells(Rows.Count, Found.Column).End(xlUp).Row
Found.Offset(, 1).EntireColumn.Insert
Cells(1, Found.Column + 1).Value = "Division"
Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).Formula =
Application.VLookup(Range(Cells(2, Found.Column), Cells(LR, Found.Column)),
Range("RANGE"), 4, False)
End Sub
Thanks
Mahesh
On Sun, Jul 17, 2011 at 12:02 PM, Prathima R wrote:
> hi experts,
>
> Please help me in this concern.
>
> i need to perform vlookup function using VBA
>
> i have attached sample work book for referance
>
> From the attached workbook i need update summary sheet using the company
> code as base from SBU Mapping sheet.
>
> i need to insert one column after Data1 , Data2, Data3 Columns and pull the
> SBU,DIVISION, REGION
>
> Please provide the vba code code to find the data1 column and then insert
> one column after it.then i need to pull data using vlookup.
>
> like wise i will be updating for all rest.
>
> Thanks,
> Prathima.
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ to find particular column and insert one column and perform Vlookup using vba
You can always use the .FormulaR1C1 to do the trick. something like Cells(1,"A").FormulaR1C1 = "=Vlookup( TM On Jul 19, 12:03 am, "Rajan_Verma" wrote: > Use > > Application.Vlookup(LookupValue,LookupRange,Col,Type) in codes > > From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] > On Behalf Of Prathima R > Sent: Sunday, July 17, 2011 12:03 PM > To: excel-macros@googlegroups.com > Subject: $$Excel-Macros$$ to find particular column and insert one column > and perform Vlookup using vba > > hi experts, > > Please help me in this concern. > > i need to perform vlookup function using VBA > > i have attached sample work book for referance > > From the attached workbook i need update summary sheet using the company > code as base from SBU Mapping sheet. > > i need to insert one column after Data1 , Data2, Data3 Columns and pull the > SBU,DIVISION, REGION > > Please provide the vba code code to find the data1 column and then insert > one column after it.then i need to pull data using vlookup. > > like wise i will be updating for all rest. > > Thanks, > > Prathima. > > -- > --- - > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links > :http://twitter.com/exceldailytip > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials athttp://www.excel-macros.blogspot.com > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below > linkhttp://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ to find particular column and insert one column and perform Vlookup using vba
Use Application.Vlookup(LookupValue,LookupRange,Col,Type) in codes From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Prathima R Sent: Sunday, July 17, 2011 12:03 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ to find particular column and insert one column and perform Vlookup using vba hi experts, Please help me in this concern. i need to perform vlookup function using VBA i have attached sample work book for referance >From the attached workbook i need update summary sheet using the company code as base from SBU Mapping sheet. i need to insert one column after Data1 , Data2, Data3 Columns and pull the SBU,DIVISION, REGION Please provide the vba code code to find the data1 column and then insert one column after it.then i need to pull data using vlookup. like wise i will be updating for all rest. Thanks, Prathima. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
