This thread is 10 years old. I suggest you start your own thread if you have a problem to solve.
Posts by rory
-
-
You can't do that, manually or in code. You would need to use an additional formula column, or an advanced filter. You could also create an array of all of last month's values plus "Yes" and filter for that.
-
It's easily done.
Glad we could help.
-
-
It looks like you are using an OLAP pivot so you need an array that you assign to the VisibleItemsList property of the field. All the array items should be of the format "[Customers].[Customer].[customer name here]"
-
Something like this:
Code
Display MorePrivate Sub OkBtn_Click() 'Check For Required Fields Dim Last As Long Last = sheet7.Cells(Rows.Count, 12).End(xlUp).Row If ReqLst.Value = Empty Or PO.Value = Empty Or DateEntered.Value = Empty Then MsgBox "Please Make Sure to Fill in All Fields Before Saving" Exit Sub End If sheet7.Unprotect Password:="Engineering" With sheet7 .ListObjects("PartsTbl").Range.AutoFilter Field:=12, Criteria1:=ReqLst.Value If Last > 3 Then .Range("M3:M" & Last).SpecialCells(xlCellTypeVisible).Value = PO.Value .Range("N3:N" & Last).SpecialCells(xlCellTypeVisible).Value = DateEntered.Value Else If .Rows(3).EntireRow.Hidden = False Then .Range("M3").Value = PO.Value .Range("N3").Value = DateEntered.Value End If End If .ListObjects("PartsTbl").Range.AutoFilter Field:=12 End With sheet7.Protect Password:="Engineering", _ AllowSorting:=True, _ AllowFiltering:=True Me.ReqLst.Value = "" Me.PO.Value = "" End Sub
-
If you use Specialcells on a single cell range (which is what happens when you have one row of data), it is applied to the entire worksheet. You need to test if Last = 3, and if it is, test if row 3 is hidden, instead of using SpecialCells.
-
Your code adds a new row to the table and populates that. There is no "information already on that line" since it's a new line (other than any table formulas that fill down).
-
It should not be there. The code is putting the value into the 13th column of the new row.
-
Your compile error is because you have typed Valuel instead of Value
-
You could use:
$A$3:INDEX($A$3:$A$13,COUNT($A$3:$A$13))
-
How representative is that of your actual data? It looks from that sample as though you can start at the third character, then find the first number after that, and see if there are at least 3 characters in between, rather than having to look for a sequence of at least 3 amongst many possible sequences.
-
Where did you put that code? It should be in the userform code module, not a regular module.
-
That's still an xlsx file so it can't have any code in it?
-
I suspect it would help if you posted the code you have currently rather than assuming we all know exactly what code that is.
-
You had at least two answers before you even posted here, neither of which you had acknowledged in any way. You should be aware that a lot of people post in multiple forums so this sort of behaviour does not go unnoticed and will often prevent you actually getting any further help.
-
You have already posted this question on several forums, and been warned about cross-posting:
Sorting 4 Keys of Excel fileThere is a requirement of 4 key fields to be sorted in Excel file (2013). Given below is the code. But not getting the desired results. Need your help to…stackoverflow.comHow to Sort Excel File (2013) with 4 or 5 Keys in VBA
How to sort excel file (2013) with 4 or 5 Keys in vbaI have a requirement to sort an Excel file (2013) with 4 keys / 5 keys. The following vba code is not working and at the same time not throwing any error too.…www.mrexcel.comAll without any acknowledgement of the help you have received so far.
Please read our rules and follow them, or you will not be welcome here.
-
For the advanced filter, you could enter the relevant header text in one cell, and <0 in the cell below and then use those two cells as the criteria range.
-
Also cross-posted (with sample) here: https://www.excelforum.com/exc…ast-page-of-an-excel.html
-