+ Reply to Thread
Results 1 to 7 of 7

scan a range of cells and select the one containing a value, then run a calculation on it

  1. #1
    Registered User
    Join Date
    11-13-2013
    Location
    United Kingdom
    MS-Off Ver
    Mac 2011
    Posts
    34

    scan a range of cells and select the one containing a value, then run a calculation on it

    hi,

    I want to build a macro that scans across a range of cells (eg. B3:D3), finds which cell =1 (there is only one piece of data in each range) , then depending on which column it is in performs a calculation on it. then drops to the next row and repeats.

    row 2 contains the time period.

    In my sheet (in row 3 for example) column A is the input data, B is calculated from the equation =1-exp(-1*A3*$B$2), C is calculated by the equation =(A2/2)*$C$2, D is calculated by the equation=(A2/2)*$D$2.

    A previous macro makes the individual values =1, then i want to add this step to automatically return them to the original.

    this is a very simplified version of a complex worksheet.

    I assume this is going to use vlookup's and nested if statements but cant seem to make it function.

    Thanks for your help.example vlookups.xlsx
    Last edited by will.girling; 03-25-2014 at 01:01 PM.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: macro to use vlookup and nested if statements

    There are no 1s in B3:D8 in the sample worksheet, but to find out which column holds 1 in a range you can use Application.Match.

    So perhaps something like this might help.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    11-13-2013
    Location
    United Kingdom
    MS-Off Ver
    Mac 2011
    Posts
    34

    Re: macro to use vlookup and nested if statements

    sorry the =1 is misleading, i run a previous macro which makes the cell go to 1 but for now forget about that.

    I just want the macro to scan the range and select the cell with any value in.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: scan a range of cells and select the one containing a value, then run a calculation on

    There will only ever be one value in the row, and that value could be anything?

  5. #5
    Registered User
    Join Date
    11-13-2013
    Location
    United Kingdom
    MS-Off Ver
    Mac 2011
    Posts
    34

    Re: scan a range of cells and select the one containing a value, then run a calculation on

    there will only ever be one value in each row in the range B:D, however this is a highly simplified version of my spreadsheet with over 200 rows of data, all with just one data piece in each row. I just need this line of code to improve my macro. i want it to simply look at the cells from B:D, and select the cell with data in, then according to which column the data is in run the appropriate calculation, then drop to the next row and repeat.

    Sorry for not being clear before. Thanks.

  6. #6
    Registered User
    Join Date
    11-13-2013
    Location
    United Kingdom
    MS-Off Ver
    Mac 2011
    Posts
    34

    Re: scan a range of cells and select the one containing a value, then run a calculation on

    SOLVED HERE http://www.excelforum.com/excel-prog...ml#post3635961

    THANKS JOHN H. DAVIS

    Please Login or Register  to view this content.

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: scan a range of cells and select the one containing a value, then run a calculation on

    Why did you start a new (duplicate?) thread?

    If you wanted further help you could have 'bumped' this thread to the top.

+ 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. Nested If and Vlookup statements
    By lompocprogramming in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-19-2014, 12:33 PM
  2. vlookup with nested if statements
    By jake0215 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-12-2013, 06:15 PM
  3. Nested IF & VLOOKUP statements
    By Bineyo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-26-2012, 11:25 AM
  4. VLOOKUP with nested IF statements?
    By crockwell in forum Excel General
    Replies: 6
    Last Post: 05-24-2010, 01:47 PM
  5. Vlookup and if statements nested against three tables
    By FMGSCG in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-22-2010, 05:13 PM

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