Hi Luke,
if my column which needs to be searched is not column A (it is Column AI) so what I have to amend in your code to get it work?
Thanks so much for your reply!
Don't need to amend the code, just change some things in the workbook. Unhide Sheet2, and change the value of cell A1 to be whatever header you want to do the filter on. The other part you may need to change is in the Formulas - Name Manager, make sure the definition of rngTable is the correct range of your table.
You could do a COUNTIFS and see if there's at least one data point.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim myCount As Long
With Sheets("5s Report Dec 2019")
'How many items have fails but nothing in next column?
myCount = WorksheetFunction.CountIfs(.Range("H:H"), "Fail", .Range("I:I"), "")
End With
If myCount > 0 Then
Cancel = True
MsgBox "Please enter a values in columns I and J", vbCritical, "Error!"
End If
End SubWhile we could certainly write some code, you could use this add-in to merge the data? Let's you pick your files, worksheet(s), and range you want copied.
Should be doable, but some more info would be helpful for building the exact code. Are you wanting to send a basic message to each user, an image from body of workbook, or an attachment? Is it just the two columns of data we need to work with?
One way I'd do it is to copy the list of emails to a new blank column, Remove Duplicates. Then cycle through that unique list and use it to filter for emails back in your original data. Depending on what we do next, we either create an attachment to send, or copy and image.
The labels in a PivotTable, including dates, are all displayed as Text. So, when trying to match up against the numerical date in A1, it's failing. You might have success by changing the "$A$1" argument to this
TEXT($A$1, "dd/mm/yyyy h:mm")
While you could do this, it would be far easier to just Insert - Symbol, and use those in an IF statement.
Look under the Segoe UI Symbol list.
Here's an example to find last row with data in a column.
Sub FindData()
Dim lastRow As Long
lastRow = ActiveCell.EntireColumn.Find(what:="*", LookIn:=xlValues, searchdirection:=xlPrevious).Row
MsgBox "Last row with data: " & lastRow
End SubBetween dynamic and table, I'd say the table will generally always win (assuming you don't need XL2003 and older compliancy). Beyond that, your next consideration is whether the data should be in a database like Access vs. Excel. If you start handling over 100k of rows, XL will start to struggle to keep up.
I'd suggest using the table. It's more "native" should will be faster than first having to do a formula calculation (even a simpel one) to define the table range. Also, using the strcutural references it'll be easier later to determine what all the formulas are doing (assuming good field names).
Digger deeper, why do you want the cell address? Reason I ask, many times people do this and then feed the address into INDIRECT, when it's generally easier to get the data directly.
But, assuming value only appears once
=ADDRESS(SUMPRODUCT((TableRange="SearchValue")*ROW(TableRange)), SUMPRODUCT((TableRange="SearchValue")*COLUMN(TableRange)))
Wow, that one was buried deep. Turns out the names were hidden from the file manager. Not sure how, but they're there. If you run this short script, you'll then be able to see the names and delete them.
Sub RevealSecrets()
Dim nm As Name
For Each nm In ActiveWorkbook.Names
nm.Visible = True
Next nm
End SubHere's I tool put together compiling various password tools. Workbookand Worksheet protection are the easiest to remove.
https://chandoo.org/forum/threads/remove-password.23208/
Just sheet protection: http://www.mcgimpsey.com/excel/removepwords.html
You need a way to tell the VBA that you're not ending the string, so we use double quotation marks to indicate that "yes, I want a quotation mark here". I also went through and optimized things a little bit
Sub TestMacro2()
'Make some variables
Dim strForm As String
'turn off screen flashing
Application.ScreenUpdating = False
Rows("1:1").Delete Shift:=xlUp
Cells.ColumnWidth = 35.71
Cells.EntireRow.AutoFit
Columns("C:C").ColumnWidth = 15.14
Columns("D:D").ColumnWidth = 12.14
Columns("E:E").ColumnWidth = 11
Columns("F:F").ColumnWidth = 11.29
Columns("G:G").ColumnWidth = 11.57
Columns("H:H").ColumnWidth = 4.57
Columns("I:I").ColumnWidth = 3.57
'What's the formula? Note the use of double quotation marks so we don't end too early
strForm = "=IF(F2>=(TODAY()+31),""Good"",IF(AND(F2<=(TODAY()+30),(F2>=(TODAY()+1))),""Due Soon"",IF(G2<>"""",""Good"",""Over Due"")"
'Write the formula all at once
Range("J2:J36").Formula = strForm
Range("G2:G36").Replace What:="-", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("F2:G36").NumberFormat = "m/d/yyyy"
Application.ScreenUpdating = True
End SubIt looks like column A is your label, and B:D are your inputs. Col E (to check your math) would be simply
=SUM(B2:D2)
In F2
=(12*B2+3*C2)/100
In G2
=(100*B2+30*C2+5*D2)/100
My guess is that you have a sheet name with a space somewhere in the name. In which case, you need to make sure you're including single quotation marks around the sheet name. Try something like this
Sub ExampleCode()
Dim S As Worksheet
Dim ThisName As String
Dim TOCRow As Long
Dim LinkName As String
'not sure what row this started at
TOCRow = 1
Application.ScreenUpdating = False
For Each S In Worksheets
' Excluded sheets - Not needed in index.
If S.Visible = -1 And S.Name <> "Selections" And S.Name <> "Guide" _
And S.Name <> "Cover" And S.Name <> "TOC" And S.Name <> "Index" _
And Not S.Name Like "Sheet*" _
Then
' Create visible text using footer data + cell A1 data
ThisName = S.PageSetup.RightFooter & " -- " & S.Range("a1").Value
' Strip off control chars
ThisName = Mid(ThisName, 3)
' Create link
LinkName = "'" & S.Name & "'!A1"
With Sheets("Index")
.Hyperlinks.Add Anchor:=.Cells(TOCRow, 1), Address:="", _
SubAddress:=LinkName, TextToDisplay:=ThisName
End With
TOCRow = TOCRow + 1 'Bump row nbr
End If
Next S
End SubChange the 2nd argument depending on when your week begins, but would be something like
=WEEKNUM(E2, 1)
Other formulas:
=MONTH(E2)
=INT((MONTH(E2)-1)/3)+1
=YEAR(E2)
Try this. It'll automaitcally clear out old query before running, and it returns all matches and relvant info.
Sub SearchAll()
Dim strName As String
Dim ws As Worksheet
Dim fCell As Range
Dim wsMain As Worksheet
Dim firstAdd As String
'Where does output go?
Set wsMain = Worksheets("Query")
'Get info from user?
strName = InputBox("What name do you want to search for?", "Name")
If strName = "" Then Exit Sub 'User cancelled
Application.ScreenUpdating = False
'Clear old data
wsMain.Range("A2:B20000").ClearContents
For Each ws In ThisWorkbook.Worksheets
'Clear values
Set fCell = Nothing
firstAdd = ""
If ws.Name <> wsMain.Name Then
Set fCell = ws.Range("B:B").Find(what:=strName, LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
If Not fCell Is Nothing Then
'We found something!
firstAdd = fCell.Address
Do
With wsMain
.Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value = fCell.Offset(0, 1).Value
.Cells(.Rows.Count, "B").End(xlUp).Offset(1).Value = ws.Name & "; " & _
fCell.Address(False, False) & "; " & fCell.Value
End With
Set fCell = ws.Range("B:B").FindNext(fCell)
Loop Until fCell.Address = firstAdd
End If
End If
Next ws
Application.ScreenUpdating = True
End SubYou want to use the EntireRow to get the range selected, not the Row property.
Sub MEF()
Dim Star8 As Integer
Dim Title8 As Range
Dim Rf As String
Dim x As Long
Dim lastRow As Long
Rf = Chr(42)
lastRow = Range("A" & Rows.Count).End(xlUp).Row
'If you use code name of a sheet, don't include workbook object
Set Title8 = Sheet15.Range("A10")
'Had a typo here, should just be Rf
Star8 = InStrRev(Title8.Value, Rf)
For x = 2 To lastRow - 1
y = InStrRev(Cells(x, 1).Value, Rf)
Select Case y
Case Star8
Title8.EntireRow.Copy
Cells(x, 1).EntireRow.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
'Carry on...I'd suggest just putting a mark of some kind in a helper column (maybe an "X"?), and then use Data - Filter, and you can filter your sheet to only show those present, and print that out.
Looks like the attachment is missing?