Surfboard
09-19-2013, 03:32 AM
Hello there
I am currently struggling with a script i am trying to create. I have account numbers that get paid each month, and i have created an input box where one can type in the account number, it will locate it and give you the row number, then another will ask you for the month, and give you the column number. Now that part is working perfectly, but i would like to add the next step where it uses the Row Number from the account number and the Column number from the Month, with the use of an input box, it would place the amount typed, within the range it generated. For example: Account number 7894561230 is located in row 67, and July is located in Column 3. Then the next input box should place the Amount in Column 3, Row 67.
Private Sub CommandButton1_Click()
Dim Acc As String
Dim Accnum, Inp, Inp2, Amt, Ins
Dim rngSearch As Range, rngFound As Range
Dim rngSearch2 As Range, rngFound2 As Range
Dim Msg As String, Ans As String
Accnum = InputBox("Enter Account Number", "Alfa Test", "7982463285")
Acc = Accnum
Set rngSearch = Range("B:B")
Set rngFound = rngSearch.Find(What:=Accnum, LookIn:=xlValues, LookAt:=xlPart)
If rngFound Is Nothing Then
MsgBox "Not found"
Else
Msg = "Column " & rngSearch.Column & ". Row " & rngFound.Row
Ans = MsgBox(Msg, vbOKOnly, "Alfa test")
If Ans = vbOK Then Inp = InputBox("Month:", "Alfa Test", "Jul-13")
Inp2 = Inp
Set rngSearch2 = Range("C:N")
Set rngFound2 = rngSearch2.Find(What:=Inp2, LookIn:=xlValues, LookAt:=xlPart)
If rngFound2 Is Nothing Then
MsgBox "Not found"
Else
Amt = InputBox("Amount:", "Alfa Test", "250")
Ins = Amt
Msg = "Column " & rngSearch2 & " Row " & rngFound
Ans = MsgBox(Msg, vbOKOnly, "Test")
If Ans = vbOK Then Range(rngSearch2 & rngFound) = Ins
End If
End If
End Sub
I am currently struggling with a script i am trying to create. I have account numbers that get paid each month, and i have created an input box where one can type in the account number, it will locate it and give you the row number, then another will ask you for the month, and give you the column number. Now that part is working perfectly, but i would like to add the next step where it uses the Row Number from the account number and the Column number from the Month, with the use of an input box, it would place the amount typed, within the range it generated. For example: Account number 7894561230 is located in row 67, and July is located in Column 3. Then the next input box should place the Amount in Column 3, Row 67.
Private Sub CommandButton1_Click()
Dim Acc As String
Dim Accnum, Inp, Inp2, Amt, Ins
Dim rngSearch As Range, rngFound As Range
Dim rngSearch2 As Range, rngFound2 As Range
Dim Msg As String, Ans As String
Accnum = InputBox("Enter Account Number", "Alfa Test", "7982463285")
Acc = Accnum
Set rngSearch = Range("B:B")
Set rngFound = rngSearch.Find(What:=Accnum, LookIn:=xlValues, LookAt:=xlPart)
If rngFound Is Nothing Then
MsgBox "Not found"
Else
Msg = "Column " & rngSearch.Column & ". Row " & rngFound.Row
Ans = MsgBox(Msg, vbOKOnly, "Alfa test")
If Ans = vbOK Then Inp = InputBox("Month:", "Alfa Test", "Jul-13")
Inp2 = Inp
Set rngSearch2 = Range("C:N")
Set rngFound2 = rngSearch2.Find(What:=Inp2, LookIn:=xlValues, LookAt:=xlPart)
If rngFound2 Is Nothing Then
MsgBox "Not found"
Else
Amt = InputBox("Amount:", "Alfa Test", "250")
Ins = Amt
Msg = "Column " & rngSearch2 & " Row " & rngFound
Ans = MsgBox(Msg, vbOKOnly, "Test")
If Ans = vbOK Then Range(rngSearch2 & rngFound) = Ins
End If
End If
End Sub