VBA InputBox Issue

A lot of bedtime reading for you. Hope I have explained everything OK and not made it too boring but please get back to me if you require any more explanation.

Q1. I thought 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.

A1. Perfectly valid method. In the code example below I have commented out the code I used and inserted the COUNTA method. Note the syntax where VBA needs to be told it is a WorksheetFunction and I used VBA ListObject variables for the range to count and input variable for the value to count. Have included 2 methods of syntax (one commented out) using the ListObject. One uses column number and the other uses the column header name.

Q2. How does it switch between Source and Output workbooks without using windows activate

A2. Generally it is not necessary to activate any workbooks, worksheets, ranges etc if the code fully references the desired location with the workbook name, sheet name and range etc. By assigning a workbook to a workbook variable then assign the worksheet to a worksheet variable by nominating both the workbook and worksheet then assigning a range to a range variable from the worksheet variable. If the objects are properly assigned in order then the last variable which is usually the range contains the full reference to workbook, worksheet and range.

Copy the following code into a blank new workbook and run the code. (to run code from within the VBA editor just click anywhere in the sub and press F5). You will see in the MsgBox that rng.Address returns the complete reference including the workbook, sheet and range. This is a great advantage of using object variables.

Sub VariableExample()

Dim wb As Workbook

Dim ws As Worksheet

Dim rng As Range

Set wb = ThisWorkbook   ***'ThisWorkbook is the workbook containing this VBA code***

'Set wb = Workbooks("My Workbook")   ***'Alternative syntax. "My Workbook" can be ThisWorkbook or a different Workbook.***

Set ws = wb.Worksheets("Sheet1")

Set rng = ws.Range("A1:C30")

MsgBox rng.Address(External:=True)  ***'Returns the full reference to workbook, sheet and range***

Worksheets("Sheet3").Activate   'NOT the sheet with the assigned rng

rng.Value = "My Test"   'This will output to Sheet1 event though Sheet3 is the Active sheet (NOTE: it populates the entire range with the same value)

End Sub

Q3. 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.

A3. Yes a ListObject is a Table. many moons ago they were called Lists in the worksheet but the name got changed to Table but the VBA has never been changed. If you search the internet for VBA code associated with List Objects you will probably find quite a few examples of the syntax. It is possible to use different syntax. eg I used the column number of the DataBodyRange. You can also use the column Id (Header Name)

Q4. Is vbCrLf same as vbNewLine? I guess it is.

A4. You guessed correctly. See the following link for some more information on these. All of the posts at the link are probably relevant. Which one to use is mostly matter of choice so if you come across any instances where you get strange results like 2 line feeds etc then try an alternative.

https://stackoverflow.com/questions/37273817/vba-vbnewline-but-with-no-extra-space

Q5. 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.

A5. See following line in the code.

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

Validate is the name of the UDF and the call passes lstObj.DataBodyRange.Columns(1) which populates the variable rngToSearch.

It also passes myInp which populates the variable varToFind

Q6. What is rngToFind?

A6. rngTofind is a range variable and the cell where the value is found is assigned to it. It will be a single cell range.

Q7. If rngToFind = Nothing.

A7. Firstly, rngTofind by itself never equals anything. The rngToFind.Value will equal something and is the value in the cell/range. The terminology is: If rngToFind is Nothing. ie. Nothing has been assigned to it. In the code I have used the opposite as follows.

If Not rngToFind Is Nothing

If it is NOT Nothing then it is Something so it has been assigned to a range meaning that the value being searched has been found and the cell where found is assigned to the range variable.

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

A8. xlValues is look in the values returned by formulas or values that are entered directly in a cell. Oposite is xlFormulas and is normally used in conjunction with xlPart (see next part of answer) to find cells that contain a particular formula. eg find COUNTA with xlFormulas will find a cell containing that COUNTA function.

xlWhole refers to the entire contents of the cell must match; not just part of the cell contents.

Opposite is xlPart. eg. cell contains OssieMac. Search for Ossie and use xlPart and the cell will be found but if xlWhole Ossie will not be found.

xlByRows means to search 1st row of range to search, then 2nd row, 3rd row etc. Opposite is xlColumns means to search 1st column then 2nd column, 3rd column etc.

xlNext means to search forward through the range from the first cell. Opposite is xlPrevious which will search backwards through the range from the last cell.

