create a "check" macro
 

First off I really need to thank Frank, Tom and Ron! While some of the
others here have also helped out, these three have really stood out when
answering my questions! You're help has been amazing! Your patience with
the rest of us is very remarkable and much appreciated! Thanks for taking
your time to help us out!

Now to the question...
I want to write a macro that checks column "V" (or range "V9:V133") on a
payroll worksheet and then if there is nothing in a cell, the macro returns
a message asking if this is correct... and then allows the user to change
the cell(s) and continues to check until the entire range has been checked.
There might be instances of "0" hours so it's OK to have "0" but not a blank
in the cell.

In the perfect world... the process would only check for the information in
the range "V9:V133" that had information in range "A9:A133"

This is probably pretty easy but I'm not clear as to how to figure this one
out... especially with the message box(es) that I would need to create...

All this because I have users who forget to put in total hours for employees
on a worksheet... ohh wellll

I'm not sure if this can even be done... but any suggestions would be great!
Thanks in advance for any and all assistance!

Greg

create a "check" macro
 

Hi Greg

You can use the Counta function to check if all the cells have a value

If Application.WorksheetFunction.CountA(Range("V9:V13 3")) < _
Range("V9:V133").Cells.Count Then MsgBox ""

You can use the beforeclose event to check the above and use Cancel = true if not
all the cells have a value

Post back if you need help with that

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Greg Liber" wrote in message ...
First off I really need to thank Frank, Tom and Ron! While some of the
others here have also helped out, these three have really stood out when
answering my questions! You're help has been amazing! Your patience with
the rest of us is very remarkable and much appreciated! Thanks for taking
your time to help us out!

Now to the question...
I want to write a macro that checks column "V" (or range "V9:V133") on a
payroll worksheet and then if there is nothing in a cell, the macro returns
a message asking if this is correct... and then allows the user to change
the cell(s) and continues to check until the entire range has been checked.
There might be instances of "0" hours so it's OK to have "0" but not a blank
in the cell.

In the perfect world... the process would only check for the information in
the range "V9:V133" that had information in range "A9:A133"

This is probably pretty easy but I'm not clear as to how to figure this one
out... especially with the message box(es) that I would need to create...

All this because I have users who forget to put in total hours for employees
on a worksheet... ohh wellll

I'm not sure if this can even be done... but any suggestions would be great!
Thanks in advance for any and all assistance!

Greg

create a "check" macro
 

Greg,

Dim ans
For i = 9 To 133
If Not IsEmpty(Cells(i, "A").Value) And IsEmpty(Cells(i, "V")) Then
ans = ""
Do While ans = ""
ans = InputBox("Cell " & Cells(i, "V").Address(False, False)
& " needs data, please supply", _
"Data Completion")

Loop
End If
Next i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Greg Liber" wrote in message
...
First off I really need to thank Frank, Tom and Ron! While some of the
others here have also helped out, these three have really stood out when
answering my questions! You're help has been amazing! Your patience with
the rest of us is very remarkable and much appreciated! Thanks for taking
your time to help us out!

Now to the question...
I want to write a macro that checks column "V" (or range "V9:V133") on a
payroll worksheet and then if there is nothing in a cell, the macro
returns
a message asking if this is correct... and then allows the user to change
the cell(s) and continues to check until the entire range has been
checked.
There might be instances of "0" hours so it's OK to have "0" but not a
blank
in the cell.

In the perfect world... the process would only check for the information
in
the range "V9:V133" that had information in range "A9:A133"

This is probably pretty easy but I'm not clear as to how to figure this
one
out... especially with the message box(es) that I would need to create...

All this because I have users who forget to put in total hours for
employees
on a worksheet... ohh wellll

I'm not sure if this can even be done... but any suggestions would be
great!
Thanks in advance for any and all assistance!

Greg

create a "check" macro
 

Hi Bob!
Thanks for the help!
But, can't seem to get this to work... any ideas?

Greg

"Bob Phillips" wrote in message
...
Greg,

