• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

InputBox looping questions

O

OleMiss2010

Member
So in my VBA, Range("B3") will hold the account number that is put into the input box. Range("C5") has a vlookup that will use the account number to lookup information. I want the input box to loop under two conditions: 1) if the input box is blank, or 2) if the vlookup does not find anything to put in Range("C5") for that account number. Under the second condition I want to give the user the option of keeping the account number that they originally typed if it is what they meant to type. This code seems to work as long as the account number is entered correctly the first time. If either condition comes into play then I get a debug message.


Any advice?

[pre]
Code: 
Do
Acct = Application.InputBox("What is the lead account in the relationship?")
Range("B3").Value = Acct
If IsError(Range("C5").Value) Then
Do
MsgBox "Is this account correct? " & Acct, vbYesNo
If MsgBox("Is this account correct? " & Acct) = vbNo Then
Do
Acct = Application.InputBox("What is the lead account in the relationship?")
Loop While Acct = ""
Else
Range("C5").Values.Errors.Ignore
End If
Loop Until MsgBox("Is this account correct? " & Acct) = vbYes
End If
Loop While Acct = ""
[/pre]
 
Far too many loops and MsgBoxs

[pre]
Code: 
Do
Acct = Application.InputBox("What is the lead account in the relationship?")
Range("B3").Value = Acct
If IsError(Range("C5").Value) Then
ans = MsgBox("Is this account correct? " & Acct, vbYesNo)
If ans = vbYes Then
Range("C5").Values.Errors.Ignore
End If
Else
ans = vbYes
End If
Loop While Acct = "" Or ans = vbNo
[/pre]
 
Still getting an error for

Code: 
Range("C5").Values.Errors.Ignore

when the account number creates an error in C5 but I want to keep that account number.


Otherwise it works fine.
 
Perhaps this is more obvious within the complete macro, but what was the intent behind that line of code? Should a new value be in that cell, or is some other part of the macro having problems with the #N/A error?
 
Luke M,

I'm assuming you're asking about cell C5. It has a vlookup formula that should almost always have a value if the account number is correct. So if the value in C5 is an error, that is a clue that there was a typo in the account number. It is possible, though unlikely, that another account number would be used though; thus I want to give the user an override option.
 
Correct, I was talking about C5. What I meant was, why does the macro need to do anything with C5? Currently, the code checks C5, then reconfirms with user. If user agrees they input the right number, then....? Does C5 need to be changed to match the user's input, or something else?
 
If the user confirms their original input, then C5 should remain as an error (there will be an opportunity to fill in the data manually after the macro runs).
 
Then we don't need to do anything with in in the code, I believe.

[pre]
Code: 
Do
Acct = Application.InputBox("What is the lead account in the relationship?")
Range("B3").Value = Acct
If IsError(Range("C5").Value) Then
ans = MsgBox("Is this account correct? " & Acct, vbYesNo)
Else
ans = vbYes
End If
Loop While Acct = "" Or ans = vbNo
[/pre]
 
You must log in or register to reply here.
Back
Top