I have a large workbook that has about 8 spreadsheets. Each sheet has a large amount of cells that contain IF formulas. I will have a specified range and in that range i want to go cell by cell and alter each cell that is true/false. How would I go about doing this? thanks to anyone who can give me some help or point me in the right direction.
Alter Each Cell Individually True/False
-
-
-
-
Re: Alter Each Cell Individually True/False
Thanks that helped. I am still learning the syntax of VBA.
-
Re: Alter Each Cell Individually True/False
I have problem with this code. It works in that it goes cell by cell in the desginated range but it is evaluating the cell's value. -1 for true and 0 for false. Not if the IF formula is true or false. so the only way the cell would be altered is if there is a -1 or 0 for the value in the cell. I want it so that if the IF formula is true or false then it is altered, not if the cell is -1 or 0. hmm
-
-
-
Re: Alter Each Cell Individually True/False
By removing the =True this only altered the cells that have values in them other than zero. I need the cells altered that have zeros and non zeros in them. I need the cells with the IF formulas that are in the true sense to be set apart from the false ones. here is the code that i have. It is a real simple copy and pastespecial values.
-
Re: Alter Each Cell Individually True/False
I'm sorry but I don't know what you mean.
That sounds to me like you just want to change all cells.
-
Re: Alter Each Cell Individually True/False
Okay so all the cells have an IF formula in them. if they cell is false it goes go 0. if they cell is true then i could go to any number from 0 to 100. i need all of the cells that are true to be altered, in that the value of them is copied over the formula. I need all the cells that are false to be left alone. the problem with what you, norie, suggested is that the cells sometimes return a 0 even when true. so i need to differentiate between the cells that are true and the ones that are false by not using the value of the cell. Also the cells that are false which return a 0 need to stay that way. I realize this is pain but there must be some kind of way to do this.
thanks -
Re: Alter Each Cell Individually True/False
Why not return a different value if the IF statement is False?
-
Re: Alter Each Cell Individually True/False
How complicated is the IF statement inside the cell you are evaluating? Any nesting? Is it basically the same for each cell in the range? Can you post the IF statement here?
I'm thinking you could collect the .formula (the IF function contained in the cell), parse it for the logical test, then use that same test in the VBA code.
Or, perhaps have the macro temporarily add a column to the right of the column being tested, copy the formulas and replace the "value if true" & "value if false" with "TRUE" and "FALSE". Then, use that temporary column in your loop to determine if you want to paste values. And, of course, delete the temporary column.
-
-
Re: Alter Each Cell Individually True/False
I cant return a value other than zero because the numbers are used in monthly totals and if i use a "-" it will give me a #VALUE!. I cant use like "-1" because it will throw off the totals.
=IF(A38<=NOW(),retrieves a value from a add-in,0)
all of the If statements in the cells are basically the same. I suppose I could use another column as you say, but the If statement in my code that tests each cell wont differentiate from a cell that is true, that could return a 0 value, and a cell that is false which defaults to 0. If ya could tell me something to put in for the false default that wont screw up my totals that would work fine. I tried using a dash, "-", and space," ".
-
Re: Alter Each Cell Individually True/False
Could you attach a sample workbook as I'm totally bewildered now.
You have If statements that if false return 0 but can also return 0 if they are true???
-
Re: Alter Each Cell Individually True/False
if the IF statement in the cell is true it will enact a add-in which retrieves a value which can be anywhere from 0 to 100. the IF statement in the cell whne false produces a 0. The IF statement in each cell is related to the date. if the date next to the cells is before or equal to NOW() then it goes to true, if not then it goes to false. the column in pink is related to each of the cells to its left. apparently if there is anything but a number in the cell it will screw up the cells that depend on the cell. i dont know if this will help because you dont have the add-ins that are required for the formulas to properly work.
-
Re: Alter Each Cell Individually True/False
Blur2037
This is the first time you have ever mentioned an Add-In.
I'm afraid it's too late for me to download your attachment, but you could have at least have mentioned this Add-In from the start.
By the way exactly how were you expecting help from people who have no idea what this Add-In does or came from?
-
Re: Alter Each Cell Individually True/False
Dont ask me to attach something and then tell me its too late for my attachment. Also it doesnt matter what the add-in is. All you have to know is that it returns a value between 0-100 which i have mentioned numerous times.
-
-
Re: Alter Each Cell Individually True/False
Blur2037
Do you actually know what I meant by late? It meant that it was 4:30 am where I was and I wished to get some sleep.
You aren't going to get much help with that sort of attitude.
I'll have a look at your attachment when I get a chance.
-
Re: Alter Each Cell Individually True/False
i did not know what you meant by late. sorry. i didnt look at what time you posted. my bad. its cool tho i finally got it to work by changing the false value to "0.00%" and then by testing if it is text or number. thanks for your help tho.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!