VBA Coding to check range against input and edit specific cell corresponding to that entry

[sintek]

Hi All

I'm new to the Forum. I need assistance with VBA Coding for the following: I have also uploaded the file.
When clicking on "PAYMENTS" in Sheet 1, a userform opens up. If I type in the WO REF NO, info, for that specific client comes up. i.e. 1006
Now I want to enter amount of payment and date of payment and have that specific value be deducted from the Balance of that specific client on Sheet1. so if WO REF NO 1006 is entered, the below code would deduct the payment entered from that specific client . If, however, I chose a different WO REF NO, the code must search the criteria, find the client and deduct payment from that client.

Can anyone assist.

Code: Select all

Private Sub Payment_Process_Click()
Dim i As Integer
Dim cNum As Integer
Dim X As Integer
Dim nextrow As Range
i = Range("F3").Value
    Range("F3") = i - Reg7.Value
 
cNum = 8
Set nextrow = Sheet2.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
For X = 1 To cNum
nextrow = Me.Controls("Reg" & X).Value
Set nextrow = nextrow.Offset(0, 1)
Next
MsgBox "The data has been sent"
cNum = 8
For X = 1 To cNum
Me.Controls("Reg" & X).Value = ""
Set nextrow = nextrow.Offset(0, 1)
Next
End Sub

[pecoflyer]

Hi and welcome

before each reply/post box in the Macro Forum, there is in a text in red font :

Put CODE tags around your macro/vba code in a post. Easy way to do this: select the macro/vba in your post and click the CODE button above where you input the text.


Please do so.

While you are at it, please also read the forum rules

Thank you

[sintek]

Hi and thank you for assisting.
I have edited my message. Hope this is correct.

[sintek]

Hi There

Anyone available to assist. I am lost

[NoSparks]

The problem associated with the line
i= Range("F3").value
is that the 3 needs to be changed to the row where Reg1.value was found when loading the data to the form.
You could use a hidden text box on the form to hold that row number.

Code: Select all

.TextBox1 = Application.WorksheetFunction.Match(CLng(Me.Reg1), Sheet1.Range("A:A"), 0)

Writing to your Transaction Sheet, you need the nextrow variable established ahead of the loop and it should be the row to use so should be declared as long.

nextrow = Sheet2.Cells(Rows.Count, 2).End(xlUp).Offset(1,0).Row

Then within the loop everything is written to the same row and only control and column increment.
The control being "Reg" & X and the column X+1 because of the blank column A.
A code snippet would be something like this

Code: Select all

With Sheet2
    nextrow = Sheet2.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
    For X = 1 To 8  'each data textbox on form
        .Cells(nextrow, X + 1).Value = Me.Controls("Reg" & X).Value
    Next X
End With


[sintek]

Hi NoSparks

Thank you for your input. I am new to this and this is my first attempt at Forms and VBA Code, so it is pretty much Greek to me. Let's ignore the part of the code for Sheet2 and focus on the code for the payment execute.

Let me explain what I am trying to achieve:

If the value of my TextBox1 Input = Value of Cell A3 in worksheet1 Then
the value of Cell F3 must become Cell F3 - value of TextBox7
If the value of my TextBox1 Input = Value of Cell A4 in worksheet1 Then
the value of Cell F4 must become Cell F4 - value of TextBox7
If the value of my TextBox1 Input = Value of Cell A5 in worksheet1 Then
the value of Cell F5 must become Cell F5 - value of TextBox7
If the value of my TextBox1 Input = Value of Cell A6 in worksheet1 Then
the value of Cell F6must become Cell F6 - value of TextBox7

And so on until end of Column A

I truly hope you are able to assist with the VBA coding

[NoSparks]

Mind if I ask how you get the form to load data from row 5 or 9 ?

Here's your file back, with my suggestions implemented.
I've left the additional text box visible so you can see what line the form is loading data from and where data will be written back to the sheet.

[sintek]

Hi NoSparks

You are a genius to say the least.

Row 5 and 9 were duplicate no's so would not work. In my file this would not occur.

Thank you so much for your assistance. I will strive to learn as much as i possibly can.

[sintek]

Hi NoSparks

Wow, I've come a long way with the understanding of VBA coding. So so much to still learn. Am thankful for this forum.

My Spreadsheet works almost perfectly. I have only one issue. Think it might be with a string of code in workbook
macro :

Code: Select all

Private Sub Reg1_AfterUpdate()

Code: Select all

.TextBox1 = Application.WorksheetFunction.Match(CLng(Me.Reg1), Sheet1.Range("A:A"), 0)

A scenario as follows:

I enter info into WorkSheet("JOB INFO FORM")
This info populates all the other worksheets i.e. Worksheet 3 through 7.
On WorkSheet("Invoice") when i click on "SEND TO DATA" the info automatically populates my WorkSheet("Database")

All works till this point, however,

When I click on "PAYMENTS" in WorkSheet("Database") and enter the ref no which is in Cell("A3")
TextBox1 = 1 and not 3 as per the info in Cell("A3")

This does not let the rest of the code work.

Please be so kind and assist.

I have uploaded my file....Protected with no password.

[NoSparks]

As posted your DATABASE is empty.
I enter bogus numbers as REF NO then click PAYMENTS to bring up the form
any of the bogus numbers I enter return the correct line number.
Don't know why it wouldn't work for you.