Returning number of blank cells
 

First of all, thanks once again for all the help I've been receiving over
the last 2 days.

I have a chart with location(column A) and sales information (rows B to P).
I was asked to prepare a function that would only display the month the
blank cells are in. For example, this is what I have:


Location Oct Nov Dec
123 Main St. 2000 5252
324 Yonge St. 6358 2551
432 James St. 9563


This is what I want:

Location Month Not Paid
123 Main St. Dec
324 Yonge St. Nov
432 James St. Nov, Dec


If there's another way I should be going about doing this, please feel free
to advise. Thanks once again.

Returning number of blank cells
 

Peter,

A custom User-Defined-Function is the best solution for what you want.

Copy the code below and put it into a codemodule in the same workbook as
your data table. Then use the function like this in cell E2 (reflecting the
structure of your sample data table in A1:D4):

=Report($A$1:$D$1,A2:D2)

Copy the formula down to match your data table, and you're done.

HTH,
Bernie
MS Excel MVP

Function Report(Months As Range, payments As Range) As String
Dim i As Integer
If Months.Cells.Count < payments.Cells.Count Then
Report = "Unequal size ranges"
Else
Report = payments.Cells(1).Value & " "
For i = 2 To payments.Cells.Count
If payments.Cells(i).Value = "" Then
Report = Report & Months.Cells(i).Value & ", "
End If
Next i
If Right(Report, 2) = ", " Then
Report = Left(Report, Len(Report) - 2)
End If
End If
End Function


"Peter McCaul" wrote in message
...
First of all, thanks once again for all the help I've been receiving over
the last 2 days.

I have a chart with location(column A) and sales information (rows B to
P).
I was asked to prepare a function that would only display the month the
blank cells are in. For example, this is what I have:


Location Oct Nov Dec
123 Main St. 2000 5252
324 Yonge St. 6358 2551
432 James St. 9563


This is what I want:

Location Month Not Paid
123 Main St. Dec
324 Yonge St. Nov
432 James St. Nov, Dec


If there's another way I should be going about doing this, please feel
free
to advise. Thanks once again.

Returning number of blank cells
 

Hey Bernie,

Thanks but I have no idea what you're asking me to do. Can you please
digress a bit more? Thanks.


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Peter,

A custom User-Defined-Function is the best solution for what you want.

Copy the code below and put it into a codemodule in the same workbook as
your data table. Then use the function like this in cell E2 (reflecting
the
structure of your sample data table in A1:D4):

=Report($A$1:$D$1,A2:D2)

Copy the formula down to match your data table, and you're done.

HTH,
Bernie
MS Excel MVP

Function Report(Months As Range, payments As Range) As String
Dim i As Integer
If Months.Cells.Count < payments.Cells.Count Then
Report = "Unequal size ranges"
Else
Report = payments.Cells(1).Value & " "
For i = 2 To payments.Cells.Count
If payments.Cells(i).Value = "" Then
Report = Report & Months.Cells(i).Value & ", "
End If
Next i
If Right(Report, 2) = ", " Then
Report = Left(Report, Len(Report) - 2)
End If
End If
End Function


"Peter McCaul" wrote in message
...
First of all, thanks once again for all the help I've been receiving
over
the last 2 days.

I have a chart with location(column A) and sales information (rows B to
P).
I was asked to prepare a function that would only display the month the
blank cells are in. For example, this is what I have:


Location Oct Nov Dec
123 Main St. 2000 5252
324 Yonge St. 6358 2551
432 James St. 9563


This is what I want:

Location Month Not Paid
123 Main St. Dec
324 Yonge St. Nov
432 James St. Nov, Dec


If there's another way I should be going about doing this, please feel
free
to advise. Thanks once again.

Returning number of blank cells
 

Peter,

Sorry, since you were posting to the programming newsgroup I thought you
wanted a programming solution.

Visit both

http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.mvps.org/dmcritchie/excel/install.htm

for more information on how to use User-Defined-Functions.

HTH,
Bernie
MS Excel MVP

"Peter McCaul" wrote in message
...
Hey Bernie,

Thanks but I have no idea what you're asking me to do. Can you please
digress a bit more? Thanks.


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Peter,

A custom User-Defined-Function is the best solution for what you want.

Copy the code below and put it into a codemodule in the same workbook as
your data table. Then use the function like this in cell E2 (reflecting
the
structure of your sample data table in A1:D4):

=Report($A$1:$D$1,A2:D2)

Copy the formula down to match your data table, and you're done.

HTH,
Bernie
MS Excel MVP

