Using command button in userform to update current row

[puffo]

Hi All,
I've been learning as I go, never played with vba before discovering that a userform might make my spreadsheet data entry easier. Now I'm obsessed..!

Problem is this:

Usually it's being used to enter new details into a spreadsheet into the first available blank row, but occasionally I need to retrieve details from sheet to userform based on the invoice number keyed into the userform, using the "CmdRetrieveDetails" button. This works a treat.

I then wish to update details in the sheet and use "CmdUpdateDetails" button to enter them back into the spreadsheet in the row from which they came, but instead they end up in the next blank row still... Can anyone shed any light?

Code for retrieval button and update details button is below:

Please Login or Register  to view this content.

***There are only 2 values in this list so far, as a test showed that they were still going into the next blank row so I didn't continue!**

There is a global command at the start, which came from the instruction video for this code

Dim currentrow As Long

**

Help please!!! Thanks! Let me know what you need from me...

[LJMetzger]

Hi puffo,

Welcome to ExcelForum. Next time you post code please use CODE TAGS. See the forum rules for instructions: http://www.excelforum.com/excel-prog...rum-rules.html

Your code keeps on going after your find the first occurrence of the 'Invoice Number'. You need to exit the loop after you process the 'Invoice Number'.

Try this (code change highlighted in red):

Please Login or Register  to view this content.

Lewis

[puffo]

LJMetzger you are a legend.. so many hours trying this and that, and so simple in the end! Thank you!

2 questions from here:

1. in the spirit of learning - the "For" starts before the "if", so in my mind logic dictates that the "if" should end first, and be contained inside the "for", but this does not work.. Why?! (If there is a shortish answer..!)

2. Is there an easier way to carry on from here and add alll the relevant fields (30ish) under the cmdUpdateDetails command button subroutine without defining each one as a string individually like in my existing code?
Thanks!

[LJMetzger]

in the spirit of learning - the "For" starts before the "if", so in my mind logic dictates that the "if" should end first, and be contained inside the "for"

You are absolutely correct. Think of the for..next and if..endif constructions as a for..next bracket and an if..endif bracket, and the rules state that the brackets can never cross (i.e. one always has to be inside the other). If you single step through the code (F8 in the debugger), you will see that if the 'exit for' is OUTSIDE if..endif (i.e. after the endif) that we will ALWAYS exit after the first invoice number is checked. If is inside the if..endif, so that when we find a MATCHING invoice number, we EXIT and don't process any more invoice numbers.

s there an easier way to carry on from here and add alll the relevant fields (30ish) under the cmdUpdateDetails command button subroutine without defining each one as a string individually

There may be an easier way, but I don't know of one. Your code is very easy to read and follow as is. I write my code in a manner similar to yours.

One difference is I usually don't like to reference column numbers for items directly in the code. Especially if the column numbers (or row numbers) appear in more than one place in the code. That way if the worksheet design changes, VBA changes to column numbers only have to be in ONE PLACE. I put all those definitions in a special (ordinary) code module I call 'ModConstants'. For example:

Module 'ModConstants':

Please Login or Register  to view this content.

Module 'Module1' or ThisWorkbook Module or a Sheet Module:

Please Login or Register  to view this content.

Lewis