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: Most helpful
Most helpful Newest Oldest
  1. OssieMac 48,006 Reputation points Volunteer Moderator
    2021-04-03T04:19:59+00:00

    In the Categories worksheets I have inserted N/A and merged the single blank row if no Category records exist.

    View for the templates in Macro workbook changed to "Page Layout". Therefore, the View in the created Output workbooks is now "Page Layout".

    In all workbooks, the first cell is selected in all worksheets and the first worksheet activated before saving.

    Link below to zipped file containing 3 workbooks.

    1. The older version of macro code without the Progress Bar,
    2. The newer version of macro with the Progress Bar
    3. The Source Workbook.

    Zipped Workbooks with updated code

    Was this answer helpful?

    0 comments
  2. Anonymous
    2021-04-02T15:31:42+00:00

    Currently, I am at the ActiveX controls and User forms in the VBA course as well.

    I learnt the ME syntax and unload. 

    In the form when I look at the code for each tab. 

    I see unload ME.

    ME is same as ThisWorkbook.Worksheet

    So when the user click on close (X) or finish. It hides for close and unload or directly unload if the user clicks Finish.

    Unload flow (clears the memory and terminates it).   

    list box looped through the code made as progress bar didn't know we could do that. 

    Only the N/A and merge part has to be done.

    Before N/A merge

    Image

    After N/A merge

    Image

    If I need to implement progress bar for Scenario 1. I can copy paste the code no changes needed. 

    I will go through the code and try to understand it.

    Was this answer helpful?

    0 comments
  3. OssieMac 48,006 Reputation points Volunteer Moderator
    2021-04-02T11:32:44+00:00

    OK. Latest versions. The zip file contains 2 versions of the Macro workbook plus the wsSource.xlsx.

    One of the Macro workbooks contains a progress bar and the other as it was previously. You previously indicated that you have a large number of records to process and I have found that the progress bar does wonders for the psychological well being  of a user when they can see that processing is taking place instead of watching the screen and wondering how long it it going to take or if it is even processing or stalled.

    Of course the Progress bar does add some time to the processing but I think that the effect on the users outweighs that. Also the percentage processed is not absolutely accurate because it is calculated for each of the Unique Numbers and there is a different amount of processing for each number but it gives the user some idea how it is progressing.

    Test and let me know if anymore fine tuning is required. If you like the Progress Bar and require more information on it then let me know and I will attempt to describe how it works and how you can export and import it to other projects. You will see the additional code for the progress bar is enclosed between top an bottom hash lines plus there is a small amount of code in the Userform Module.

    Link to project:  https://1drv.ms/u/s!ArAXPS2RpafCtHC6rx0_kwFvlhuh?e=i3GNCQ

    Was this answer helpful?

    0 comments
  4. OssieMac 48,006 Reputation points Volunteer Moderator
    2021-04-02T03:17:35+00:00

    Bit confusing. Who is HafizUsman4. Has the OP, ExcelFunTension changed username or using multiple usernames.

    Was this answer helpful?

    0 comments
  5. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more