VBA InputBox Issue

That's okay take your time.

In the meanwhile, I tried to address the code with the help of the recorder. But I was hoping, if I can use For Each, Next construct.

However, I face issue with all boarders. The template I use has conditional formatting so if I use Clear instead of Clear.Contents conditional formatting is gone.

Clear.contents leave the boarders and when I run the macro with a different input number now the boarders are beyond the last row.

Range("A4").Select

Selection.End(xlDown).Select

ActiveCell.Offset(0, 14).Range("A1").Select

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

Selection.FormulaR1C1 = "New York"

Range("A4").Select

Selection.End(xlDown).Select

ActiveCell.Offset(0, 15).Range("A1").Select

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

Selection.FormulaR1C1 = "=TODAY()"

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks \_

    :=False, Transpose:=False

Range("A4").Select

Selection.End(xlDown).Select

ActiveCell.Offset(0, 16).Range("A1").Select

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

Selection.FormulaR1C1 = "C"

Range("O3").Select

ActiveCell.FormulaR1C1 = "Location"

Range("P3").Select

ActiveCell.FormulaR1C1 = "Date"

Range("Q3").Select

Selection.FormulaR1C1 = "Result"

Range("A3").Select

PS. it is a space I get a value of 32 and I am aware of the notorious CHAR 160. I made a mistake, I input the wrong number that is from the B column instead of A column. Later realized that the table has a flaw while filtering.

@ExcelFunTensionCreated,

Firstly I am so happy that you have made the effort to understand the code. Your efforts demonstrate that you are the type of person who will become quite a proficient programmer in the not too distant future. Congratulations on your efforts.

However, I have other stuff on this morning (about 7:30am in my part of the world) but I will try to put together answers to your questions later today. I just want to let you know that I am not abandoning you.

I understood the code but need to test it to see whether it is fool proof.

I though of using COUNTA/Resize to check if the user inputs a invalid number. In such case, there would be no entries in the column. So COUNTA will be nothing/zero excluding the column heading.

But unfortunately, if no matches ,OK in the filter is disable therefore no empty rows will show up.

So I had to test your code. It worked flawless! The code also handles errors.

How does it switch between Source and Output workbooks without using windows activate. We set the workbook names so that does the trick?

Assigning string to a prompt is creative I did not know that.

Also DataBodyRange is good. I don't have to worry about offset anymore just input the column number cuz it will exclude the header anyway.

I have some general question and one additional requirement.

1.) What is a list object. I have checked it in the object browser ListObject is part of Worksheet and TableObject class. I don't see any syntax.

2.) Is vbCrLf same as vbNewLine? I guess it is.

3.) I see no variables being passed or sub being called. Function Validate is public. It jumps to function validate procedure to validate myInp while debugging.

In the code below:

What is rngToFind?

If rngToFind = Nothing means no match found is True Exit Do? if Fasle loop (since validate function is a Boolean)

In the Find arguments what is xlValues, xlWhole, xlByRows, xlNext is default I see in syntax MatchCase Fasle implies?

With rngToSearch

    Set rngToFind = .Find(What:=varToFind, \_           

            LookIn:=xlValues, \_               

            LookAt:=xlWhole, \_

            SearchOrder:=xlByRows, \_

            SearchDirection:=xlNext, \_

            MatchCase:=False)

    If Not rngToFind Is Nothing Then

        Validate = True

    Else

        Validate = False

    End If

End With 

Instead of selecting the range to be copied, I have used code to reference the required column of data in the table rather than select and end down etc.

code to reference is above with statement?

Lastly, After copying it in to wsOutput sheet (see screenshot)

I want to find the last row starting from A4 or N4 doesn't matter all have the same last row.

Then For each cell in O4, P4 and Q4 to last row in the sheet

I want to fill

O4 to last row with "Location" (New York will be hardcoded in the code)

P4 to last row with TodaysDate (06-Mar-2021)

Q4 to last row with C

and have all boarders only to the filled cells.

Test the following code and see if it does what you require. I have written it in a more professional way by assigning workbooks, worksheets and table to variables. When variables are created from the top down (ie, workbook, worksheet, table) then the table (or ListObject) contains all of the parent references so by then using the ListObject Variable, the code knows the workbook name and worksheet name to which the Table belongs.

