Use Msgbox for data validation

[TPortsmouth]

Hello Excel VBA expert,

By the help of MrExcel expert's help, I've developed below VBA to copy a specific record to the last row within the same worksheet.

Sub CopyRecord()
'

lr = Range("A" & Rows.Count).End(xlUp).Row + 1
'lr is to determine the Last Row

Last = Range("A" & Rows.Count).End(xlUp).Row
'This is to to determine the last Row address


Record_Number = InputBox("Which record you want to copy?")

If Record_Number < Last Then MsgBox "Record do not exist!": Exit Sub
' This is to display error message if user entered a number larger than the existing record number.


Range("A" & Record_Number & ":E" & Record_Number + 1).Copy
    Range("A" & lr).Select
    ActiveSheet.Paste
End Sub

The general idea is when use click the Macro button, a message box appear and copy the 2 row data and paste and the bottom of the same data set.

This is working fine. However, I want to add a validation to Stop the Macro if user enter a record number which does not exist.

If Record_Number < Last Then MsgBox "Record do not exist!": Exit Sub

However, there is some problem of this statement, and it didn't work. Can you help me on pointing out which part caused the failure?

Thanks.

 

[TPortsmouth]

Hello,

I still have other issues on this project and started another thread:

Copy data range and insert the copied data by the next row

Please also have a look when you have time, thanks.

TPortsmouth

 

[FormR]

Is there some Help file which can show me the remaining of those "Type"?

Hi, see here:
Application.InputBox Method (Excel)

By the way, I've used below code as validation, seems like it's working fine, do you think if it's ok?

If Record_Number = "" Then MsgBox ("Cancelled by user"): Exit Sub
' this checks for Cancel

If Record_Number <= 0 Then MsgBox ("Invalid record number!"): Exit Sub
' this checks for 0 and negative number

For the cancel shouldn't you be testing that the Record_Number variable = False?