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: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2021-03-30T09:32:44+00:00

    Exactly, if the row values are same from A:E just merge as one. This is applicable for Category_3.

    It is very much like the below code but I am worried what if same values are not next to each other. A2 =A1 logic works only if same values are next to each other only then we can merge cells. If not maybe we need to sort it in such a way everything is grouped together. In my case they are probably place next to each other.

            For Each rng In Selection

            If rng.Value = rng.Offset(1, 0).Value And rng.Value <> "" Then

            Range(rng, rng.Offset(1, 0)).Merge

            End If

            Next

    While wrapping the text in column G. It is only applicable for Category_1 instead of the whole column in the sheet.

    Category_2 doesn't need wrap text.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2021-03-30T10:03:00+00:00

    e)Columns O, P, and Q in the Output sheetwill always have the same values

    Location="New York"

    Date= current date

    Status= "C"

    You might don't need to have it in the code, the template can handle it (but it is up to you guys)

    I though of the same initially but O, P and Q needs to be dynamic have to adjust with the number of rows populated from the wsSource or as you were suggesting delete the extra rows data from the columns O, P and Q from the template as per the last row. 

    Category_1 to Category_4 needs to be in a single sheet appended after one after other with a row gap. I am not allowed to change the template format.

    I did not go through your code I saved your file.

    I still have to go through OssieMac code as well. 

    I also saw ByVal being used in Ossiemac code. I do want to use arrays and passing arguments in the code where applicable.

    Once I have the final code. I will spend time to understand it and clear my questions.

    Was this answer helpful?

    0 comments
  3. OssieMac 48,006 Reputation points Volunteer Moderator
    2021-03-30T11:16:53+00:00

    @ExcelFunTension,

    No problems with the changes you have requested. I will implement them all. It is nearing my bedtime tonight so it will be tomorrow before I continue with it.

    If not maybe we needto sort it in such a way everything is grouped together*.*Yes! I think that is a wise suggestion. I have the code written to perform the merging and I realized that it is necessary that the like rows be adjacent and I intended to ask you if it is OK to sort the data so you have answered my question before I asked it.

     It is my intention to continue working with you until you are satisfied with the end result. I rather like a challenge with a bit of complexity instead of the mundane simple question and answer.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2021-03-30T16:29:13+00:00

    Take your time.

    I also need time to understand the code because it is slightly different from Scenario_1 code.

    If I am unable to understand the logic I will let you know.

    Was this answer helpful?

    0 comments
  5. OssieMac 48,006 Reputation points Volunteer Moderator
    2021-04-02T01:37:55+00:00

    @ExcelFunTension,

    Just a progress update so you know I have not forgotten about you. As far as I have tested, the actual code is working (including the changes you requested re formatting etc),

    However, I want to tidy up a few things in it before sending to you. One of the things is that I want to do is to integrate  the wrapping of the text in column "W" of the source file with the main process so it is a single process from start to finish.

    I have had other things (unrelated to computers) taking precedence for the last couple of days but that should finish later today so I should be able to get back on it tomorrow.

    Was this answer helpful?

    0 comments