Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > rng.Count ====results wrong value?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 28
Default rng.Count ====results wrong value?
Sub tEST()
Dim LastRow As Long
Dim myRng As Range
Dim WS As Worksheet

Set WS = Sheets("DATA 2") '<<< Ganti jika perlu
With WS
LastRow = .Cells(.Rows.Count, "Q").End(xlUp).Row
Set myRng = .Range("Q11:Q" & LastRow)

MsgBox myRng.Count
End With
End Sub
================================================
Hi all,
is there something wrong with the above code.?
Actually,there is no any data on the .Range("Q11:Q" & LastRow)
Why does the msgbox return the value 2
instead 0?.

rgds,

Shiro


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,420
Default rng.Count ====results wrong value?
Because it counts the number of cells, not the number of cells with data?

--
__________________________________
HTH

Bob

"shiro" wrote in message
...
Sub tEST()
Dim LastRow As Long
Dim myRng As Range
Dim WS As Worksheet

Set WS = Sheets("DATA 2") '<<< Ganti jika perlu
With WS
LastRow = .Cells(.Rows.Count, "Q").End(xlUp).Row
Set myRng = .Range("Q11:Q" & LastRow)

MsgBox myRng.Count
End With
End Sub
================================================
Hi all,
is there something wrong with the above code.?
Actually,there is no any data on the .Range("Q11:Q" & LastRow)
Why does the msgbox return the value 2
instead 0?.

rgds,

Shiro




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 62
Default rng.Count ====results wrong value?
On Jun 19, 4:13*pm, "shiro" wrote:
Sub tEST()
* * Dim LastRow As Long
* * Dim myRng As Range
* * Dim WS As Worksheet

* * Set WS = Sheets("DATA 2") *'<<< Ganti jika perlu
* * With WS
* * * * LastRow = .Cells(.Rows.Count, "Q").End(xlUp).Row
* * * * Set myRng = .Range("Q11:Q" & LastRow)

* * MsgBox myRng.Count
* * End With
End Sub
================================================
Hi all,
is there something wrong with the above code.?
Actually,there is no any data on the .Range("Q11:Q" & LastRow)
Why does the msgbox return the value 2
instead 0?.

rgds,

Shiro

Actually it is returning the value of the number of the selected cells
in myRng. So it is actually selecting Q10, Q11 and hence returns 2,
the count of selected cells
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 28
Default rng.Count ====results wrong value?
And then,
how to manipulate the msgbox so that it says
0 if actually no data in cell Q10 and Q11?


"Nayab" wrote in message
...
On Jun 19, 4:13 pm, "shiro" wrote:
Sub tEST()
Dim LastRow As Long
Dim myRng As Range
Dim WS As Worksheet

Set WS = Sheets("DATA 2") '<<< Ganti jika perlu
With WS
LastRow = .Cells(.Rows.Count, "Q").End(xlUp).Row
Set myRng = .Range("Q11:Q" & LastRow)

MsgBox myRng.Count
End With
End Sub
================================================
Hi all,
is there something wrong with the above code.?
Actually,there is no any data on the .Range("Q11:Q" & LastRow)
Why does the msgbox return the value 2
instead 0?.

rgds,

Shiro

Actually it is returning the value of the number of the selected cells
in myRng. So it is actually selecting Q10, Q11 and hence returns 2,
the count of selected cells


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,420
Default rng.Count ====results wrong value?
MsgBox Application.Count(myRng)


--
__________________________________
HTH

Bob

"shiro" wrote in message
...
And then,
how to manipulate the msgbox so that it says
0 if actually no data in cell Q10 and Q11?


"Nayab" wrote in message
...
On Jun 19, 4:13 pm, "shiro" wrote:
Sub tEST()
Dim LastRow As Long
Dim myRng As Range
Dim WS As Worksheet

Set WS = Sheets("DATA 2") '<<< Ganti jika perlu
With WS
LastRow = .Cells(.Rows.Count, "Q").End(xlUp).Row
Set myRng = .Range("Q11:Q" & LastRow)

MsgBox myRng.Count
End With
End Sub
================================================
Hi all,
is there something wrong with the above code.?
Actually,there is no any data on the .Range("Q11:Q" & LastRow)
Why does the msgbox return the value 2
instead 0?.

rgds,

Shiro

Actually it is returning the value of the number of the selected cells
in myRng. So it is actually selecting Q10, Q11 and hence returns 2,
the count of selected cells




Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default rng.Count ====results wrong value?
Or maybe...

MsgBox Application.CountA(myRng)

(just in case <vbg)

Bob Phillips wrote:

MsgBox Application.Count(myRng)

--
__________________________________
HTH

Bob

"shiro" wrote in message
...
And then,
how to manipulate the msgbox so that it says
0 if actually no data in cell Q10 and Q11?


