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
