I have information in columns "C" & "D" and I want to loop through each row until there are no numbers left in those columns, and have a message box pop up for each individual cell and ask the question, "Is this the right number in cell 1?" & then "Is this the right number in cell 2?" If it is, then it goes to the next row down and keeps asking the same questions until there are no numbers left in the two columns. If the number isn't right, then the user can type in the correct number into a different message box and the cell "content" will be changed. Thanks in advance!!!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Looping through columns for information.
- Thread starter gjsala
- Start date
SkipVought
Programmer
Hi,
Here's a way...
Hope this helps 
Skip,
Skip@TheOfficeExperts.com
Here's a way...
Code:
Dim r As Range, rng As Range, lLastRow As Long, ans As Variant
lLastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row
Set rng = Range(Cells(1, "C"), Cells(lLastRow, "D"))
For Each r In rng
With r
.Select
ans = 99
Do While (ans <> "")
ans = InputBox("Is " & .Value & " the value correct value in cell " & .Address & "?", vbYesNo)
If ans <> "" Then
.Value = ans
End If
Loop
End With
NextSkip,
Skip@TheOfficeExperts.com
Hi gjsala,
I didn't check Skip's code. It's almost always right the first time, so there's no need. But, I do want to ask you, "Why"?
How long would you use Excel, if Excel did the same thing that you want to subject your end users to?
Imagine typing in Skip's code:
Do you really want r to be a Range?
You click Yes
Do you really want rng to be a Range?
You click Yes
Do you really want lLastRow to be a Long?
You click Yes
Do you really want ans to be a Variant?
You would click Yes again, but you slammed you're fist down so hard on the mouse after the last question that it no longer works.
Fun, huh?
How do you think the end users feel that have to use this code? How long are they going to continue using this spreadsheet as a solution to whatever problem it was intended to alieviate?
You probably have a perfectly good reason to force the users through this. But, is it going to work?
If you didn't get as frustrated as I would have, at some point you would quit examining what you had previously typed in, and would just click the Yes button everytime the message popped up. My breaking point would be 3 or 4 times, because I could see what's coming. The end users may be 10, 20, 30 or more (the firsttime they use it). What's their breaking point after 10 times of using the spreadsheet (if they use it that many times)? Probably zero.
My point is, you need to code for any answers that seem out of bounds, and throw up the question at that point, or, simply refuse their entry as an answer, and let them know what is an exceptable answer.
I'll deny in court that I said this but, at some point you have to trust the end-user.
I didn't check Skip's code. It's almost always right the first time, so there's no need. But, I do want to ask you, "Why"?
How long would you use Excel, if Excel did the same thing that you want to subject your end users to?
Imagine typing in Skip's code:
Code:
Dim r As Range, rng As Range, lLastRow As Long, ans As VariantYou click Yes
Do you really want rng to be a Range?
You click Yes
Do you really want lLastRow to be a Long?
You click Yes
Do you really want ans to be a Variant?
You would click Yes again, but you slammed you're fist down so hard on the mouse after the last question that it no longer works.
Fun, huh?
How do you think the end users feel that have to use this code? How long are they going to continue using this spreadsheet as a solution to whatever problem it was intended to alieviate?
You probably have a perfectly good reason to force the users through this. But, is it going to work?
If you didn't get as frustrated as I would have, at some point you would quit examining what you had previously typed in, and would just click the Yes button everytime the message popped up. My breaking point would be 3 or 4 times, because I could see what's coming. The end users may be 10, 20, 30 or more (the firsttime they use it). What's their breaking point after 10 times of using the spreadsheet (if they use it that many times)? Probably zero.
My point is, you need to code for any answers that seem out of bounds, and throw up the question at that point, or, simply refuse their entry as an answer, and let them know what is an exceptable answer.
I'll deny in court that I said this but, at some point you have to trust the end-user.
sfvb: Good Point.
gjsala: What sfvb is saying is rather than asking your users "Is this the right number in cell 1?" & then "Is this the right number in cell 2?", come up with some criteria that you can test the information against "in code." Then, if the information does not fit into the criteria, THEN prompt the user to replace the value (just be sure to test their input also).
For Example:
If C is a Job Number and D is a Job Name then check if C is an integer and that D is a string...
I C and D should be related in some way, test the relationship in your code...
It may mean more work for you upfront, especially if you are not fluent with VBA, but man-o-man will it ever make a difference to your users!! It could vey well mean the difference between your "solution" being used or not at all.
********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!
gjsala: What sfvb is saying is rather than asking your users "Is this the right number in cell 1?" & then "Is this the right number in cell 2?", come up with some criteria that you can test the information against "in code." Then, if the information does not fit into the criteria, THEN prompt the user to replace the value (just be sure to test their input also
).For Example:
If C is a Job Number and D is a Job Name then check if C is an integer and that D is a string...
I C and D should be related in some way, test the relationship in your code...
It may mean more work for you upfront, especially if you are not fluent with VBA, but man-o-man will it ever make a difference to your users!! It could vey well mean the difference between your "solution" being used or not at all.
********************
What's the best way to get the answers you need?? See FAQ222-2244 for details!
- Thread starter
- #6
SkipVought,
Thanks for your help, because everyone else in here has solutions. I have another question and maybe I can change the wording. Instead of what was said earlier, I would like the user to be able to select a cell and then change number in that cell instead of checking every cell in column "C" & "D". I have tried with the following code, but it doesn't change the cell that was selected. Do you have any other ideas or changes? Thanks.
CODE...
ans = Application.InputBox("Type in the cell you wish to change for example C2.", Cell)
ActiveCell = ans
inp = InputBox("Type in the new number.", Cell)
inp = ActiveCell
CODE...
Thanks for your help, because everyone else in here has solutions. I have another question and maybe I can change the wording. Instead of what was said earlier, I would like the user to be able to select a cell and then change number in that cell instead of checking every cell in column "C" & "D". I have tried with the following code, but it doesn't change the cell that was selected. Do you have any other ideas or changes? Thanks.
CODE...
ans = Application.InputBox("Type in the cell you wish to change for example C2.", Cell)
ActiveCell = ans
inp = InputBox("Type in the new number.", Cell)
inp = ActiveCell
CODE...
SkipVought
Programmer
Well, why not just let the user go to that cell and change it directly -- not need for a program to get in the way of doing that!
It's much easier to navigate to the place you want to change and then just change it.
The comments made above are very correct. Don't make this more difficult.
Skip,
Skip@TheOfficeExperts.com
It's much easier to navigate to the place you want to change and then just change it.
The comments made above are very correct. Don't make this more difficult.
Skip,
Skip@TheOfficeExperts.com
- Thread starter
- #8
SkipVought,
The reason I'm asking is because this in the only part in my program that's not working. I have the first part gathering all of the information, then comes this part which I'm having difficulty. Finally after the changes are made by the user, then the data is configured to end the program.
The reason I'm asking is because this in the only part in my program that's not working. I have the first part gathering all of the information, then comes this part which I'm having difficulty. Finally after the changes are made by the user, then the data is configured to end the program.
SkipVought
Programmer
Code:
ans = InputBox("Type the cell you wish to change for example C2.", Cell)
inp = InputBox("Type in the new number.", Cell)
range(ans).value = inpSkip,
Skip@TheOfficeExperts.com
- Thread starter
- #10
- Status
Similar threads
- Locked
- Question
- Locked
- Solved
- Locked
- Question
- Locked
- Question