PDA

View Full Version : [SOLVED] VBA Formual not working



nirvehex
09-29-2014, 01:55 PM
Hi,

I have a code where I'm trying to do a unique count of location codes in a column.



LOCS = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & LOCS + 1).Formula = "=SUM(IF(FREQUENCY(MATCH(A2:A" & LOCS & ","A2:A" & LOCS & ","0) & ","MATCH(A2:A" & LOCS & ","A2:A" & LOCS & ","0))>0 & ","1))"


The formula in excel language is:



=SUM(IF(FREQUENCY(MATCH(A2:A36,A2:A36,0),MATCH(A2:A36,A2:A36,0))>0,1))


I'm trying to modify the range by using a last row formula which I'm setting as "LOCS"

I think I'm screwing up some quotations somewhere. Can anyone give me a hand?


Thanks!

mancubus
09-29-2014, 04:02 PM
maybe it can be resolved some other way but it's hard to say it when there's no info about the entire code or workbook.

for formula use this:

Range("A" & LOCS + 1).Formula = "=SUM(IF(FREQUENCY(MATCH(A2:A" & LOCS & ",A2:A" & LOCS & ",0),MATCH(A2:A" & LOCS & ",A2:A" & LOCS & ",0))>0,1))"