| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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) |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| when a "check box" is checked, a "result" to be shown in another c | Excel Discussion (Misc queries) | |||
| Check if cells contain the word "Thailand", return "TRUE" | Excel Worksheet Functions | |||
| create links to check boxes marked "good" fair"and "bad" | Excel Worksheet Functions | |||
| create a "check" macro | Excel Programming | |||
| Macro to "Stop Playing" wave files | Excel Programming | |||
Hybrid Mode
