VBA to see cell value instead of formula

  • A modified VBA originally provided by Anonymous on July 2, 2004 works for what I need if the cell in column A contains a manually entered value. I am using a formula in the target cell that will result in either a blank cell or a value. I need to modify the VBA to allow for the formula but ignore a blank cell and fill down only when the formula results in a value. Here is what I currently have:



    Much appreciation for any help.

  • Hello and Welcome to the Forum :)


    To make thing a lot easier .... why don't you attach a sample file ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • My original has multiple sheets and each sheet has many columns. Sheet1 is used to input data. The cell formulas in all other sheets extract data from Sheet1 dependent upon an index number assigned to the appropriate sheet in column A of each sheet. Some of the cell formulas are very long and complex. My original is exceedingly large and slow. For the sake of file size and calculation/open/save times, I need a VBA that will only fill down the formula rows when an index number is assigned by the existing cell formula in column A of each sheet. The present VBA does not recognize the index number unless it is manually entered negating the convenience of the auto-sorting system used to assign data to specified sheets.


    I have built a highly simplified version of the original workbook as a sample that should demonstrate the problem.


    Thank you for your help.

    Files

    • Sample.xlsm

      (82.08 kB, downloaded 40 times, last: )
  • A formula recalculation does not trigger the Change event. You either need the Calculate event, or use the Change event but monitor the cells that are inputs to the formula, not the formula cell itself.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Why not converting your database i.e. your sheet Data into a Table ...


    You would have nothing to worry about ...:)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I'm sorry, I'm not following, rory. I'm a true novice at VBA.


    I need the sheet VBA to see only the value if and when it is assigned in column A by the cell formula. My current code is included in my original post. I've attached a working workbook example in my last post. As can be seen in it, the index number is assigned to a sheet. However, the formulas will not fill down. If I manually enter the the value over the formula generated value, it then fills down. I'm trying to find the solution to make it dependent entirely on the formula generated data and don't understand VBA well enough to figure out how to modify what I have.

  • Thank you for the suggestion, Carim. Sheet 1 is the only sheet in which data is manually entered. It utilizes 30 columns and currently has a large number of rows containing manually entered data which must be retained. All new data is unique and unrelated to all previously entered data and is entered in a new row. The database and the number of rows with it is constantly expanding and will reach several thousand rows. Specifying 30 entire columns in the table range is not practical. Having to change the table range with each new entry is undesirable.


    Data from each new row entered in sheet 1 is distributed to multiple sheets depending on the nature of the data. All sheets that utilize data from sheet 1 contain differing numbers of rows of data because data entered in sheet 1 may or may not be relevant to the sheet. All data from sheet 1 is used somewhere. If the sheet 1 database has 500 rows of data, sheet 2 may have 150 rows, sheet 2 may have 325 rows, sheet 3 may have 75 rows, etc. Each sheet that is collecting data currently contains 2500 rows of long complex formulas. There is a lot going on in the workbook and it's really slow.


    I would like to delete the formulas in all rows that do not contain data, excluding column A, in all data collection sheets. The formula in column A of each data collection sheet determines data relevance. If new data from sheet 1 is relevant, the last empty cell in column A will show an index number referencing the data from sheet 1. This is what I need to key on. I would like to fill down formulas in the collection sheets only if an index number appears in column A of a new row in that sheet.


    The VBA in my previous posts works but only if I manually enter the index number over the formula in column A.


    I am not very knowledgeable in VBA. I don't understand what rory is suggesting and don't know how to accomplish what I'm trying to accomplish.


    I hope this helps to explain what I need.


    Thanks again.

  • Hello,


    Based on your explanation ... it seems to me there is no need for a macro ...


    Just fill in your input is Sheet Data ... your formulas in the different sheets do act as filters ...:)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thank you again, Carim. One of my big objectives is to eliminate the very long and complex formulas in tens of thousands of cells until they are required, in order to speed up the workbook. This can be achieved by using the code I included in previous posts. But this code still requires that I go to each sheet and manually enter an index number in column A over the formula in the column A cell that has already assigned an index number to that cell.


    I don't know how to modify the code to achieve that. Alternatively, I don't know what I'm doing in the first place when it comes to VBA and need help with code that will accomplish that.


    In my mind, I don't require a module for the entire workbook since some sheets are not affected by column A index number data assignment. This is why the more simplistic example that I attached shows the code in each of the three data transfer sheets.


    My original workbook is very, very large. I'm trying to shrink it down to make it faster.

  • In order to completely eliminate all formulas, we have to focus on the core principle of your entire workbook.

    Quite obviously, X,Y,Z, Colors and Sizes are only a way to generically describe your actual workbook structure.


    My understanding is that your Data worksheet is the Main sheet, and it is holding all your inputs.

    All your other sheets are partial extracts based on a given criteria.


    So, the very first questions are about your real-life input process and your needs:


    - Do you manually add new records to your database ?

    - Do you need a live update, i.e. record by record ?

    - Do you need a bulk filtering solution which would create all your other sheets, based on the Main one ?

    - Do you need to be be able to modify an existing record and see it automatically updated in its destination sheet ?


    Thanks for all your clarifications

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello again,


    In order to base the discussion on something concrete ...

    Attached is your initial test file where, as requested, all formulas have been deleted ...


    In your main worksheet Data, you do need to fill in your Column A with X, Y, Z in order to automatically generate your three "extract" sheets ... please note Columns E and F do need to be populated ahead of your inputs in Column A ...


    At this stage, all the questions listed in message #10 remain valid, since many things do depend a lot on your operating process, especially since your sample database does not seem to be built with a Unique Identifier ...


    Hope this will help

    :)

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Once you have tested the macro ... feel free to share your comments

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Carim, you're amazing to keep going at it and I'm just not making what you're going at very clear. I greatly oversimplified the example attached in message #3. I apologize for my vagueness.


    In my original file a new row of data entered in sheet 1 includes a date. New rows of data are not always entered sequentially according to the dates entered. Therefore the date entered in row 100 may be prior to the dates in the previous 10 rows. The next column in sheet 1 ranks the dates entered in all rows. The rank creates an index number for all data in that row. The rank may change depending on dates entered for data in new rows. If any row in sheet 1 contains data that is relevant to sheet 2, the formula in column A of sheet 2 assigns the current rank/index number from sheet 1 for that row of data. What occurs is, all data in sheet 2 (and other sheets) will always be sequential by date regardless of the sequence the data was entered in sheet 1.


    I've attached a revised version of the message #3 sample file. The revised file includes both date and rank indexing. Dates in this example are non-sequential in sheet 1 to demonstrate the significance of the changing index number for sequencing data in other sheets. I've also set the formulas in sheet 1 columns D, E and F to key on the current rank/index number of the date for each row to demonstrate the dependency of all the data on the possibility of a changing index number. This should make it easier to understand why the formula in column 1 of other sheets must remain intact.


    - Data is all manually entered in sheet 1 with the exception of the rank column and columns used for data assignment such as columns D, E and F in the example.

    - The VBA code that I have can be modified to fill down formulas in sheet 1 based on the date entry so this sheet is not a problem.

    - Data from previous entries may on occasion be changed and possibly including date.

    - All data in sheets 2, 3 and 4 in the example must remain capable of changing with any change to data or change in ranking in sheet 1.


    I have added a row of data in sheet 1 of the new example. It should be assigned to sheet 2 "X". In sheet 2 you will see that column A has picked up the current index number for the data but the code did not fill down the formulas for columns B,C and D as desired.


    I hope I gave you what you need this time. Thanks for your tremendous patience.

  • Sadly you have decided not to answer the questions ...

    And since you are only providing important information very sparingly ...

    After 14 messages .... we have yet not reached the Start Line ...!!!=O


    Recap

    1. Date in Column A is the triggering input

    2. Index # in Column B is a Date Ranking formula with no Ties

    3. Columns D, E , F are the filtering criteria

    4. Columns G and H are simple data


    Questions :

    A. Are you manually adding Dates in Column A or are you copying them from another source ....???

    B. How many "X,Y,Z" or "Columns D,E,F" do you actually have ...???

    C. How many "Color, Size" or "Columns G,H" do you actually have ...???

    D. To handle everything without formulas, could a standard macro be suitable or not ...???


    Thanks for taking the time :

    1. To read the Read the Recap

    2. To validate or not to validate my understanding

    3. To answer the 4 questions

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Looks like your have decided ... to give up ...:(

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)