continued issue with countif function

[christopher ward]

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

[GB]

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

[GB]

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

[Dave Peterson]

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