Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif function Issue | Excel Worksheet Functions | |||
COUNTIF Function issue | Excel Worksheet Functions | |||
Countif issue | Excel Worksheet Functions | |||
Excel Function . . . Continued | Excel Discussion (Misc queries) | |||
issue with countif in vba | Excel Programming |