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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Trixterz

Board Regular
Joined
Aug 15, 2019
Messages
59
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
 

Claxton

New Member
Joined
Oct 13, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,110
Messages
5,576,162
Members
412,702
Latest member
maggielrux
Top