Posts by rory

This thread is 10 years old. I suggest you start your own thread if you have a problem to solve.

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.

Where exactly are you stuck? You have the name of the sheet, and it sounds like you want a simple formula link, so you'd just be adding a formula using code like:

somecell.formula = "='" & FullName & "'!A1"

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:

Private 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 file

There 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.com

https://www.myonlinetraininghub.com/excel-forum/vba-macros/how-to-sort-excel-file-2013-with-4-or-5-keys-in-vba

How to Sort Excel File (2013) with 4 or 5 Keys in VBA

How to sort excel file (2013) with 4 or 5 Keys in vba

I 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.com

All 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

The Dictionary.Items property returns an array though. (as does Keys)