+ Reply to Thread
Results 1 to 2 of 2

Macro Vlookup multiple columns

  1. #1
    Registered User
    Join Date
    10-06-2011
    Location
    US
    MS-Off Ver
    Excel 365
    Posts
    34

    Macro Vlookup multiple columns

    hi!

    I need a better way of doing this : I have this macro that I wrote. it does a vlookup from 2 sheets "RMUI" and "Convert". it takes the results and divides it with the number in the RMUI sheets.

    instead of doing this for each column below ( Ai, Aj.......to FJ), is it possible to combine all the columns? just a lot of repetition


    here is the sample

    Sub RMUItoGrams()
    On Error Resume Next

    Dim RM As Worksheet
    Dim conv As Worksheet

    Set RM = Sheets("RMUI")
    Set conv = Sheets("Convert")
    Set myrange = conv.Range("A:B")


    lastrow = RM.Range("D" & Rows.Count).End(xlUp).Row


    For i = 5 To lastrow
    x = Application.WorksheetFunction.VLookup(RM.Range("D" & i), myrange, 2, 0)

    ' this is where I need to find a solution to reduce this repetition

    RM.Range("Ai" & i) = RM.Range("Ai" & i) / x
    RM.Range("Aj" & i) = RM.Range("Aj" & i) / x
    RM.Range("Ak" & i) = RM.Range("Ak" & i) / x
    RM.Range("Al" & i) = RM.Range("Al" & i) / x
    RM.Range("Am" & i) = RM.Range("Am" & i) / x
    RM.Range("An" & i) = RM.Range("An" & i) / x
    RM.Range("Ao" & i) = RM.Range("Ao" & i) / x

    Next i

    End Sub

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro Vlookup multiple columns

    Maybe:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. vlookup to retun multiple column values against multiple columns
    By Kencosgrove in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-07-2016, 07:47 AM
  2. [SOLVED] Sum multiple columns using a VLOOKUP only if the columns have a value
    By pareid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-04-2016, 09:29 AM
  3. vlookup in macro for multiple columns
    By haperah in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-25-2016, 01:38 PM
  4. macro for efficient vlookup with multiple lookup values in the columns to the left
    By ammartino44 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-16-2015, 03:51 PM
  5. [SOLVED] VLookup - Single value lookup returning multiple records into multiple columns
    By kllovin in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-07-2014, 05:14 AM
  6. Return multiple columns with vlookup when there are multiple matches
    By elapo21 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-06-2013, 07:11 PM
  7. Macro to vlookup across multiple columns
    By tantcu in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-10-2013, 04:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1