For Next Loop Problem

Hi. I'm trying to use this code to update records in a worksheet using a user form. When I run this, I get a message saying Invalid Next Control Variable Reference and currentRow (after Next) is highlighted. I declare currentRow as Long globally. Can anyone steer me in the right direction. I did a crappy job of naming my controls, but I'm learning as I go.

Please Login or Register  to view this content.

Gosh...I missed that, and that fixed the problem. I'll google option explicit to learn about that.

Thank you Norie, and have a great weekend!

Hi Richard. TXTInvoiceLookup is the text box where the user enters the invoice number they want to find and possibly edit. Norie found the problem, so I want to thank you for taking time to look at this, but I don't want to waste your time by looking at the entire workbook...it seems to be working fine now.

Thank you again,
Guy

Thanks bakerman2 - you know as I was assigning all those variables it occurred to me that it seemed strange that I couldn't just use the value in the control. That's just the way I learned from Exceltrainingvideos.com. There is probably an application for that approach, but for me, this is much cleaner. Thank you very much! I'll plug this in and although the users will never know the difference, I will.

Thanks again,
Guy

This is a separate problem, but it is related to the search I received help for above. My form has a text box "TXTInvoiceLookup" that the user will input an invoice number to look up into. Then, the form will populate all controls with values from that invoice. Once the form is loaded with data, the user can edit and then save the record (the update from my first post). So, the user opens the form, types in an invoice number, clicks search, and if the invoice number is found, the form fills with data. Then they edit and click Update, and the new data is written into the worksheet. Everything seems to work ok as long as I provide a valid number. If I don't, nothing seems to happen, and instead, I'd like to have a message box alert the user to try a different number. I've tried a couple of things that didn't work, so here I am again...Here's the code for the search button: And sorry if I should have started a new thread, I thought it would be helpful to have ready access to the update code as well. And again, thank you in advance for taking your time to read this and fix my crappy code.

Please Login or Register  to view this content.

First, a quick thank you to Norie and bakerman2 for the help and lessons. Unfortunately neither solution is working just yet. bakerman2, if I understand the code this line

Please Login or Register  to view this content.

searches column 1 starting at the first row for the value the user enters in TXTInvoiceLookup. Then, if a match is found, it appears the next bit writes values to the worksheet. This is where I'm getting confused, and it's likely because I combined these two topics. I noticed your code is for the BtnUpdate sub. I've got the search function on a separate button so the user can look for an invoice first, then edit any values needed, and then use the update routine to write the records to the worksheet. Of course if I've misinterpreted what this does

Please Login or Register  to view this content.

then this doesn't make any sense. So when I run this, I get an error (Run Time Error 13 - Type Mismatch) on the fRow =line. The invoice numbers in column one start on row 4 and they are unique.

Norie, when I try the code you modified, I get the error message even if I enter a valid number. I'm not sure where to go from here. I think I understand what you've got there - apparently

Please Login or Register  to view this content.

searches all values in Inv History column 1 for the number stored in InvoiceNumber, and if a match isn't found, the error message is displayed, otherwise it populates the form with values from the row containing the value in InvoiceNumber. So, why no joy?

I cannot thank you all enough.

Guy

Hi Norie. It's all numbers and the cell is formatted as Number with 0 decimals.

Hi Norie. Sorry this is taking so much time. Here's the workbook.

That was exactly it! I very much appreciate the help you and Norie provided. Many thanks.

Sincerely,
Guy