+ Reply to Thread
Results 1 to 6 of 6

using a cells value as part of vba formula

  1. #1
    Registered User
    Join Date
    07-14-2010
    Location
    chelmsford, england
    MS-Off Ver
    Excel 2007
    Posts
    16

    using a cells value as part of vba formula

    Hi,
    I need vba to perform a match function in one cell and to then use the value produced in this cell to identify the row from which to copy a number of cells.

    Getting vba to write the match code into a cell is no problem, but how do i then use this value as the row number in the below range( ).select

    something like... range("R"& cell(A10).value"C2:R"& cell(A10).value"C11").select ...only this doesn't seem to work



    Please Login or Register  to view this content.

    thanks in advance for any help with this
    Last edited by *benj; 07-30-2010 at 04:53 AM.

  2. #2
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,225

    Re: using a cells value as part of vba formula

    Try this

    Please Login or Register  to view this content.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: using a cells value as part of vba formula

    Assuming you do need the formula in A10

    Please Login or Register  to view this content.
    You don't specify the sheet on which the A10 formula resides - in the above have assumed this is 2nd sheet - modify as nec.

    It's also not clear what the remainder of the routine is and whether or not use fo clipboard is required.

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: using a cells value as part of vba formula

    Seems over complicated. Why not attach ann example workbook with what you want to do & I'm sure we can post a better way
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  5. #5
    Registered User
    Join Date
    07-14-2010
    Location
    chelmsford, england
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: using a cells value as part of vba formula

    thanks donkeyote that worked perfectly... I'm beginning to get the hang of macros but now and then something trips me up!

    bob phillips your solution looks pretty similar tho it came up with an error (expected list separator) when I inserted it into my code so I guess some ) or " missing?

    Thanks for all the responses
    ben

  6. #6
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,225

    Re: using a cells value as part of vba formula

    Quote Originally Posted by *benj View Post
    thanks donkeyote that worked perfectly... I'm beginning to get the hang of macros but now and then something trips me up!

    bob phillips your solution looks pretty similar tho it came up with an error (expected list separator) when I inserted it into my code so I guess some ) or " missing?

    Thanks for all the responses
    ben
    Missing a " after C2

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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