Still playing with the "Check" macro...
 

HI!
Sorry about reposting this but the last thread was so long I thought that
maybe it had been missed...

I need to check "A" and if there is data in it ensure that there is data in
"U" (can be "0" but needs to be filled in so no "" allowed)
The rows of data are 9 to 134

Bob gave me an idea that did this but it doesn't enter in the
Information entered into the message box into the cell missing the data...

And Chandlm gave me an idea that enters the data into the cell but doesn't
check "A" so the user would need to check all the rows without data in "U"

(both are below if that helps... not sure how to mesh them)

Sorry to be such a pain...

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

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)

Still playing with the "Check" macro...
 

Bob was doing 9 to 133 and looking at V, so perhaps you changed your spec.
Here is a modification.

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

--
Regards,
Tom Ogilvy

"Gbiwan" wrote in message
...
HI!
Sorry about reposting this but the last thread was so long I thought that
maybe it had been missed...

I need to check "A" and if there is data in it ensure that there is data
in
"U" (can be "0" but needs to be filled in so no "" allowed)
The rows of data are 9 to 134

Bob gave me an idea that did this but it doesn't enter in the
Information entered into the message box into the cell missing the data...

And Chandlm gave me an idea that enters the data into the cell but doesn't
check "A" so the user would need to check all the rows without data in "U"

(both are below if that helps... not sure how to mesh them)

Sorry to be such a pain...

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

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)

Still playing with the "Check" macro...
 

THANKS!

I'm not sure how but now my data seems to move and fill in the value in the
blank cells below the data are (any rows without data up to 134) Does this
make sense?

The data entered for the missing cell fills all the rows in that column
(9-134)

Any ideas?

Thanks for your patience!

Greg

"Tom Ogilvy" wrote in message
...
Bob was doing 9 to 133 and looking at V, so perhaps you changed your spec.
Here is a modification.

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

--
Regards,
Tom Ogilvy

"Gbiwan" wrote in message
...
HI!
Sorry about reposting this but the last thread was so long I thought
that
maybe it had been missed...

I need to check "A" and if there is data in it ensure that there is data
in
"U" (can be "0" but needs to be filled in so no "" allowed)
The rows of data are 9 to 134

Bob gave me an idea that did this but it doesn't enter in the
Information entered into the message box into the cell missing the
data...

And Chandlm gave me an idea that enters the data into the cell but
doesn't
check "A" so the user would need to check all the rows without data in
"U"

(both are below if that helps... not sure how to mesh them)

Sorry to be such a pain...

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

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)

Still playing with the "Check" macro...
 

HI!

I've narrowed down my problem to this statement

cells(i,"U").Value = ans

Which seems to be the hang-up... is there a way to change this to only put
in the value which was entered into the cell that needs the data and then
not into anything else that is blank in that column? (the rest of the info
is below if that helps...)

Thanks!
Greg

"Gbiwan" wrote in message
...
THANKS!

I'm not sure how but now my data seems to move and fill in the value in
the
blank cells below the data are (any rows without data up to 134) Does
this
make sense?

The data entered for the missing cell fills all the rows in that column
(9-134)

Any ideas?

Thanks for your patience!

Greg

"Tom Ogilvy" wrote in message
...
Bob was doing 9 to 133 and looking at V, so perhaps you changed your
spec.
Here is a modification.

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

--
Regards,
Tom Ogilvy

"Gbiwan" wrote in message
...
HI!
Sorry about reposting this but the last thread was so long I thought
that
maybe it had been missed...

I need to check "A" and if there is data in it ensure that there is
data
in
"U" (can be "0" but needs to be filled in so no "" allowed)
The rows of data are 9 to 134

Bob gave me an idea that did this but it doesn't enter in the
Information entered into the message box into the cell missing the
data...

And Chandlm gave me an idea that enters the data into the cell but
doesn't
check "A" so the user would need to check all the rows without data in
"U"

(both are below if that helps... not sure how to mesh them)

Sorry to be such a pain...

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

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)

Still playing with the "Check" macro...
 

I the Cells(i,"U") = ans was misplaced.

try this revision:

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

Cells(i, "U").Value = ans

End If

Next i

End Sub

--
Regards,
Tom Ogilvy

"Gbiwan" wrote in message
...
THANKS!

I'm not sure how but now my data seems to move and fill in the value in
the
blank cells below the data are (any rows without data up to 134) Does
this
make sense?

The data entered for the missing cell fills all the rows in that column
(9-134)

Any ideas?

Thanks for your patience!

Greg

"Tom Ogilvy" wrote in message
...
Bob was doing 9 to 133 and looking at V, so perhaps you changed your
spec.
Here is a modification.

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

--
Regards,
Tom Ogilvy

"Gbiwan" wrote in message
...
HI!
Sorry about reposting this but the last thread was so long I thought
that
maybe it had been missed...

I need to check "A" and if there is data in it ensure that there is
data
in
"U" (can be "0" but needs to be filled in so no "" allowed)
The rows of data are 9 to 134

Bob gave me an idea that did this but it doesn't enter in the
Information entered into the message box into the cell missing the
data...

And Chandlm gave me an idea that enters the data into the cell but
doesn't
check "A" so the user would need to check all the rows without data in
"U"

(both are below if that helps... not sure how to mesh them)

Sorry to be such a pain...

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

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)

Still playing with the "Check" macro...
 

THAT'S THE TICKET!

You always come through!

Thanks a TON!

Greg

"Tom Ogilvy" wrote in message
...
I the Cells(i,"U") = ans was misplaced.

try this revision:

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

Cells(i, "U").Value = ans

End If

Next i

End Sub

--
Regards,
Tom Ogilvy

"Gbiwan" wrote in message
...
THANKS!

I'm not sure how but now my data seems to move and fill in the value in
the
blank cells below the data are (any rows without data up to 134) Does
this
make sense?

The data entered for the missing cell fills all the rows in that column
(9-134)

Any ideas?

Thanks for your patience!

Greg

"Tom Ogilvy" wrote in message
...
Bob was doing 9 to 133 and looking at V, so perhaps you changed your
spec.
Here is a modification.

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

--
Regards,
Tom Ogilvy

"Gbiwan" wrote in message
...
HI!
Sorry about reposting this but the last thread was so long I thought
that
maybe it had been missed...

I need to check "A" and if there is data in it ensure that there is
data
in
"U" (can be "0" but needs to be filled in so no "" allowed)
The rows of data are 9 to 134

Bob gave me an idea that did this but it doesn't enter in the
Information entered into the message box into the cell missing the
data...

And Chandlm gave me an idea that enters the data into the cell but
doesn't
check "A" so the user would need to check all the rows without data
in
"U"

(both are below if that helps... not sure how to mesh them)

Sorry to be such a pain...

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

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)