[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1563
  • Last Modified:

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.

 
0
simsima_7876
Asked:
simsima_7876
  • 2
1 Solution
 
stephenbarrowCommented:
Hi

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
0
 
simsima_7876Author Commented:
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.  
0
 
stephenbarrowCommented:
Ok, so in this case use similar code to that I gave you but go to the reference sheet and load the values into an array first to save having to scan the shhet multiple times.  You can then scan the array to get the values you need as you work your way down the first sheet values.

Like this:

Const max = 10
Dim lookup(2, max) As String

Sub collectvalues()
Dim i As Integer
For i = 1 To 7
    lookup(1, i) = Cells(i, 1)
    lookup(2, i) = Cells(i, 2)

Next

MsgBox getValue("2")

End Sub



Function getValue(ref As String) As String
    Dim i As Integer
    For i = 1 To max
        If lookup(1, i) = ref Then
            getValue = lookup(2, i)
            Exit Function
        End If
    Next i
    getValue = "Error " & ref & " not found"
End Function





0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now