Lookup In VBA

Posted on 2007-10-16
Last Modified: 2010-05-18

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.

Question by:simsima_7876
    LVL 3

    Expert Comment


    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:
        'or if you don't know the range but know the column and start point
        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
        Range("A3:A" & i - 1).Select
    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.


    Author Comment

    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.  
    LVL 3

    Accepted Solution

    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)


    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

    LVL 1

    Expert Comment

    Forced accept.

    EE Admin

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now