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.

 

[gallen]

If Record_Number < Last Then MsgBox [B][COLOR=#ff0000]([/COLOR][/B]"Record do not exist!"[COLOR=#ff0000][B])[/B][/COLOR]: Exit Sub

 

[TPortsmouth]

If Record_Number < Last Then MsgBox [B][COLOR=#ff0000]([/COLOR][/B]"Record do not exist!"[COLOR=#ff0000][B])[/B][/COLOR]: Exit Sub

Thanks for your reply, but even I changed it to blanket, it still doesn't work.

 

[gallen]

How doesn't it work? Are you getting an error?

 

[TPortsmouth]

How doesn't it work? Are you getting an error?

Like below situation using smaller then,

If Record_Number < Last Then MsgBox "Too Big!": Exit Sub

if the last cell in column A which contains value is A10, then "Last" = 10, however, no matter I enter 1 to 100, no message box appear.

However, if I change to lager then,

If Record_Number > Last Then MsgBox "Too Big!": Exit Sub

It always show the alert.

You can have a try to copy and paste the same code and have a try, and you will have the same result.

Thanks.

 

[gallen]

I don't have sample data. However this is confusing:

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

Last will be the row number of the last used row in Column A

lr will be the first blank row after Last

What is your thinking here?

I've commented your code how i see it:

Sub CopyRecord()'


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


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


Record_Number = InputBox("Which record you want to copy?") 'User input to copy a certain row


If Record_Number > Last Then MsgBox ("Record do not exist!"): Exit Sub 'Changed the sign to GREATER THAN from LESS THAN
' 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 'Copy Columns A:E of the row the user selected plus the row below it
    Range("A" & lr).Select 'Select next empty cell
    ActiveSheet.Paste 'paste to that cell
End Sub

Also note that the last 3 lines can be written on one line:

Range("A" & Record_Number & ":E" & Record_Number + 1).Copy Range("A" & lr)

 

[FormR]

no matter I enter 1 to 100, no message box appear.

Hi, the way you are using InputBox() means that it is returning text and text is always greater than a number.

One way around this would be to force it return a number by using:

Record_Number = Application.InputBox("Which record you want to copy?", Type:=1)

You'll probably also want to add some code to check if the user pressed the cancel button.

 

[gallen]

Didn't spot that I assumed 'Record_Number' was declared elsewhere as a number

 

[TPortsmouth]

Hello gallen,

Thanks for your feedback, I am using Last to check the maximum number of rows.
While using lr as the placeholder as the "Copied" row.

And it's working fine. Thanks for your inspiration, I've modified the code a little bid.

I don't have sample data. However this is confusing:

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

Last will be the row number of the last used row in Column A

lr will be the first blank row after Last

What is your thinking here?

I've commented your code how i see it:

Sub CopyRecord()'


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


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


Record_Number = InputBox("Which record you want to copy?") 'User input to copy a certain row


If Record_Number > Last Then MsgBox ("Record do not exist!"): Exit Sub 'Changed the sign to GREATER THAN from LESS THAN
' 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 'Copy Columns A:E of the row the user selected plus the row below it
    Range("A" & lr).Select 'Select next empty cell
    ActiveSheet.Paste 'paste to that cell
End Sub

Also note that the last 3 lines can be written on one line:

Range("A" & Record_Number & ":E" & Record_Number + 1).Copy Range("A" & lr)

 

[TPortsmouth]

Hi FormR,

Wow!, I didn't realized input box will always return as text until you told me so, big thank you.

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

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

Hi, the way you are using InputBox() means that it is returning text and text is always greater than a number.

One way around this would be to force it return a number by using:

Record_Number = Application.InputBox("Which record you want to copy?", Type:=1)

You'll probably also want to add some code to check if the user pressed the cancel button.