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"
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<$J 130)),"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"
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<$J 130)),"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.]

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.