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

[sintek]

Hi NoSparks

Thanks for reply. Yes when entering bogus data into Database the code works.


However, problem is when I enter data into WorkSheet("JOB INFO FORM") to populate my other worksheets i.e. Worksheet 3 through 7. This is how my business is set up.

My WorkSheet("Invoice") is how I populate my Database and when i click on "SEND TO DATA" the info automatically populates my WorkSheet("Database").

So now that I've got my database populated, I want to transact a payment and that's when I 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")


Scenario:

I input a invoice no "1006" into "printer order" no in cell("G4") on WorkSheet3(Job Info form"). This info goes to Cell(F4") on WorkSheet5("Invoice"). When I press "Send To Data" the "1006" goes to WorkSheet1.("DATABASE").

Now when I When I click on "PAYMENTS" in WorkSheet("Database") and enter the ref no which is in Cell("A3") i.e 1006,
and enter a payment amount to process, TextBox1 = 1 and not 3 as per the info in Cell("A3") nand therefore the calculation to deduct from balance does not work.

Hope this makes sense.

[NoSparks]

If I unprotect your DATABASE sheet so that the SEND TO DATABASE button will actually write to the DATABASE sheet,
then follow your senerio exactly,
TextBox1 reads 3, which is correct.

[sintek]

Hi NoSparks

Thanks for assistance. Unfortunately, it still has no effect.

I have however changed this string of code:

Code: Select all

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

to

Code: Select all

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

and it works perfectly.

Thank you

[sintek]

Hi NoSparks

If I use a alphanumeric invoice no then my code does not work i.e If Me.Reg1 = A1234 and not 1234

Code: Select all

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

I am learning slowly but surely

[NoSparks]

Up 'til now your REF NO has been a number, now it isn't a number, it contains more than just numeric characters, it's now a string.

The MATCH function is for numbers.
The CLng was required in what to MATCH in order to coerce the textbox Reg1 value to a number rather than text.

To find the row containing the REF NO string I'd use the Range.Find method.

http://www.globaliconnect.com/excel/ind ... Itemid=475
https://msdn.microsoft.com/en-us/librar ... 39746.aspx

Would you like to see why what you thought was the wrong number was being displayed in Textbox1 ?

Using the attached file I've unprotected the DATABASE sheet, UNMERGED THE CELLS of row 1 and moved your shapes off to the side.
Go to your INVOICE sheet and click SEND TO DATABASE once, then view the DATABASE sheet to see what happens.

I suspect you'll want to change the Button2_Click code as that's not what you want.

Would also be beneficial to have a look at the Excel/VBA Golden Rules

[sintek]

Hi NoSparks

Thanks a mil for all your assistance. Will look into Range.Find method and Button2_Click code.