Subroutine to overwrite lines of data in a spreadsheet.

[LyntonSmith]

I have a spreadsheet which users populate by a userform. If they enter the same claim number, then clicking update will scroll through the list of claims in the spreadsheet and if it is present will overwrite the line of data, else it will append a new row to the bottom.

Part of the save/update code is here:

Else
With Sheet3
.Range("A1").Value = TextBox1
.Range("B1").Value = CDate(DateSerial(VBA.Right(TextBox4.Text, 4), VBA.Mid(TextBox4.Text, 4, 2), VBA.Left(TextBox4.Text, 2))): .Range("B1").NumberFormat = "DD/MM/YYYY"
.Range("C1").Value = TextBox3
.Range("D1").Value = ComboBox1
.Range("E1").Value = CDate(DateSerial(VBA.Right(TextBox5.Text, 4), VBA.Mid(TextBox5.Text, 4, 2), VBA.Left(TextBox5.Text, 2))): .Range("E1").NumberFormat = "DD/MM/YYYY"
.Range("G1").Value = TextBox8
.Range("H1").Value = TextBox9
End With
'transferring data across spreadsheets switched off
Search_Current_Claims TextBox1.Value

The last line calls the subroutine to check whether the claim is already on the spreadsheet and is here:


Public AROW
Sub Search_Current_Claims(CLMNO)
On Error GoTo NOTFOUND

Sheets("DATABASE").Select
Cells.Find(What:=CLMNO, After:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
'MsgBox ("Claim reference already exists")
AROW = ActiveCell.Row
'Sheets("Launch").Select
Exit Sub
NOTFOUND:

Range("A65536").End(xlUp).Select
AROW = ActiveCell.Row + 1
'Sheets("Launch").Select
End Sub


This works perfectly. I then tried to apply this to another worksheet again using a userform to populate a worksheet on a spreadsheet. Exactly the same principle, so copied the code ascross and amended the first part of code to Textbox24.Value

However, when testing this one it invariably writes to another line instead of overwriting. I think it is to do with what Excel is trying to find and the problem lies with the "What:=CLMNO" - I assume I have to define CLMNO.

Any help would be appreciated. I have been staring at it for ages and am going mad.



THanks


Lynton