Interesting thing here is if xlNext is used then if the first cell of the range is a match then it is not found until after it finishes at the bottom of the range and loops around to the top of the range again. Find alwys loops around so code needs to be used to halt the code if FindNext is used when searching for multiple instances of a value.

If you perform a Find in the interactive mode on the worksheet and in the find dialog select the Options then you will be able to associate most of the VBA arguments to the Interactive Mode.

(xlPrevious is not supported in the Interactive mode)

Q9. 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.

A9. See the code example below where I clear the borders below the data and to right of data without affecting Conditional formatting.

Also see where I have populated the date cells directly with the value from the VBA Date function and it is not necessary to Copy -> PasteSpecial -> Values.

See how you go with it and again feel free to get back to me if any more questions or problems.

Amended Code Example.

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

Dim lngLastRow As Long

Dim strCity As String

Dim strRowRef As String

strCity = "New York"

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

    '######################################################################################################

    'Following line counts the number of instances of the input value in the first column of data

    'If WorksheetFunction.CountA(lstObj.DataBodyRange.Columns(1), myInp) > 0 Then           'OptionUsing column number

    If WorksheetFunction.CountA(lstObj.ListColumns("Serial").DataBodyRange, myInp) > 0 Then 'Option Using column header name

        Exit Do

    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

    '#######################################################################################################

    '\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

    'Code between asterisk lines replaced with alternative code above between hash lines

    '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

With wsOutput

    'Next line: Call the UDF to find the last used row on the worksheet

    lngLastRow = LastRow(.Cells)  '.Cells is entire range of wsOutput.

    .Range(.Cells(4, "O"), .Cells(lngLastRow, "O")) = strCity

    .Range(.Cells(4, "P"), .Cells(lngLastRow, "P")) = Date  'Date is the current date in VBA

    .Range(.Cells(4, "P"), .Cells(lngLastRow, "P")).NumberFormat = "dd-mmm-yyyy"

    .Range(.Cells(4, "Q"), .Cells(lngLastRow, "Q")) = "C"

    ***'Remove all borders below the last row of data***

    strRowRef = lngLastRow + 1 & ":" & .Rows.Count

    .Rows(strRowRef).Borders.LineStyle = xlNone

    ***'Remove all borders from column after last used column to right extremity of worksheet***

    ***'Next line just used the actual column reference from next column***

    ***'past data to maximum columns because usually known range.***

    .Columns("S:XFD").Borders.LineStyle = xlNone

End With

End Sub

Function LastRow(rng As Range) As Long

***'Finds the last used row in a worksheet. Essential if not all columns have data to bottom***

Dim rngToFind As Range

With rng

    Set rngToFind = .Find(What:="\*", \_

            LookIn:=xlFormulas, \_

            LookAt:=xlPart, \_

            SearchOrder:=xlRows, \_

            SearchDirection:=xlPrevious, \_

            MatchCase:=False)

End With

If Not rngToFind Is Nothing Then

    LastRow = rngToFind.Row

Else

    LastRow = 1  'If nothing found then set row one as last row

End If

End Function

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

***'This function not required when COUNTA function used in lieu***

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

Thank you very much for taking the time to explain it to me.

Q1) I have a question on strPrompt. At the start we assigned strPrompt to a text.

During validation again we assigned strPrompt to a text.

How does second strPrompt invoke InputBox? Just because it was part of Inputbox earlier?

I tried the COUNTA code hoping it might be faster than function validate code. There is not much difference if not at all.

In COUNTA code, if I enter a invalid number or type a single number it throws an error 1004 no cells were found. The Err.number can be converted to a msg debug. Like I was expecting earlier.

Q2) 'Alternative syntax. "My Workbook" can be ThisWorkbook or a different Workbook.

I ran into problems when I use ThisWorkbook

e.g I created a macro to clear all the cells after I run the MCR macro so that I can run again with different number.

Code shown below:

ActiveWorkbook.Worksheets("My Sheet").Range("A4:R3000").ClearContents

Instead of active workbook, if I use ThisWorkbook it throws an error "method or data member not found."

Q3) rngTofind is a range variable and the cell where the value is found is assigned to it. It will be a single cell range.

Okay, so a single cell can also be assigned to a range cuz range can be singe cell to many cells.

Q4) Find arguments

I can relate it to a lookup function

xlValues means look in all values in the column range

