Thanks a ton to one all really helped me alot in this concern.
On Mon, Jul 18, 2011 at 11:30 PM, Mahesh parab <mahes...@gmail.com> 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 <mahes...@gmail.com> 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 <prathima....@gmail.com>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
