Range = CountIF problem

[Ray805]

Hi,

I cannot work out what im doing wrong!
What im trying to do is copy a long list from column A and paste in several differnt columns, with the first column being the left over.

from a list in column A i need to count how many there are. then i need to take that total then minus off 10(to fill the other two columns) then it will give me the numbers of lines i need to put in the first column.
I.e.[TABLE="width: 500"]
<tbody>[TR]
[TD]0101
[/TD]
[TD]0104
[/TD]
[TD]0109
[/TD]
[/TR]
[TR]
[TD]0102
[/TD]
[TD]0105
[/TD]
[TD]0110
[/TD]
[/TR]
[TR]
[TD]0103
[/TD]
[TD]0106
[/TD]
[TD]0111
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0107
[/TD]
[TD]0112
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0108
[/TD]
[TD]0113
[/TD]
[/TR]
</tbody>[/TABLE]

the current code im trying to use is

    Dim myRng As Range
    Dim nResult As Long
    Dim oneGLRnumber As Long
    Dim LASTR As Integer
    Dim oneGLR As Integer
    Dim oneGFR As Integer
    LASTR = Cells(Rows.Count, "A").End(xlUp).Row
        Set myRng = Range("A2:A" & LASTR)
        nResult = Application.CountIf(myRng, "*")
        oneGLRnumber = (nResult - 10)
oneGFR = Range("A2")
oneGLR = oneGLRnumber
Set oneGRange = Range(oneGFR & ":A" & oneGLR)
oneGRange.Activate
Selection.Copy
Range("S1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'twoGFR = Range("A2")
'twoGLR = Range("")
'threeGFR = Range("A2")
'threeGLR = Range("")

i havent even started on two or three yet as i cnat even get the one to work
any help on this would be awesome!

 

[dermie_72]

instead of countif "*", try doing a countblank, then nresults can be lastrow-countblank.
last row give you the total rows used in column A. Count blank identifies how many are not there, giving you the same result as countif *

 

[Ray805]

sorry i dont really understand, does countIf not work in a macro? i have never used countblank before :S

 

[dermie_72]

countif does work in the macro, but it looks for a specific string, not a wildcard. At least that's my understanding, because when I changed the "*" to a variable that was in the list, it returned the correct value. Try countblank in excel to get your parameters, but when I tested it, it worked for me.

 

[Ray805]

LASTR = Cells(Rows.Count, "A").End(xlUp).Row
        Set myRng = Range("A2:A" & LASTR)
        nResult = WorksheetFunction.CountIf(myRng, "*")
        oneGLRnumber = (nResult - 159)
'MsgBox oneGLRnumber

this seems to work fine and the msg box returns the correct value im looking for, but i cant seem to use the "oneGLRnumber" result in:

oneGFR = Range("A2")
oneGLR = oneGLRnumber[INDENT]Set oneGLRange = Range(oneGFR & ":A" & oneGLR)
oneGLRange.Copy
Range("S1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False[/INDENT]