xlWhole exact match

xlPart approx. match

xlByRows by rows

xlNext top down

MatchCase ???

In the find dialog select the options.

Options means arguments?

Q5) I might have not explained it clearly with the boarders. When I run the code it removes all the boarders.

After I run the macro say it had 20 used rows so I would expect all 20 rows to have all boarders (A1:R20). I save the file as .xlsx then I would clear the contents.

When I run the code with a new number. I might get 400 rows of data or less 100 rows. Now the boarders have to be dynamic for 400 (A1:R400) or 100 (A1:R100).

If it were 400, boarders until 400th row no boarders thereafter. If it were 100, boarders until 100th row no boarders thereafter.

I don't have to worry after column R there are no boarders applied as those columns are not part of the used range.

Q6) Can I write the below code as .Range.Cells(4, "O"), Cells(IngLastRow, "O") = strCity

.Range(.Cells(4, "O"), .Cells(lngLastRow, "O")) = strCity

and date format can be edited as well I suppose to "dd, mmm yyyy" (original "dd-mmm-yyyy")

Q7) And I noticed we used comma instead of colon e.g O4,O10 = strCity?

Q8) I did not get what is LngLastRow + 1 & ":" & .Rows.Count

If LnglastRow = 5 then I get 6:5

Q9) To apply boarders to the used rows. I only to need to edit .Borders.LineStyle = ??? (xlnone replace with ?)

Q10) In rngToFind why find "*", xlFormulas, xIPart, xLPrevious instead of xIValues, xIWhole, xINext.

Does the asterisk denote any character like we use for wildcard matches?

In that case xIPart makes sense. But xlFormulas, xLPrevious???

Q11) Can the below public function LastRow (although its predefined funtion) be written as follows, if not why do we declare the variable within the variable? I want to know what is rng As Range. Also we use With rng

Function LastRow () As Long

Dim rng As Range

Dim rngToFind As Range

Function LastRow(rng As Range) As Long

*Dim rngToFind As Range* 

Q12) Else LastRow = 1 my range starts from A4. A1 to A3 are headers should I change last row = 4?

'Remove all borders below the last row of data

strRowRef = lngLastRow + 1 & ":" & .Rows.Count

.Rows(strRowRef).Borders.LineStyle = xlNone

Function LastRow(rng As Range) As Long

'Finds the last used row in a worksheet. Essential if not all columns have data to bottom

Dim rngToFind As Range

With rng

Set rngToFind = .Find(What:="*", _

LookIn:=xlFormulas, _

LookAt:=xlPart, _

SearchOrder:=xlRows, _

SearchDirection:=xlPrevious, _

MatchCase:=False)

End With

If Not rngToFind Is Nothing Then

LastRow = rngToFind.Row

Else

LastRow = 1 'If nothing found then set row one as last row

End If

End Function

@ExcelFunTension,

My apologies for not getting back to you today. Unfortunately I was just too busy with some other matters that finished up taking the entire day to complete. I will try to get back to you tomorrow.

No problem. Once you are free could you go through the questions.

I have one challenge in the below code if I have one row retuned from the wsSource to wsOutput

O4, P4 and Q4 populate to the end of the sheet cuz when I recorded the macro to find the last row. I used Ctrl Shift Down. If there is only one value in A4 my macro also starts from A4. So it goes all the way down to last row in the sheet 1048576.

To overcome it, I adjusted my range to A3:R3 also tried A2:R2. However, in the R column R3 is merged R2&R3 see screenshot so Ctrl Shift Down won't select the A4:R4 row. Therefore, no borders to the last filled row of the sheet.

Need to fix it with a logic.

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 = Date

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

**Range("A4:R4").Select**

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

With Selection

.Borders(xlEdgeLeft).LineStyle = xlContinuous

.Borders(xlEdgeTop).LineStyle = xlContinuous

.Borders(xlEdgeBottom).LineStyle = xlContinuous

.Borders(xlEdgeRight).LineStyle = xlContinuous

.Borders(xlEdgeLeft).LineStyle = xlContinuous

.Borders(xlInsideVertical).LineStyle = xlContinuous

.Borders(xlInsideHorizontal).LineStyle = xlContinuous

End With

Reverse your thinking - change this:

Range("A4:R4").Select

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

To this:

Range(Range("A4"), Cells(Rows.Count,"R").End(xlUp)).Select