VBA - InputBox Cancel/no results

Claxton

New Member
Joined
Oct 13, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi there,

I'm attempting to put together a macro for a small work project, I'm trying to create an input box that will allow the user to search for a reference within the 20th column and then copy and returned results to another worksheet.

This works perfectly for retrieving, copying, and pasting the data, the downside is if the user clicks "Cancel" or enters a value which isn't available in the 20th column it will continue to execute the macro (there is more to the macro than what's below, but this is my issue currently).

VBA Code:
    Sheets("Cosh Dump").Select
    ActiveSheet.Range("$A$1:$U$44415").AutoFilter 20, InputBox("Please enter Reference") & ""
    ActiveWindow.ScrollColumn = 2
    Range("A2:M50000").Select
    Range("M50000").Activate
    Selection.Copy
    Sheets("Workings").Select
    Range("A2").Select
    ActiveSheet.Paste

If anyone is able to assist that would be greatly appreciated.

Thank you,

Ben
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Try this...

VBA Code:
    Sheets("Cosh Dump").Select
    ActiveSheet.Range("$A$1:$U$44415").AutoFilter 20, InputBox("Please enter Reference") & ""
    ActiveWindow.ScrollColumn = 2
    If (ActiveSheet.AutoFilterMode)= False Then Exit Sub
    Range("A2:M50000").Select
    Range("M50000").Activate
    Selection.Copy
    Sheets("Workings").Select
    Range("A2").Select
    ActiveSheet.Paste
 
Upvote 0
Try this...

VBA Code:
    Sheets("Cosh Dump").Select
    ActiveSheet.Range("$A$1:$U$44415").AutoFilter 20, InputBox("Please enter Reference") & ""
    ActiveWindow.ScrollColumn = 2
    If (ActiveSheet.AutoFilterMode)= False Then Exit Sub
    Range("A2:M50000").Select
    Range("M50000").Activate
    Selection.Copy
    Sheets("Workings").Select
    Range("A2").Select
    ActiveSheet.Paste

Heya - thanks for your response, I gave this a go I was presented with this error message when there were no returned results.

1602602869774.png


After clicking "OK", it annoyingly proceeds to execute the rest of macro :D
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top