"Nayab" wrote in message
...
On Jun 19, 4:13 pm, "shiro" wrote:
Sub tEST()
Dim LastRow As Long
Dim myRng As Range
Dim WS As Worksheet

Set WS = Sheets("DATA 2") '<<< Ganti jika perlu
With WS
LastRow = .Cells(.Rows.Count, "Q").End(xlUp).Row
Set myRng = .Range("Q11:Q" & LastRow)

MsgBox myRng.Count
End With
End Sub
================================================
Hi all,
is there something wrong with the above code.?
Actually,there is no any data on the .Range("Q11:Q" & LastRow)
Why does the msgbox return the value 2
instead 0?.

rgds,

Shiro

Actually it is returning the value of the number of the selected cells
in myRng. So it is actually selecting Q10, Q11 and hence returns 2,
the count of selected cells



--

Dave Peterson
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 62
Default rng.Count ====results wrong value?
On Jun 19, 4:33*pm, "shiro" wrote:
And then,
how to manipulate the msgbox so that it says
0 if actually no data in cell Q10 and Q11?

"Nayab" wrote in message

...
On Jun 19, 4:13 pm, "shiro" wrote:





Sub tEST()
Dim LastRow As Long
Dim myRng As Range
Dim WS As Worksheet

Set WS = Sheets("DATA 2") '<<< Ganti jika perlu
With WS
LastRow = .Cells(.Rows.Count, "Q").End(xlUp).Row
Set myRng = .Range("Q11:Q" & LastRow)

MsgBox myRng.Count
End With
End Sub
================================================
Hi all,
is there something wrong with the above code.?
Actually,there is no any data on the .Range("Q11:Q" & LastRow)
Why does the msgbox return the value 2
instead 0?.

rgds,

Shiro

Actually it is returning the value of the number of the selected cells
in myRng. *So it is actually selecting Q10, Q11 and hence returns 2,
the count of selected cells- Hide quoted text -

- Show quoted text -

Sub tEST()
Dim LastRow As Long
Dim myRng, r As Range
Dim WS As Worksheet

Set WS = Sheets(2) '<<< Ganti jika perlu
i = 0
With WS
LastRow = .Cells(.Rows.Count, "Q").End(xlUp).Row
Set myRng = .Range("Q11:Q" & LastRow)
For Each r In myRng
If (r.Value = "") Then
Else
i = i + 1
End If
Next r
MsgBox myRng.Count
End With
If i = 0 Then
MsgBox "0"
End If
End Sub
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 28
Default rng.Count ====results wrong value?
Thank's All,
it's a new lesson.

Rgds,

Shiro


"Nayab" wrote in message
...
On Jun 19, 4:33 pm, "shiro" wrote:
And then,
how to manipulate the msgbox so that it says
0 if actually no data in cell Q10 and Q11?

"Nayab" wrote in message

...
On Jun 19, 4:13 pm, "shiro" wrote:





Sub tEST()
Dim LastRow As Long
Dim myRng As Range
Dim WS As Worksheet

Set WS = Sheets("DATA 2") '<<< Ganti jika perlu
With WS
LastRow = .Cells(.Rows.Count, "Q").End(xlUp).Row
Set myRng = .Range("Q11:Q" & LastRow)

MsgBox myRng.Count
End With
End Sub
================================================
Hi all,
is there something wrong with the above code.?
Actually,there is no any data on the .Range("Q11:Q" & LastRow)
Why does the msgbox return the value 2
instead 0?.

rgds,

Shiro

Actually it is returning the value of the number of the selected cells
in myRng. So it is actually selecting Q10, Q11 and hence returns 2,
the count of selected cells- Hide quoted text -

- Show quoted text -

Sub tEST()
Dim LastRow As Long
Dim myRng, r As Range
Dim WS As Worksheet

Set WS = Sheets(2) '<<< Ganti jika perlu
i = 0
With WS
LastRow = .Cells(.Rows.Count, "Q").End(xlUp).Row
Set myRng = .Range("Q11:Q" & LastRow)
For Each r In myRng
If (r.Value = "") Then
Else
i = i + 1
End If
Next r
MsgBox myRng.Count
End With
If i = 0 Then
MsgBox "0"
End If
End Sub


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Wrong results from MATCH function Imladrian Excel Worksheet Functions 4 April 25th 23 09:02 AM
LOOKUP Function gives wrong results AndreasW Excel Worksheet Functions 4 October 21st 09 12:45 AM
VLOOKUP Formula Returns the Wrong Results. Greg Excel Worksheet Functions 3 June 2nd 08 08:11 AM
Array formula returning wrong results TUNGANA KURMA RAJU Excel Discussion (Misc queries) 1 November 19th 05 10:29 AM
macro results wrong Joseph Tibiita Charts and Charting in Excel 2 June 30th 05 05:32 PM


All times are GMT +1. The time now is 04:32 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement