1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

How to select a range in Excel using a cell value

Discussion in 'Business Applications' started by Fadi, Nov 2, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. Fadi

    Fadi Thread Starter

    Joined:
    Nov 2, 2009
    Messages:
    2
    Hi,

    I am a begginer in excel programing and i don't know how to use cell value inside range command in VB
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,799
    Fadi, welcome to the Forum.
    Do you mean like this
    cells(1,1)
    which is range "A1"
    or
    Range(Cells(1, 1), Cells(3, 3)).Select
    which selects the range A1 to C3

    Did you know that if you open the VBA editor and click the Help it has it's own help which is different to the Excel Workbook help.
    So when you type in the Editor's Help Search the key word Cells() it will describe for you what it is and how it works.
    This applies to most VBA key words
     
  3. Fadi

    Fadi Thread Starter

    Joined:
    Nov 2, 2009
    Messages:
    2
    Hi OBP

    Thanks for your help but what needed wasn't the information you provided, may i should have explained more so please let me do so

    I have cell with value changes according to a certain selection from the user, the user inputs a number and according to this number this cell gives the column number in which i want to select some cells

    i don't know how to use this number to select the cells i want.

    Thanks
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,799
    cells(1,Cells(1,1)).select
    Which would use the value in Cell a1 to select the first cell of the Column that matches that value.
     
  5. wedonttakecrap

    wedonttakecrap

    Joined:
    Nov 3, 2009
    Messages:
    7
    If I understand this, the user enters a value, and you want to use that value to find a column corresponding to it elsewhere in the book?

    If the user is entering the column number, then OBP's solution will work.

    If you want to find the column in the sheet that matches the user entered value, then you probably don't need to hassle with VBA at all, and you can get by with the MATCH() function.

    Syntax
    MATCH(lookup_value,lookup_array,match_type)
    it can be used to find an exact match (0), next lowest value(-1), or next highest value(1).

    If it is something best left to VBA, I personally despise the syntax for VBA's find function and instead I'd probably use the WorksheetFunction property to call Match from inside my code, like this example:

    answer = Application.WorksheetFunction.Match(UserValueCell, LookupRange,0)

    I hope that helps.
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/873733

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice