2nd variable needed and can't think of anything to use to do it... T-T

Hi again,
i'm starting to adore this website :)

Ok, i have a VBA coding that finds an entry, saves it, input another value at that same place and then inputs the saved value in the first empty cell in Column A (2 other values ar esaved and entred in B and C). Then a MessageBox opens and I want it to say that the saved entry was inputed at a certain value (cell value in Column D of the line of the first empty cell - iRow and that's sorted) but here's the problem: then I also want it to say that the new entry (which replaced the saved value after having found it in the database) was inputed at a certain value (cell value in Column D of the line of the original value that was looked for using VBA coding at the beggining)

Complex to explain but tried it as good as I could...

teh problem lies at the end of the coding.

i tried to put a x = SelectionRow after the search string but that doesn't work. it gives me the value of the cell in column D of the row selected when opening Excel....

250 points for this one...

Here's the code:
--------------------------------------------------------
Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Database")

'check for a Reference number
If Trim(Me.RefReplaceNew.Value) = "POBF" Then
  Me.RefReplaceNew.SetFocus
  MsgBox "Please enter a Reference number for the new job"
  Exit Sub
End If

'check for a Title
If Trim(Me.TitleReplaceNew.Value) = "" Then
  Me.TitleReplaceNew.SetFocus
  MsgBox "Please enter a Title"
  Exit Sub
End If

'check for a Category
If Trim(Me.CategoryReplaceNew.Value) = "" Then
  Me.CategoryReplaceNew.SetFocus
  MsgBox "Please enter a Category"
  Exit Sub
End If

'check for a Reference number
If Trim(Me.RefReplaceOld.Value) = "POBF" Then
  Me.RefReplaceOld.SetFocus
  MsgBox "Please enter a Reference number for the old job"
  Exit Sub
End If

'Find old job
Set cel = Range("A:A").Find(RefReplaceOld, LookIn:=xlValues, LookAt:=xlWhole)
If Not cel Is Nothing Then
    With cel
        RefReplaceSave = .Value
        TitleReplaceSave = .Offset(0, 1).Value
        CategoryReplaceSave = .Offset(0, 2).Value

        .Value = RefReplaceNew
        .Offset(0, 1).Value = TitleReplaceNew
        .Offset(0, 2).Value = CategoryReplaceNew
    'find  first empty row in database
        iRow = FirstEmptyRowInA(ws)
    'copy the data to the database
        ws.Cells(iRow, 1).Value = RefReplaceSave
        ws.Cells(iRow, 2).Value = TitleReplaceSave
        ws.Cells(iRow, 3).Value = CategoryReplaceSave
    'Autofit Column B
        Columns("B:B").EntireColumn.AutoFit
    'Autofit Column C
        Columns("C:C").EntireColumn.AutoFit
    'clear the data
        Me.RefReplaceNew.Value = "POBF"
        Me.TitleReplaceNew.Value = ""
        Me.CategoryReplaceNew.Value = ""
        Me.RefReplaceOld.Value = "POBF"
    'Message box
    MsgBox "The number assigned to the New entry is: " & Sheets("Database").Range("D" & x) & " and the old entry is now in batch: " & Sheets("Database").Range("D" & iRow)
    End With
Else
    MsgBox "Reference # " & RefReplaceOld & " not found!", vbInformation
End If


End Sub

Private Sub CommandButton2_Click()
'Close that form

Me.Hide
frmHome.Show
End Sub

Function FirstEmptyRowInA(wks As Worksheet)
   With wks
      If Len(.Cells(1, "A").Formula) = 0 Then
         FirstEmptyRowInA = 1
      ElseIf Len(.Cells(2, "A").Formula) = 0 Then
         FirstEmptyRowInA = 2
      Else
         FirstEmptyRowInA = .Cells(1, "A").End(xlDown).Row + 1
      End If
   End With
End FunctionStart Free Trial