+ Reply to Thread
Results 1 to 4 of 4

vba - convert formulas to values when specific criteria is met

  1. #1
    Registered User
    Join Date
    10-25-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    vba - convert formulas to values when specific criteria is met

    I have a workbook that is used for billing and it looks up overtime rates in another workbook. Essentially, column A is the person's ID number. Column B is the type of pay (regular, holiday, overtime, etc.). Column C is a formula that looks in different places for their rates based on the type of pay.

    I am looking for a code that will scroll through each row on each spreadsheet.

    If value in "B3" = "overtime", then convert formula to value in "C3"

    The concern is that without this macro, if their overtime rate changes then past data that was entered is no longer accurate.

    Thanks in advance!

  2. #2
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: vba - convert formulas to values when specific criteria is met

    How about this ?
    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  3. #3
    Registered User
    Join Date
    10-25-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: vba - convert formulas to values when specific criteria is met

    Thank you so much for the quick reply. Screen Shot 2017-12-18 at 9.14.34 PM.png

    Attached is a screenshot of the sheet. The code seems like it is converting every cell in the sheet, but I could be wrong. I also added to the top "dim cl as range" but I'm also not sure if that was correct. Maybe that's why it went through every cell? Clearly my original post simplified the columns. It isn't really A, B, and C.

  4. #4
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: vba - convert formulas to values when specific criteria is met

    Play around with the offset.

    Please Login or Register  to view this content.
    means jump to positions to right
    so as in your image Overtime is in column H so we have to check
    Please Login or Register  to view this content.

+ 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] Convert Values to formulas
    By TSter in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-19-2021, 11:41 PM
  2. Macro in excel to copy specific values from a table with a specific criteria
    By Adam Brave in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-12-2015, 05:40 AM
  3. Convert Text Values to Specific Numeric Values
    By lmp101010 in forum Excel General
    Replies: 5
    Last Post: 09-06-2014, 04:00 PM
  4. Convert formulas to values for specific rows
    By Mike361 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-24-2012, 02:25 PM
  5. Convert formulas to values
    By Aland2929 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-04-2011, 07:38 AM
  6. Convert IF formulas to their values
    By GregR in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-06-2006, 04:55 PM
  7. convert formulas to values
    By MatthewFlinchem in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-17-2006, 11:45 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