using countif from a vba macro

[Guest]

hi all

i have a problem with a countif function if anyone could help please do : (
im a novice user so forgive me if my use of system is not as it should be )

i have a range H4:H1443.. its full of product name with many duplicates

as in ;

chris
tony
brian
chris

h4 is start of range
H1443 is end of range

what i want to do in vba in cells t4:T1443 is write something like -

if ifcount(H4:H1443,H4)=1,h4) to be placed in cell T4
and then in T5 using if ifcount(H4:H1443,H4)=1,H5)

formula works when i type it in excel but how do i put it into a vba macro ?

 

[Yngve]

Hi christopher

Start recording macro and put the formula in the cell.
Stop recordig an goto VB/prodject/Modul, ther you will find the
macro

Regards Yngve

 

[Bob Phillips]

Range("T4"),Formula = "=IF(COUNTIF(H4:H1443,H4)=1,H4)"
Range("T5").Formula = "=IF(COUNTIF(H4:H1443,H4)=1,H5)"


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

I can not disagree with what Yngve stated. I can say that it really depends
on what you mean by putting it in a macro. For example, if you want to say,
I have this name, and I want to return the value of the number of cells that
have that data, then I would do something like this:

Public function NumberOfData(SourceCell as range) as long
Dim I as int

NumberOfData = 0

For I = 5 to 1443
If ActiveSheet.Cells(I, SourceCell.column).value = SourceCell.value
then
NumberOfData = NumberOfData + 1
End if

Next I

NumberOfData = NumberOfData - 1
end function

This function will return the value of the number of duplicates of the
information that is contained in the "original" cell. I say duplicates
because of the - 1 just before the end function. If you wanted the
occurrence of the name (The total number of times that the item occurred),
then just remove that last -1 item.

To get to this function you would need something like:

OccurrenceOfItem = NumberOfData(ActiveSheet.Cells(5, "A"))
I'm sorry I do not have help files available but I think the "A" would work
otherwise I know that you could use the number 1 to refer to column 1.

Best of luck.

 

[Guest]

You can automate it a little more using the below code by saying:

For I = 4 to X <- Where X is the last row you want to include data
Range("T"&I).Formula = "=IF(COUNTIF(H4:H1443,H4)=1,H"&I&")"
Next I

This will put the formula in column T for each row.
Realize too that if you make a N by M matrix, that excel only allows I think
it is 256 columns it may be only 255 unless someone else knows a way around
this. I do know that you could establish multiple worksheets in the same
workbook where the start of each worksheet is where the last one ended, and
thus you can have a virtually unlimited amount of columns to do your
"reviews".

 

[Guest]

hi all - many thanks for trying to help but i still am failing - my sheet now
shows the following in colum V

#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?

the code i run is like this


Sub spot_duplicates()

'For I = 4 to X <- Where X is the last row you want to include data
' Range("T"&I).Formula = "=IF(COUNTIF(H4:H1443,H4)=1,H"&I&")"
'Next i

Dim cell As Range
Dim i As Integer

Sheets("Data").Select
Range("V4:V1443").Select

For i = 4 To 1443
Range("V" & i).Formula = "=IF(COUNTIF(H4:H1443,H4)=1,H&i)"
Next i

End Sub

 

[Dave Peterson]

You changed GB's formula.

Watch those double quotes and try again.

But you could get it all at once and skip the looping:


Range("T4:T1443").Formula = "=IF(COUNTIF($H$4:$H$1443,H4)=1,H4)"