PDA

View Full Version : Using inputbox to locate a range, and using 2 different locations to insert amounts



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

Jan Karel Pieterse
09-19-2013, 05:01 AM
You need the intersect function:
Intersect(rngFound.EntireRow,rngFound2.EntireColumn).Value=Amt

Surfboard
09-19-2013, 05:22 AM
That could work as well, and from another forum (Thanks to Yudlugar from excelforum)



Sub macro_1()
Dim r, c
On Error GoTo not_found
r = WorksheetFunction.Match(Int(InputBox("Enter Account Number", "Alfa Test", "7982463285")), Range("B:B"), 0)
c = WorksheetFunction.Match(Int(Format(CDate("01/" & InputBox("Month:", "Alfa Test", "Jul-13")), 0)), Rows(9), 0)
Cells(r, c) = InputBox("Amount:", "Alfa Test", "250")
On Error GoTo 0
Exit Sub
not_found:
MsgBox "not found"
End Sub

we got a shorter version of the whole script, quite fascinating. Thank you for the reply :)