VBA not getting count correct

[motilulla]

VBA not getting count correct

Hello,

I am trying to write line in vba that count used cells in range B3:J6, I am getting wrong count (12-7-4 &11) I want them to get correct are 9-6-3 & 1

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1												XYZ
2	ABC	A|A	A|B	A|C	A|D	A|E	A|F	A|G	A|H	A|I	1	2
3	XYZ	2	11	1	3	5	0	7	50	21	2	2
4	XYZ	2	4	6	7	8	9				3	2
5	XYZ	1	2	3							4	2
6	XYZ	1									5	2
7											6	2
8											7	2
9											8	2
10											9	2
11											10	2
Hoja1

Sub Count()

     Dim colLast1 As Long
     Dim colLast2 As Long
     Dim colLast3 As Long
     Dim colLast4 As Long
     
    
     colLast1 = Range("B3").End(xlToRight).Column
     colLast2 = Range("B4").End(xlToRight).Column
     colLast3 = Range("B5").End(xlToRight).Column
     colLast4 = Range("B6").End(xlToRight).Column
     MsgBox colLast1
     MsgBox colLast2
     MsgBox colLast3
     MsgBox colLast4
     End Sub

Thank you all

Excel 2000
Regards,
Moti

 

[Rick Rothstein]

Some questions...

1) What is in the range B3:J6... formulas or constants (and blanks)?

2) Do you want a count of each row separately or do you want the total for the entire range?

3) Where do you want the count(s) to go to?

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG29Nov58
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B3"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
  MsgBox "Row " & Dn.Row & ", Count =" & Application.CountA(Dn.Resize(, 9))
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[motilulla]

Some questions...

1) What is in the range B3:J6... formulas or constants (and blanks)?

2) Do you want a count of each row separately or do you want the total for the entire range?

3) Where do you want the count(s) to go to?

Hello Rick Rothstein, thank you

1. Range B3:J6 are constants
2. I want to count of each row separately

MsgBox colLast1 = 9
MsgBox colLast2 = 6
MsgBox colLast3 = 3
MsgBox colLast4 = 1

3. Just give me a correct msg 9, 6, 3 & 1

Regards,
Moti

 

[motilulla]

Try this:-

[COLOR=navy]Sub[/COLOR] MG29Nov58
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] Rng = Range(Range("B3"), Range("B" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
  MsgBox "Row " & Dn.Row & ", Count =" & Application.CountA(Dn.Resize(, 9))
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Thank you MickG, is it possible to give 4 separate messages I can not explain but like 4 deferent variables like I shown

MsgBox colLast1 = 9
MsgBox colLast2 = 6
MsgBox colLast3 = 3
MsgBox colLast4 = 1

Or instead colLast1 any

Regards,
Moti

 

[motilulla]

Further I want to use "reference colLast1, 2 , 3 ,4" like this

 For I = 2 To colLast1
        For j = 2 To colLast2
           For k = 2 To colLast3
              For l = 2 To colLast4

 

[motilulla]

Hello,
Here is the code

 Sub Count()
     Dim colLast1 As Long
     Dim colLast2 As Long
     Dim colLast3 As Long
     Dim colLast4 As Long
          colLast1 = Range("B3").End(xlToRight).Column
          colLast2 = Range("B4").End(xlToRight).Column
          colLast3 = Range("B5").End(xlToRight).Column
          colLast4 = Range("B6").End(xlToRight).Column
   For I = 2 To colLast1
        For j = 2 To colLast2
           For k = 2 To colLast3
              For l = 2 To colLast4
 End Sub

The code above is giving me wrong count with post#1 layout
For I = 2 To colLast1 = is giving 2 to 12
For j = 2 To colLast2 = is giving 2 to 7
For k = 2 To colLast3 = is giving 2 to 4
For l = 2 To colLast4 = is giving 2 to 11

Correct would be as below
For I = 2 To colLast1 = I want code could give 2 to 9
For j = 2 To colLast2 = I want code could give2 to 6
For k = 2 To colLast3 = I want code could give2 to 3
For l = 2 To colLast4 = I want code could give2 to 1

Please can some one take a look?

Thank you

Regards,
Moti

 

[motilulla]

Bump

 

[motilulla]

Thank you MickG, is it possible to give 4 separate messages I can not explain but like 4 deferent variables like I shown

MsgBox colLast1 = 9
MsgBox colLast2 = 6
MsgBox colLast3 = 3
MsgBox colLast4 = 1

Or instead colLast1 any

Regards,
Moti

Thank you MickG, Finally I manage to work your code “Application.CountA” as below.

[COLOR=#000000]Sub Count()[/COLOR]
[COLOR=#000000]     MyCount1 = Application.CountA(Range("B3:J3"))
     MyCount2 = Application.CountA(Range("B4:J4"))
     MyCount3 = Application.CountA(Range("B5:J5"))
     MyCount4 = Application.CountA(Range("B6:J6"))[/COLOR]
[COLOR=#000000]     MsgBox MyCount1
     MsgBox MyCount2
     MsgBox MyCount3
     MsgBox MyCount4[/COLOR]
[COLOR=#000000]End Sub[/COLOR]

Regards,
Moti

 

[MickG]

You're welcome
Pleased you got it to work !!
Regrds Mick