+ Reply to Thread
Results 1 to 4 of 4

VB to loop through whole columns

  1. #1
    Registered User
    Join Date
    03-19-2015
    Location
    singapore
    MS-Off Ver
    2012
    Posts
    2

    VB to loop through whole columns

    I am new to VB scripting and i am required to use VB to loop through the whole column in excel to label each row.
    Need to get some help on this.

    I have the following 2 tables:
    Untitled.jpg

    The condition is that i need to look and compare each row of the 2nd table (the X and Y value) with the X and Y values in the first table.
    I will name the X and Y in table 1 as X1 and Y1 and X2 and Y2 for table 2.
    The idea of it is to compare and assign the lower limit of the name to the rows in table 2.

    Anybody got hints on how this can be done?

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VB to loop through whole columns

    Assuming the two tables are on the same sheet...
    Assuming table 1 is in columns A:C...

    Assuming table2 is in columns F:I...

    The formula in I2, then copied down could be:

    =INDEX(A:A, MATCH(G2,B:B, 1))

    http://screencast.com/t/Xsp86bxzU
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    03-19-2015
    Location
    singapore
    MS-Off Ver
    2012
    Posts
    2

    Re: VB to loop through whole columns

    Thanks for the response.

    This can only work if X column is taken into the comparison.
    But there might be instances where the following records are observed:

    Table1:

    Name X Y
    ----------------------
    E 1 12
    F 2 19


    Table2:

    No X Y To be populated
    ------------------------------------------
    4 1 12.4 E
    5 2 19.8 F


    Hence using the index matching method will return A and B for records 4 and 5 respectively.
    How can i consider both the X and Y variables when doing the comparison?

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VB to loop through whole columns

    Maybe you should post an actual workbook demonstrating ALL the variations you want to deal with in a single formula. Reading your last message above the original formula given would still give the correct answers, so far we still don't have a need a for the second number.

+ 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. [SOLVED] Loop through Columns and Row
    By Lukael in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-16-2014, 11:56 PM
  2. Loop Across Columns...If Blank Cell, Loop Down Rows
    By flindy87 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-25-2014, 07:05 PM
  3. Loop Across Columns If Blank Cell Loop Down Rows
    By flindy87 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-05-2014, 10:22 AM
  4. loop through columns in range and only copy columns that have value of 0
    By gherzberg in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-07-2012, 12:55 PM
  5. Loop through Columns
    By Sally in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-23-2006, 02:24 AM

Tags for this Thread

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