Countif + Left VBA code

**MJW** · 12-09-2011, 02:28 PM

Hi All,

I'm trying to figure out code that will scan all cells in a column, W, and return a count of how many of the cell values start with a particular text string, for our purposes , "XYZ". I know how to do this with a helper column but can't figure out how to do it without one and i'd prefer not to have a helper column if at all possible.

Thanks for your help!

**tigeravatar** · 12-09-2011, 02:40 PM

MJW,

Something like this?

Sub tgr()
    
    MsgBox [COUNTIF(W:W,"XYZ*")]
    
End Sub

**MJW** · 12-09-2011, 02:46 PM

Sorry, i should have been more specific. i need it to return the value to a cell. for instance AA1.

thanks!

**tigeravatar** · 12-09-2011, 02:48 PM

Sub tgr()
    
    Range("AA1").Value = [COUNTIF(W:W,"XYZ*")]
    
End Sub

**tigeravatar** · 12-09-2011, 02:49 PM

Also, its worth noting that VBA isn't required for this. You could simply put this formula in cell AA1:
=COUNTIF(W:W,"XYZ*")

**MJW** · 12-09-2011, 02:52 PM

yep, right after i posted that i figured out how to return it to a cell. its part of a much bigger macro and i'm trying to foolproof it for the end user. I've never used the [] before in that way so mine looks slightly different but works all the same. Thanks for your help!