Dim ans
For i = 9 To 133
If Not IsEmpty(Cells(i, "A").Value) And IsEmpty(Cells(i, "V"))
Then
ans = ""
Do While ans = ""
ans = InputBox("Cell " & Cells(i, "V").Address(False,
False)
& " needs data, please supply", _
"Data Completion")

Loop
End If
Next i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Greg Liber" wrote in message
...
First off I really need to thank Frank, Tom and Ron! While some of the
others here have also helped out, these three have really stood out when
answering my questions! You're help has been amazing! Your patience
with
the rest of us is very remarkable and much appreciated! Thanks for
taking
your time to help us out!

Now to the question...
I want to write a macro that checks column "V" (or range "V9:V133") on a
payroll worksheet and then if there is nothing in a cell, the macro
returns
a message asking if this is correct... and then allows the user to
change
the cell(s) and continues to check until the entire range has been
checked.
There might be instances of "0" hours so it's OK to have "0" but not a
blank
in the cell.

In the perfect world... the process would only check for the information
in
the range "V9:V133" that had information in range "A9:A133"

This is probably pretty easy but I'm not clear as to how to figure this
one
out... especially with the message box(es) that I would need to
create...

All this because I have users who forget to put in total hours for
employees
on a worksheet... ohh wellll

I'm not sure if this can even be done... but any suggestions would be
great!
Thanks in advance for any and all assistance!

Greg

create a "check" macro
 

thanks for the info... but yup need some help with the rest of what you said
to do... sorry I don't want to impose.

Greg

"Ron de Bruin" wrote in message
...
Hi Greg

You can use the Counta function to check if all the cells have a value

If Application.WorksheetFunction.CountA(Range("V9:V13 3")) < _
Range("V9:V133").Cells.Count Then MsgBox ""

You can use the beforeclose event to check the above and use Cancel = true
if not
all the cells have a value

Post back if you need help with that

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Greg Liber" wrote in message
...
First off I really need to thank Frank, Tom and Ron! While some of the
others here have also helped out, these three have really stood out when
answering my questions! You're help has been amazing! Your patience
with
the rest of us is very remarkable and much appreciated! Thanks for
taking
your time to help us out!

Now to the question...
I want to write a macro that checks column "V" (or range "V9:V133") on a
payroll worksheet and then if there is nothing in a cell, the macro
returns
a message asking if this is correct... and then allows the user to
change
the cell(s) and continues to check until the entire range has been
checked.
There might be instances of "0" hours so it's OK to have "0" but not a
blank
in the cell.

In the perfect world... the process would only check for the information
in
the range "V9:V133" that had information in range "A9:A133"

This is probably pretty easy but I'm not clear as to how to figure this
one
out... especially with the message box(es) that I would need to
create...

All this because I have users who forget to put in total hours for
employees
on a worksheet... ohh wellll

I'm not sure if this can even be done... but any suggestions would be
great!
Thanks in advance for any and all assistance!

Greg

create a "check" macro
 

Greg,

It worked in my tests.What happens when you run it?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Greg Liber" wrote in message
...
Hi Bob!
Thanks for the help!
But, can't seem to get this to work... any ideas?

Greg

"Bob Phillips" wrote in message
...
Greg,

Dim ans
For i = 9 To 133
If Not IsEmpty(Cells(i, "A").Value) And IsEmpty(Cells(i, "V"))
Then
ans = ""
Do While ans = ""
ans = InputBox("Cell " & Cells(i, "V").Address(False,
False)
& " needs data, please supply", _
"Data Completion")

Loop
End If
Next i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

create a "check" macro
 

Sorry Bob!

Tried it again and it works FANTASTIC! One issue down and several more to
go!

Thanks for your help!

TTFN
Greg

"Bob Phillips" wrote in message
...
Greg,

It worked in my tests.What happens when you run it?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Greg Liber" wrote in message
...
Hi Bob!
Thanks for the help!
But, can't seem to get this to work... any ideas?

Greg

"Bob Phillips" wrote in message
...
Greg,

Dim ans
For i = 9 To 133
If Not IsEmpty(Cells(i, "A").Value) And IsEmpty(Cells(i, "V"))
Then
ans = ""
Do While ans = ""
ans = InputBox("Cell " & Cells(i, "V").Address(False,
False)
& " needs data, please supply", _
"Data Completion")

