Retrieving the results of a formula to use in another formula

[Proshot]

I am trying to write code that will compare two columns in one worksheet and display the results in a single cell of another worksheet. The columns contain either a 1 or null in each cell, and I am multiplying the cells from each column in each row, then building a running sum of these products. The code works fine, until I reach a column where I've used an IF statement to determine 1 or null for each row (I'm comparing ballerina's roles with the possible combinations of rehearsals to identify personnel schedule conflicts). When I get to columns where each cell has a formula determining the value of the cell, my code crashes. Can anyone tell me how to access the value generated by the formula in the original columns for use in my comparison algorithm?

Thanks in advance.

[FDibbins]

Hi, welcome to the forum

A formula should not affect the results of your calc. What is the formula you are using? I have a feeling if says something like...=IF(test,"1","0")

If indeed you have "" around 0 and 1, remove them, they are forcing the numbers to be text

On a side note, have you considered using SUMPRODUCT() for this instead of VBA?

[Proshot]

Thanks Ford. That solved the problem. I had tried changing the "value if false", or else piece from "" to 0 before. This time it worked. I'm using VBA to develop a scheduling app that our local community ballet can use to schedule rehearsals for the annual Nutcracker performance. This piece of the app builds a matrix that identifies rehearsal combinations that include the same dancers. I want to learn VBA, so this is my first project. Therefore, I may be doing things the hard way, but I don't know the details of the Excel built in functions, and I'm learning a ton by fire. Thanks for the suggestion on the function. I'll look into it and see if it is a fit for my grand scheme.

Luke

[FDibbins]

Happy to help, that is actually a fairly common error

If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

[Proshot]

I found the star piece. However, Thread Tools doesn't seem to have an option to label the thread SOLVED. Can you help me out on this?...never mind. Just found it.