For Next Loop Problem

[gyster]

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.

[Norie]

You've misspelled currentRow here.

Please Login or Register  to view this content.

PS To avoid this sort of thing you can add Option Explicit at the top of the module.

[Richard Buttrey]

Hi,

What is TXTInvoiceLookup.Text ?

Does this happen at the first attempted pass through the loop or later?

It would help if you were to upload the actual workbook and explain to us how you use it.

[bakerman2]

Please Login or Register  to view this content.

[gyster]

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!

[gyster]

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

[gyster]

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

[gyster]

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.

[Norie]

There's really no need to loop.

Try this.

Please Login or Register  to view this content.

[bakerman2]

@ gyster.
If Invoicenumber is unique in column A then you don't have to loop every value but you can do with this.

Please Login or Register  to view this content.

PS thanks for the rep.

@ Norie

When using With ... you have forgotten to use

Please Login or Register  to view this content.

otherwise it's still the active sheet where the search is.

Please Login or Register  to view this content.

[Norie]

Oops, I did miss the dots.

Not sure how it affects this though.

Please Login or Register  to view this content.

[gyster]

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

[Norie]

Guy

What type of data do you have in the first column? Text? Numeric? Alphanumeric?

[gyster]

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

[Norie]

I think it might be time to see a workbook.

Any chance you could upload a sample file?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[gyster]

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

[bakerman2]

A lot of your problems will be solved if you declare Invoicenumber correctly.
Since they are true numbers you can't declare them as String but as Double.
So codes become this.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[gyster]

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

Sincerely,
Guy

[bakerman2]

You're welcome and thanks for the rep.