Loop
End If
Next i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

create a "check" macro
 

Greg,

You can use the following to do what you want.

Sub check_values()
For Each cell In Range("v9", "v133")
If cell.Value = "" Then
If MsgBox("No Value in Cell" & cell.Address & " . Is this OK? "
vbYesNo, "Check Value") = vbNo Then
cell.Value = InputBox("Please enter value for Cell" & cell.Address
"New Value", 0)
End If
End If

Next

End Sub


HTH

Matt


Greg Liber wrote:
*Hi Bob!
Thanks for the help!
But, can't seem to get this to work... any ideas?

Greg

"Bob Phillips" wrote in message
...
Greg,

Dim ans
For i = 9 To 133
If Not IsEmpty(Cells(i, "A").Value) And IsEmpty(Cells(i
"V"))
Then
ans = ""
Do While ans = ""
ans = InputBox("Cell " & Cells(i
"V").Address(False,
False)
& " needs data, please supply", _
"Data Completion")

Loop
End If
Next i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Greg Liber" wrote in message
...
First off I really need to thank Frank, Tom and Ron! While som
of the
others here have also helped out, these three have really stoo
out when
answering my questions! You're help has been amazing! You
patience
with
the rest of us is very remarkable and much appreciated! Thank
for
taking
your time to help us out!

Now to the question...
I want to write a macro that checks column "V" (or rang
"V9:V133") on a
payroll worksheet and then if there is nothing in a cell, th
macro
returns
a message asking if this is correct... and then allows the use
to
change
the cell(s) and continues to check until the entire range ha
been
checked.
There might be instances of "0" hours so it's OK to have "0" bu
not a
blank
in the cell.

In the perfect world... the process would only check for th
information
in
the range "V9:V133" that had information in range "A9:A133"

This is probably pretty easy but I'm not clear as to how t
figure this
one
out... especially with the message box(es) that I would nee
to
create...

All this because I have users who forget to put in total hour
for
employees
on a worksheet... ohh wellll

I'm not sure if this can even be done... but any suggestion
would be
great!
Thanks in advance for any and all assistance!

Greg





--
Message posted from http://www.ExcelForum.com

create a "check" macro
 

Hi Greg

I see you got it working with Bob's code

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Greg Liber" wrote in message ...
thanks for the info... but yup need some help with the rest of what you said
to do... sorry I don't want to impose.

Greg

"Ron de Bruin" wrote in message
...
Hi Greg

You can use the Counta function to check if all the cells have a value

If Application.WorksheetFunction.CountA(Range("V9:V13 3")) < _
Range("V9:V133").Cells.Count Then MsgBox ""

You can use the beforeclose event to check the above and use Cancel = true
if not
all the cells have a value

Post back if you need help with that

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Greg Liber" wrote in message
...
First off I really need to thank Frank, Tom and Ron! While some of the
others here have also helped out, these three have really stood out when
answering my questions! You're help has been amazing! Your patience
with
the rest of us is very remarkable and much appreciated! Thanks for
taking
your time to help us out!

Now to the question...
I want to write a macro that checks column "V" (or range "V9:V133") on a
payroll worksheet and then if there is nothing in a cell, the macro
returns
a message asking if this is correct... and then allows the user to
change
the cell(s) and continues to check until the entire range has been
checked.
There might be instances of "0" hours so it's OK to have "0" but not a
blank
in the cell.

In the perfect world... the process would only check for the information
in
the range "V9:V133" that had information in range "A9:A133"

This is probably pretty easy but I'm not clear as to how to figure this
one
out... especially with the message box(es) that I would need to
create...

All this because I have users who forget to put in total hours for
employees
on a worksheet... ohh wellll

I'm not sure if this can even be done... but any suggestions would be
great!
Thanks in advance for any and all assistance!

Greg

create a "check" macro
 

Hi RON!

Sorry it was a long night and I kept working at both options until I sorta
understood them and got one of them to work!

Thanks for the help!

