simsima_7876
asked on
Lookup In VBA
Hi
i want a procedure which performs the following tasks in VBA: (im using Excel 2003 and the Excel is not using R1C1 cell referencing)
- insert a new column into a worksheet
- gives the column header a title
- populates this new column with data from another worksheet stored witin the same workbook
(I can do this using a vlookup function within the worksheet but I want a VBA code way of doing this) - i would ideally like a method which does this which runs as quick as possible as i have been told that using a vlookup within code might not be as efficient as other methods.
i want a procedure which performs the following tasks in VBA: (im using Excel 2003 and the Excel is not using R1C1 cell referencing)
- insert a new column into a worksheet
- gives the column header a title
- populates this new column with data from another worksheet stored witin the same workbook
(I can do this using a vlookup function within the worksheet but I want a VBA code way of doing this) - i would ideally like a method which does this which runs as quick as possible as i have been told that using a vlookup within code might not be as efficient as other methods.
ASKER
Just to clarify the first worksheet (Worksheet 1) contains a column of Unique Reference Numbers plus about 25 other columns of other data. The 2nd worksheet contains the same Reference Numbers and a column of Country Codes. I want to be able to poulate the first worksheet with a column of country codes based on the Unique Reference Numbers. The Unique Reference Numbers are not in the same row order on the 2 worksheets nor are they sorted in ascending order. I can perform this task using the worksheet function VLookUp but i would like to be able to do this within code.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Forced accept.
Computer101
EE Admin
Computer101
EE Admin
Something like this should do what you are after:
Sub copy_values()
Dim sht As Worksheet
Set sht = ActiveSheet 'get the current sheet
Range("A1") = "Column_Heading"
'assume sheet1 has data within
'if you know the range already the use something like:
Sheets("Sheet1").Select
Range("B6:B16").Select
Selection.Copy
sht.Select
Range("A2").Select
ActiveSheet.Paste
'or if you don't know the range but know the column and start point
Sheets("Sheet1").Select
Dim i As Integer
i = 3 'or whatever row your data starts in.
Do Until Sheets("Sheet1").Cells(i, 1) = "" 'change 1 to the column number as appropriate
i = i + 1
Loop
Range("A3:A" & i - 1).Select
Selection.Copy
sht.Select
Range("A2").Select
ActiveSheet.Paste
End Sub
This has a couple of option depending on what you know about the data to copy across, just select the option and change the numbers to suit.
Hope this helps.
Stephen