VBA InputBox Issue

Anonymous
2021-03-05T18:47:39+00:00

I have a simple macro. To copy values from one workbook to another in this case ML to MCR based on the user input number.

Number format can be e.g M5888-9087, M1990A 600111512, 453564034421 822349224.

Macro works with numbers like M5888-9087 but if I enter M1990A 600111512 or 453564034421 822349224 it copies all the rows instead of filtering M1990A 600111512 or 453564034421 822349224 and copying those selected rows.

Space in between is the culprit. How to overcome this?

Does changing the filter helps? I see other filters in object library.

Sub MCR()

Dim myInp As Variant

myInp = VBA.Interaction.InputBox(Prompt:="Enter the number", Title:="MCR Macro")

Windows("ML.xlsx").Activate

Worksheets("Full-View").Select

Worksheets("Full-View").ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=myInp

Range("Table1[[#Headers],[Serial]]").Select

ActiveCell.Offset(1, 7).Range("Table1[[#Headers],[Serial]]").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.Copy

Windows("MCR Macro.xlsm").Activate

Range("A4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

End Sub

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

99 answers

Sort by: Newest
Most helpful Newest Oldest
  1. OssieMac 48,006 Reputation points Volunteer Moderator
    2021-03-05T22:17:22+00:00

    Okay, my other concern was when the user inputs the number he might copy a trailing space with the number then the number won't show up in the filter.

    e.g M5888-9087_ (trailing space denoted as _)

    How to handle such trailing spaces with error handling.

    Use the following line and it will remove any leading and trailing spaces.

    myInp = Trim(VBA.Interaction.InputBox(Prompt:="Enter the number", Title:="MCR Macro"))

    In my previous post I omitted to explain why all data is output when no data is displayed in the table. Unfortunately with tables, instead of producing an error, you select the first hidden cell and then down and it actually selects the entire column of data in the table ( the end down goes to bottom of table) and because there is no mix of visible and non visible then it returns the entire column of data.

    Tables do some strange things that do not occur on worksheets without tables.

    Later today I will see if I can modify your code to test for existence of the entered value and also test that visible data is displayed.

    Was this answer helpful?

    0 comments
  2. OssieMac 48,006 Reputation points Volunteer Moderator
    2021-03-05T21:39:30+00:00

    Insert Exit Sub immediately after the AutoFilter line and run the code then check if the filter has worked or if the entire DataBody range is hidden.

    If entire DataBodyRange is hidden then what appears to be a space is not a space character.

    I suspect that the values have been downloaded/imported from elsewhere and that what appears to be a space is really another non printable character like CHAR 160.

    Test the character as follows.

    Select All in the filter and display all of the data.

    Select a blank cell out to the right of your table

    Enter the following formula where A2 is the cell containing the string with apparent space, 7 is the count of characters to the apparent space and 1 is to return for one character only.

    =CODE(MID(A2,7,1))

    If the result is 32 then it is a space but any other character then is not a space. I am guessing it will be 160.

    I suggest that you make a backup copy of your workbook before performing the following procedure in case you make an error and it does not turn out as expected.

    If it is not a space (code 32) then perform the following operation on your data

    1. Select the column of data
    2. On Home ribbon select "Find & Select" -> "Replace"
    3. Click in the "Find what" field.
    4. Hold the Alt key and then on the number key pad enter a 4 digit number like 0160 and release. (Enter whatever number the formula returned but must be 4 digits with leading zeros to pad out to 4 digits). Must use the keypad; cannot use the number keys at top of keyboard.
    5. In the "Replace with" field enter one space.
    6. Click Replace all button.

    Retest you code and see if fixed.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2021-03-05T21:37:36+00:00

    Okay, my other concern was when the user inputs the number he might copy a trailing space with the number then the number won't show up in the filter.

    e.g M5888-9087_ (trailing space denoted as _)

    How to handle such trailing spaces with error handling.

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2021-03-05T20:38:28+00:00

    You could use the dropdown filter list on your table column to choose the values of interest, and use a macro just to copy the values to the other workbook. Or you could present a list of unique values in a combobox, and choosing the value from the combobox should remove your matching issue.

    Was this answer helpful?

    0 comments