VBA Formula Problem, Someone please Help

Question

I have been going crazy trying to make a VBA code to do the following - I will explain it the best I can.

I have 2 Sheets, one named Pay_balance and the other Debtor_list. The Debtor_list Sheet is a Table from Cells A2:B13; A1 & B1 are Headers ("Debtors" and "Balance"). Both columns have names assigned ("Alldebtor" - A2:A13 & "Allbalance" B2:B13). On the Pay_balance sheet I have a drop-down list using Data Validation (Source) "Alldebtors" on F18 A Cell to input a Value on G18 And the current Balance of the "Alldebtors" Customer selected on F18 in Cell H18 using the formula below:

=VLOOKUP(F18,Debtor_list!A2:B13,2,FALSE)

I then have a pay Button using the VBA Code;

Sub Pay_Click()

    mycount = Range("G18") + Range("H18") Range("a1") = mycount

    MsgBox "You have just Credited $" & Range("G18") & vbCrLf & "Your Account Balance is now: " & mycount

    Application.Goto Reference:="Creditbox" 
    Selection.ClearContents 

    Application.Goto Reference:="Balance_Debtor" 
    Selection.ClearContents 

    Sheets("Menu").Select 
End Sub

The result of G18 + H18 is "mycount" it currently pastes the result in A1 of the "Pay_balance" Sheet and displays a MSG Box, what I need is for it to replace the value returned by the VLOOKUP Formula on the "Debtor_list" Sheet being the Customers new Balance, so when I return to the "Pay_balance" sheet select the persons name from F18 it displays their new Balance being the "mycount" in H18.

I hope I have made this clear enough and someone can help me, thank you.

Answer 1

Sub Pay_Click() 

    Name = Worksheets("pay_balance").Range("F18").Value 
    Amount = CSng(Worksheets("pay_balance").Range("G18").Value) 

    If Name = "" Then 
        MsgBox "Select Debtor" 
        Exit Sub 
    End If 

    DebtorRow = 1 
    Do 
        TempName = Worksheets("Debtor_list").Range("A" & DebtorRow).Value 
        If TempName = Name Then 
            DebtorBalance = CSng(Worksheets("Debtor_List").Range("B" & DebtorRow).Value) 
            Exit Do 
        End If 
        DebtorRow = DebtorRow + 1 
    Loop Until TempName = "" 

    If TempName = "" Then 
        MsgBox "Debtor not found" 
        Exit Sub 
    End If 


    Worksheets("Debtor_List").Range("B" & DebtorRow).Value = DebtorBalance + Amount 

    MsgBox "You have just Credited $" & Range("G18") & vbCrLf & "Your Account Balance is now: " & Range("H18") 

    Application.Goto Reference:="Creditbox" 
    Selection.ClearContents 

    Application.Goto Reference:="Balance_Debtor" 
    Selection.ClearContents 

    Sheets("Menu").Select 

End Sub