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 ?


--
C Ward

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

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)

"christopher ward" wrote in
message ...
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
?


--
C Ward

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.

"christopher ward" wrote:

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 ?


--
C Ward

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".


"Bob Phillips" wrote:

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)

"christopher ward" wrote in
message ...
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
?


--
C Ward

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

--
C Ward


"Yngve" wrote:

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

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)"

christopher ward wrote:

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

--
C Ward

"Yngve" wrote:

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



--

Dave Peterson