# Range = CountIF problem

#### Ray805

##### New Member
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.
 0101 0104 0109 0102 0105 0110 0103 0106 0111 0107 0112 0108 0113

<tbody>
</tbody>

the current code im trying to use is
Code:
``````    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!

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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 *

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

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.

Code:
``````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:

Code:
``````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]``````

Replies
15
Views
647
Replies
0
Views
254
Replies
0
Views
153
Replies
7
Views
1K
Replies
2
Views
297

1,203,354
Messages
6,054,919
Members
444,759
Latest member
TeckTeck

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back