continued issue with countif function
 

hi all - many thanks to any reples its most appreciated although im still
having a few issues as my spreadsheet now has #NAME writen in many cells - my
code is as follows

Sub spot_duplicates()

Dim cell As Range
Dim counter As Integer

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

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

End Sub

--
C Ward

continued issue with countif function
 

You forgot some quotes:
Instead of:
For counter = 4 To 1443
Range("V" & counter).Formula = "=IF(COUNTIF(H4:H1443,H4)=1,H&counter)"
Next counter

For counter = 4 To 1443
Range("V" & counter).Formula = "=IF(COUNTIF(H4:H1443,H4)=1,H"&counter")"
Next counter
Look at the end of the line, need to end the H section add the counter
"Number" then end the formula with a parantheses between quotes.



"christopher ward" wrote:

hi all - many thanks to any reples its most appreciated although im still
having a few issues as my spreadsheet now has #NAME writen in many cells - my
code is as follows

Sub spot_duplicates()

Dim cell As Range
Dim counter As Integer

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

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

End Sub

--
C Ward

continued issue with countif function
 

Two additional things. I would discourage using the data type Integer for a
variable that is going to be used on rows in a worksheet. The maximum value
of Integer would not cover the maximum number of rows in any given worksheet.
If you look at the data type returned from Range.Rows you would see that it
is a LONG (Ie. long integer.)

2ndly, you do not NEED to select the range. At least for this snippet of
code. You may have some other reason to select it, but it is not required to
perform the actions you are doing.


"christopher ward" wrote:

hi all - many thanks to any reples its most appreciated although im still
having a few issues as my spreadsheet now has #NAME writen in many cells - my
code is as follows

Sub spot_duplicates()

Dim cell As Range
Dim counter As Integer

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

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

End Sub

--
C Ward

continued issue with countif function
 

You have more replies at your other thread.

christopher ward wrote:

hi all - many thanks to any reples its most appreciated although im still
having a few issues as my spreadsheet now has #NAME writen in many cells - my
code is as follows

Sub spot_duplicates()

Dim cell As Range
Dim counter As Integer

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

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

End Sub

--
C Ward

--

Dave Peterson