Function Report(Months As Range, payments As Range) As String
Dim i As Integer
If Months.Cells.Count < payments.Cells.Count Then
Report = "Unequal size ranges"
Else
Report = payments.Cells(1).Value & " "
For i = 2 To payments.Cells.Count
If payments.Cells(i).Value = "" Then
Report = Report & Months.Cells(i).Value & ", "
End If
Next i
If Right(Report, 2) = ", " Then
Report = Left(Report, Len(Report) - 2)
End If
End If
End Function


"Peter McCaul" wrote in message
...
First of all, thanks once again for all the help I've been receiving
over
the last 2 days.

I have a chart with location(column A) and sales information (rows B
to
P).
I was asked to prepare a function that would only display the month
the
blank cells are in. For example, this is what I have:


Location Oct Nov Dec
123 Main St. 2000 5252
324 Yonge St. 6358 2551
432 James St. 9563


This is what I want:

Location Month Not Paid
123 Main St. Dec
324 Yonge St. Nov
432 James St. Nov, Dec


If there's another way I should be going about doing this, please feel
free
to advise. Thanks once again.

Returning number of blank cells
 

I do want a programming solution. I just didn't understand what you had
given me. I was hoping you could explain in more detail. It will be
appreciated, thanks.


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Peter,

Sorry, since you were posting to the programming newsgroup I thought you
wanted a programming solution.

Visit both

http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.mvps.org/dmcritchie/excel/install.htm

for more information on how to use User-Defined-Functions.

HTH,
Bernie
MS Excel MVP

"Peter McCaul" wrote in message
...
Hey Bernie,

Thanks but I have no idea what you're asking me to do. Can you please
digress a bit more? Thanks.


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Peter,

A custom User-Defined-Function is the best solution for what you want.

Copy the code below and put it into a codemodule in the same workbook
as
your data table. Then use the function like this in cell E2
(reflecting
the
structure of your sample data table in A1:D4):

=Report($A$1:$D$1,A2:D2)

Copy the formula down to match your data table, and you're done.

HTH,
Bernie
MS Excel MVP

Function Report(Months As Range, payments As Range) As String
Dim i As Integer
If Months.Cells.Count < payments.Cells.Count Then
Report = "Unequal size ranges"
Else
Report = payments.Cells(1).Value & " "
For i = 2 To payments.Cells.Count
If payments.Cells(i).Value = "" Then
Report = Report & Months.Cells(i).Value & ", "
End If
Next i
If Right(Report, 2) = ", " Then
Report = Left(Report, Len(Report) - 2)
End If
End If
End Function


"Peter McCaul" wrote in message
...
First of all, thanks once again for all the help I've been receiving
over
the last 2 days.

I have a chart with location(column A) and sales information (rows B
to
P).
I was asked to prepare a function that would only display the month
the
blank cells are in. For example, this is what I have:


Location Oct Nov Dec
123 Main St. 2000 5252
324 Yonge St. 6358 2551
432 James St. 9563


This is what I want:

Location Month Not Paid
123 Main St. Dec
324 Yonge St. Nov
432 James St. Nov, Dec


If there's another way I should be going about doing this, please
feel
free
to advise. Thanks once again.

Returning number of blank cells
 

Peter,

Visit the two links for instructions on how to use the code that I posted
previously.

HTH,
Bernie
MS Excel MVP

"Peter McCaul" wrote in message
...
I do want a programming solution. I just didn't understand what you had
given me. I was hoping you could explain in more detail. It will be
appreciated, thanks.

Returning number of blank cells
 

Hey Bernie,

Sorry for bugging you. I went back to your suggestion. After reading it a
few times, i think i understand what you wanted me to do. Anyways, the code
is enter into a module and the formula I've placed in cell e2 (next to the
chart). I get the #name? error when I press enter. I've retyped it a few
times, even copied and paste what you typed but to no avail. Any help is
appreciated, thanks.
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Peter,

Visit the two links for instructions on how to use the code that I posted
previously.

HTH,
Bernie
MS Excel MVP

"Peter McCaul" wrote in message
...
I do want a programming solution. I just didn't understand what you had
given me. I was hoping you could explain in more detail. It will be
appreciated, thanks.

Returning number of blank cells
 

Peter,

I will send you a working version to your hotmail address.

HTH,
Bernie
MS Excel MVP

"Peter McCaul" wrote in message
...
Hey Bernie,

Sorry for bugging you. I went back to your suggestion. After reading it
a
few times, i think i understand what you wanted me to do. Anyways, the
code
is enter into a module and the formula I've placed in cell e2 (next to the
chart). I get the #name? error when I press enter. I've retyped it a few
times, even copied and paste what you typed but to no avail. Any help is
appreciated, thanks.
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Peter,

Visit the two links for instructions on how to use the code that I
posted
previously.

HTH,
Bernie
MS Excel MVP

"Peter McCaul" wrote in message
...
I do want a programming solution. I just didn't understand what you
had
given me. I was hoping you could explain in more detail. It will be
appreciated, thanks.