[SOLVED] Finding number of empty cells

All:

I read through numerous similar postings and stilll could not get my
simple UDF to work.

Function CountColumns(RngStartingPoint As Range) As Integer
CountColumns = Range(Cells(RngStartingPoint), Cells(1,
Cols.Count).End(xlToLeft))
End Function

I want to be able to count the number of blank cells from a given range
("AA1") back to a populated cell ("G1"). The populated cells contain
an "x".

Any assistance as to why this function is incorrect, would be greatly
appreciated.

Cheers.

Function CountColumns(RngStartingPoint As Excel.Range) As Long
CountColumns = Application.CountBlank(Range(RngStartingPoint, _
Cells(1, Columns.Count).End(xlToLeft)))
End Function

Sub CallFunction()
MsgBox CountColumns(Range("G1"))
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Floyd" <[email protected]>
wrote in message
All:
I read through numerous similar postings and stilll could not get my
simple UDF to work.

Function CountColumns(RngStartingPoint As Range) As Integer
CountColumns = Range(Cells(RngStartingPoint), Cells(1,
Cols.Count).End(xlToLeft))
End Function

I want to be able to count the number of blank cells from a given range
("AA1") back to a populated cell ("G1"). The populated cells contain
an "x".
Any assistance as to why this function is incorrect, would be greatly
appreciated.
Cheers.

Thank you.

Cheers.

Trial Number 2:

The "blank" cells contain the following formula, which may be
interfering with the calculation:
=IF(OR(AND(V2>=$G129,V2<$J128),AND(V2>=$J129,V2<$J130)),"X"," ")

This function
Function CountColumns(RngStartingPoint As Excel.Range) As Long
CountColumns = Application.CountBlank(Range(RngStartingPoint, _
Cells(1, Columns.Count).End(xlToLeft)))
End Function

provides wildly large numbers such as 616 instead of 2.

Should I have modified the "1" to match the row for others rows besides
one?

Also, is the countblank being messed up due to the equation that is in
those cells?

Thanks in advance.

I truly appreciate your responses.

Cheers.

Formulas should not cause a problem.
The first number in Cells(1, 2) specifies the row number.
The second number specifies the column number.
The above is Range("B1")

When specifying a range, you normally use the top left cell
and the bottom right cell. However, either one can come first.
Range("G1", "J10") is the same as Range("J10", "G1")
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Floyd" <[email protected]>
wrote in message
Trial Number 2:
The "blank" cells contain the following formula, which may be
interfering with the calculation:
=IF(OR(AND(V2>=$G129,V2<$J128),AND(V2>=$J129,V2<$J130)),"X"," ")

This function
Function CountColumns(RngStartingPoint As Excel.Range) As Long
CountColumns = Application.CountBlank(Range(RngStartingPoint, _
Cells(1, Columns.Count).End(xlToLeft)))
End Function

provides wildly large numbers such as 616 instead of 2.
Should I have modified the "1" to match the row for others rows besides
one?
Also, is the countblank being messed up due to the equation that is in
those cells?
Thanks in advance.
I truly appreciate your responses.
Cheers.

Mr. Cone,

Thanks for your continue persistence.

I am using the following two functions based on your response to
bracket data in a row.

Function CountColumnsRight(RngStartingPoint As Excel.Range) As Long
CountColumnsRight = Application.CountBlank(Range(RngStartingPoint, _
Cells(4, Columns.Count).End(xlToLeft)))
End Function

Function CountColumnsLeft(RngStartingPoint As Excel.Range) As Long
CountColumnsLeft = Application.CountBlank(Range(RngStartingPoint, _
Cells(4, Columns.Count).End(xlToRight)))
End Function

If I delete the worksheet equation, then the CountColumnsRight UDF
works, but not the other.

I tried using your function with COUNTIF, IsEmpty and IsNull, but I
couldn't get it to work.

Is there a way to modify my worksheet equations, such that it will work
with your function?

Any other suggestions?

Cheers.


[The server temporarily went down. I hope this doesn't get posted
twice.]

Try the functions with the MsgBox line added in each.
I suspect the ranges Excel uses are not what you think they are...

Function CountColumnsRight(RngStartingPoint As Excel.Range) As Long
MsgBox Range(RngStartingPoint, Cells(4, Columns.Count).End(xlToLeft)).Address

CountColumnsRight = Application.CountBlank(Range(RngStartingPoint, _
Cells(4, Columns.Count).End(xlToLeft)))
End Function
'---
Function CountColumnsLeft(RngStartingPoint As Excel.Range) As Long
MsgBox Range(RngStartingPoint, Cells(4, Columns.Count).End(xlToRight)).Address

CountColumnsLeft = Application.CountBlank(Range(RngStartingPoint, _
Cells(4, Columns.Count).End(xlToRight)))
End Function
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html


"Floyd" <[email protected]>
wrote in message
Mr. Cone,
Thanks for your continue persistence.
I am using the following two functions based on your response to
bracket data in a row.

Function CountColumnsRight(RngStartingPoint As Excel.Range) As Long
CountColumnsRight = Application.CountBlank(Range(RngStartingPoint, _
Cells(4, Columns.Count).End(xlToLeft)))
End Function

Function CountColumnsLeft(RngStartingPoint As Excel.Range) As Long
CountColumnsLeft = Application.CountBlank(Range(RngStartingPoint, _
Cells(4, Columns.Count).End(xlToRight)))
End Function

If I delete the worksheet equation, then the CountColumnsRight UDF
works, but not the other.
I tried using your function with COUNTIF, IsEmpty and IsNull, but I
couldn't get it to work.
Is there a way to modify my worksheet equations, such that it will work
with your function?
Any other suggestions?
Cheers.

[The server temporarily went down. I hope this doesn't get posted
twice.]

Mr. Cone,

That is helpful. For the CountColumnsRight UDF, it is returning a
single cell which is the one that is passed to it.

I will keep beating on the computer to see, if I can get the equation
to grab the correct range. For whatever reason, the worksheet equation
is stopping it from grabbing the intended range.

Thank you for your time and assistance.

Cheers.

Mr. Cone,

It is the Shift-Right or Left that is not recognizing the "X" from my
worksheet equation.

Any thoughts on this?

Mr. Cone,

I am using two equations based on yours.

Function CountColumnsRight(RngStartingPoint As Excel.Range) As Long
CountColumnsRight = Application.CountBlank(Range(RngStartingPoint, _
Cells(4, Columns.Count).End(xlToLeft)))
End Function

Function CountColumnsLeft(RngStartingPoint As Excel.Range) As Long
CountColumnsLeft = Application.CountBlank(Range(RngStartingPoint, _
Cells(4, Columns.Count).End(xlToRight)))
End Function

If I delete the worksheet equation, then the CountColumnsRight UDF
works but not the other.

Is there a way to use your UDF with a conditional statement, such as
COUNTIF, IsNull, IsEmpty, so that it will work with my worksheet
equation? I tried these, but I couldn't get it to work.

Sorry to keep this going.

Cheers.

You lost me, there is no "shift" in the code.
Each function counts the blank cells in the range shown in the MsgBox.
Are the ranges shown the correct ones?
You must be able to tell Excel the exact location of the ranges or
nothing is going to work.
Jim Cone

"Floyd" <[email protected]>
wrote in message

It is the Shift-Right or Left that is not recognizing the "X" from my
worksheet equation.
Any thoughts on this?