Baffled by countif #Value error

Hi everyone,

I have the following formula:

=CA2&COUNTIF($CA$2:CA2,CA2)

CA2 has a value that's derived from a macro. for some reason I am constently getting #Value for this formula.

Whenver i used the asme formula on target cells that have formulas themselves (like the cells in which the macros derive their values) the correct result is shown. The CA cell is a long text string, where as the cell this value is derived from is only a formula . Perhaps this is the issue?

Any ideas what I'm doing wrong?

Thanks!

Hi Special-K

I'm afraid I cannot. I'm quite certain its due to the length of the text string. Are you aware of anyway to apply countif in the manner I listed above on a long text string?

hi Jonmo1,

thanks very much, your formula worked perfectly. Unfortunately I have to use that formula on about 15000 rows in my worksheet. Is there anyways I can apply the following two formulas to a macro which could be called when another VBA macro is run (which generates the data from which your formula works)?:

=LEFT(CA2,15)
=CB2&COUNTIF($CB$2:CB2,CB2)

thanks!!

something like

Please Login or Register  to view this content.