Greg
"Ron de Bruin" wrote in message
...
Hi Greg

I see you got it working with Bob's code

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Greg Liber" wrote in message
...
thanks for the info... but yup need some help with the rest of what you
said
to do... sorry I don't want to impose.

Greg

"Ron de Bruin" wrote in message
...
Hi Greg

You can use the Counta function to check if all the cells have a value

If Application.WorksheetFunction.CountA(Range("V9:V13 3")) < _
Range("V9:V133").Cells.Count Then MsgBox ""

You can use the beforeclose event to check the above and use Cancel =
true
if not
all the cells have a value

Post back if you need help with that

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Greg Liber" wrote in message
...
First off I really need to thank Frank, Tom and Ron! While some of
the
others here have also helped out, these three have really stood out
when
answering my questions! You're help has been amazing! Your
patience
with
the rest of us is very remarkable and much appreciated! Thanks for
taking
your time to help us out!

Now to the question...
I want to write a macro that checks column "V" (or range "V9:V133")
on a
payroll worksheet and then if there is nothing in a cell, the macro
returns
a message asking if this is correct... and then allows the user to
change
the cell(s) and continues to check until the entire range has been
checked.
There might be instances of "0" hours so it's OK to have "0" but not
a
blank
in the cell.

In the perfect world... the process would only check for the
information
in
the range "V9:V133" that had information in range "A9:A133"

This is probably pretty easy but I'm not clear as to how to figure
this
one
out... especially with the message box(es) that I would need to
create...

All this because I have users who forget to put in total hours for
employees
on a worksheet... ohh wellll

I'm not sure if this can even be done... but any suggestions would
be
great!
Thanks in advance for any and all assistance!

Greg

create a "check" macro
 

HI!

Thanks for the help1

Just one more thing on this topic... can I also check A9:A133 first then if
there is a number in there to run the check?

I know that Bob gave me an idea that did this but it doesn't enter in the
information from the message box to the cell missing the data...

Sorry to be such a pain...

Greg


"chandlm " wrote in message
...
Greg,

You can use the following to do what you want.

Sub check_values()
For Each cell In Range("v9", "v133")
If cell.Value = "" Then
If MsgBox("No Value in Cell" & cell.Address & " . Is this OK? ",
vbYesNo, "Check Value") = vbNo Then
cell.Value = InputBox("Please enter value for Cell" & cell.Address,
"New Value", 0)
End If
End If

Next

End Sub


HTH

Matt


Greg Liber wrote:
*Hi Bob!
Thanks for the help!
But, can't seem to get this to work... any ideas?

Greg

"Bob Phillips" wrote in message
...
Greg,

Dim ans
For i = 9 To 133
If Not IsEmpty(Cells(i, "A").Value) And IsEmpty(Cells(i,
"V"))
Then
ans = ""
Do While ans = ""
ans = InputBox("Cell " & Cells(i,
"V").Address(False,
False)
& " needs data, please supply", _
"Data Completion")

Loop
End If
Next i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Greg Liber" wrote in message
...
First off I really need to thank Frank, Tom and Ron! While some
of the
others here have also helped out, these three have really stood
out when
answering my questions! You're help has been amazing! Your
patience
with
the rest of us is very remarkable and much appreciated! Thanks
for
taking
your time to help us out!

Now to the question...
I want to write a macro that checks column "V" (or range
"V9:V133") on a
payroll worksheet and then if there is nothing in a cell, the
macro
returns
a message asking if this is correct... and then allows the user
to
change
the cell(s) and continues to check until the entire range has
been
checked.
There might be instances of "0" hours so it's OK to have "0" but
not a
blank
in the cell.

In the perfect world... the process would only check for the
information
in
the range "V9:V133" that had information in range "A9:A133"

This is probably pretty easy but I'm not clear as to how to
figure this
one
out... especially with the message box(es) that I would need
to
create...

All this because I have users who forget to put in total hours
for
employees
on a worksheet... ohh wellll

I'm not sure if this can even be done... but any suggestions
would be
great!
Thanks in advance for any and all assistance!

Greg



*


---
Message posted from http://www.ExcelForum.com/