create a "check" macro

  • Thread starter Thread starter Greg Liber
  • Start date Start date
G

Greg Liber

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
 
Hi Greg

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

If Application.WorksheetFunction.CountA(Range("V9:V133")) < _
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
 
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)
 
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
 
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)
 
Sorry Bob!

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

Thanks for your help!

TTFN
Greg
 
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
 
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
 
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
 
Back
Top