After the User enters a value in the InputBox, the code searches for the value in the first column of the table and if found then when the filter is applied there must b at least one row of visible data. Function Validate is a User Defined Function (UDF) which returns True or False depending on whether the entered value is found in the first column of the Table. The range to search and the value to find are passed to the UDF with the arguments/parameters.

Instead of selecting the range to be copied, I have used code to reference the required column of data in the table rather than select and end down etc.

Note that the code uses all full references to workbooks, worksheets and ranges and because of this, it is not necessary to activate workbooks, worksheets etc. finally the code Application.Goto wsOutput.Range("A4") does activate the Output worksheet and range for the user.

Note where you need to edit the sheet name for the output. Your code did not have anything that identified it.

Feel free to get back to me if you require further clarification of what the code is doing.

Sub MCR()

Dim wbSource As Workbook

Dim wbOutput As Workbook

Dim wsOutput As Worksheet

Dim wsFull As Worksheet

Dim lstObj As ListObject

Dim strPrompt As String

Dim myInp As Variant

Set wbSource = Workbooks("ML.xlsx")             'Assign workbook to workbook variable

Set wsFull = wbSource.Worksheets("Full-View")   'Assign the worksheet to worksheet variable

Set lstObj = wsFull.ListObjects("Table1")   'Assign the Table (List Object) to a List Object variable

'Setting the variables as per the previous 3 lines of code, lstObj now contains the full

'infomation about the Table including the Workbook name, Worksheet name and Table name

Set wbOutput = Workbooks("MCR Macro.xlsm")

Set wsOutput = wbOutput.Worksheets("Sheet1")    'Edit "Sheet1" to the worksheet name for the output

'By using a variable for the prompt, the prompt can be altered if invalid input by User

strPrompt = "Enter the number" & vbCrLf \_

            & "Cancel to exit and terminate processing."

Do

    'By using default in following line if User errors in the Inlut then

    'User can see what was entered and correct it otherwise it will be blank

    myInp = Trim(VBA.Interaction.InputBox(Prompt:=strPrompt, Title:="MCR Macro", Default:=myInp))

    If myInp = "" Then  'If user cancels then myInp will be zero length string

        MsgBox "User cancelled. Processing terminated." 'Optional. Can delete this line and simply Exit

        Exit Sub

    End If

    'If at least one value found in the column to Filter then there will be some output.

    If Validate(lstObj.DataBodyRange.Columns(1), myInp) Then

        Exit Do 'Go to past Loop command if Valid Input

    Else

        'Change Input prompt with message re invalid input and loop back to InputBox

        strPrompt = "Invalid entry. Please edit the number" & vbCrLf \_

                    & "Cancel to exit and terminate processing."

    End If

Loop    'If Invalid Input will loop back to Do for User to edit Input.

lstObj.Range.AutoFilter Field:=1, Criteria1:=myInp

'DataBodyRange is range under the column headers and Column 8 is the same as offset 7 columns.

lstObj.DataBodyRange.Columns(8).SpecialCells(xlCellTypeVisible).Copy

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

    :=False, Transpose:=False

lstObj.AutoFilter.ShowAllData   'Optional: Cancel filtering on the Table

Application.CutCopyMode = False 'Optional: Clear copy from clipboard

Application.Goto wsOutput.Range("A4")   'Optional: To Activate the worksheet with Output

End Sub

Function Validate(rngToSearch As Range, varToFind As Variant) As Boolean

Dim rngToFind As Range

With rngToSearch

    Set rngToFind = .Find(What:=varToFind, \_

            LookIn:=xlValues, \_

            LookAt:=xlWhole, \_

            SearchOrder:=xlByRows, \_

            SearchDirection:=xlNext, \_

            MatchCase:=False)

    If Not rngToFind Is Nothing Then

        Validate = True

    Else

        Validate = False

    End If

End With

End Function

It is tables exactly if it throws an error maybe I can convert the error code into a msgbox debug.

But there is no error

It does not actually throw an error. It is simply an idiosyncrasy of tables because they do not always work as one would expect. However, I have some thoughts on it and I am reasonably sure that I can test if the user entered a valid option for the filter.

If the Input is not a valid (ie. the value does not exist in the column) I will code to advise the user that the entry is not valid and request to re-enter. I will also identify if the User Cancels and then the process is terminated.

PS. Did you identify if the apparent space is actually a space or other character and if another character, were you able to replace other character with a space.