Using command button in userform to update current row

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):

Private Sub CmdRetrieveDetails_Click()
  Dim lastRow
  Dim invoiceno As String
  lastRow = Sheets("Invoices").Range("A" & Rows.Count).End(xlUp).Row
  invoiceno = TxtInvoiceNo.Text
  For currentrow = 2 To lastRow
    If Cells(currentrow, 1).Text = invoiceno Then

      TxtInvoiceNo.Text = Cells(currentrow, 1).Text
      '...
      ChkPaidContractor.Value = Cells(currentrow, 66)

      Exit For

    End If
  Next currentrow
  TxtInvoiceNo.SetFocus
End Sub

Lewis

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!

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':

Option Explicit

Public Const nSheetInvoicesInvoiceNoCOLUMN = 1
Public Const nSheetInvoicesChkPaidContractorCOLUMN = 66

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

Private Sub CmdRetrieveDetails_Click()
  Dim lastRow
  Dim invoiceno As String
  lastRow = Sheets("Invoices").Range("A" & Rows.Count).End(xlUp).Row
  invoiceno = TxtInvoiceNo.Text
  For currentrow = 2 To lastRow
    If Cells(currentrow, 1).Text = invoiceno Then

      TxtInvoiceNo.Text = Cells(currentrow, nSheetInvoicesInvoiceNoCOLUMN).Text
      '...
      ChkPaidContractor.Value = Cells(currentrow, nSheetInvoicesChkPaidContractorCOLUMN)

      Exit For

    End If
  Next currentrow
  TxtInvoiceNo.SetFocus
End Sub

Lewis