Posts by Infomage

I have tested this with your provided data.

Making some assumptions, the attached code works.

It is generally really not a good idea to do something I have done here with a loop variable but it seemed appropriate in this case.

What happens if you drop the "Set" keyword?

abc = ActiveWorkbook.Range("D3").Value

Something like:

For Each pt In ActiveSheet.PivotTables
    pt.RefreshTable
Next

Sorry - this isn't likely to help you much, but I have had a look and can't see anything obvious that I would do differently....

That said, when I run your FillDates macro on your attached file on my machine it takes something less than 30 seconds to complete... This is on an Intel Core i7 based laptop with 8Gb RAM running 64-bit Office 365... Perhaps a hardware upgrade is the solution?

Please use code tags around your code.

You need to refer to the named range in your code as a range:

Sub test()
Dim testvalue As Variant
For Each testvalue In ActiveSheet.Range("AreaCOLow")
MsgBox (testvalue)
Next
End Sub

Edit: I think you may be barking up the wrong tree, though. What's the actual requirement?

1) Can you upload a sheet with examples of the results you need?
2) Please use the better version I gave you in your other thread.

mash

You're welcome.

=IF(ABS(F1-G1)<=TIME(1,0,0),TRUE(),FALSE())

is better - more easily configurable... the parameters are TIME(Hours,Minutes,Seconds)

Try this in cell G1:

=IF(ABS(F1-G1)<=0.041666667,TRUE(),FALSE())

Note: 0.041666667 is the time serial number for 1 hour.

stone

Someting like this perhaps? Should work up to row 104 with no problems.

For anything more elegant, you should really look at a VBA solution.

post

What about Worksheet_Deactivate?

case

hoop

What about:

Sub SendHTMLEmail()
Dim OutlookApp As Object
Dim MItem As Object
Dim cell As Range
Dim Subj As String
Dim EmailAddr As String
Dim Recipient As String
Dim Msg As String
Dim SecondAttachment as String


'Create Outlook object
Set OutlookApp = CreateObject("Outlook.Application")


'Loop through the list of emails in Column A
For Each cell In Columns("A").Cells.SpecialCells(xlCellTypeVisible)
If cell.Value Like "*@*" Then
EmailAddr = cell.Value
SecondAttachment = cell.offset(0,1).value


'Create email and view before sending. To automatically send all emails, uncomment the .send line at the end.
Set MItem = OutlookApp.CreateItem(olMailItem)
With MItem
'Email Importance. Can be 1 (which is normal), 2 (which is High) or 0 (which is Low).
.Importance = 2
'Read Receipt. Set this to True or False.
.ReadReceiptRequested = True
.To = EmailAddr
.cc = "ourcompanyemailhere"
.Subject = "subject here however long I need"
.HTMLbody = "Dear Sir/Madam," & "<br><br>" & _
"blahdy blahdy blah." & "<br><br>" & _
"moredy moredy more" & "<br><br>" & _
"Kind regards,"
.Attachments.Add ("F:\2017\Sub\Main File Here.docx")
.Attachments.Add (SecondAttachment)
.Display
'.Send
End With
End If


Next cell


End Sub

***Warning*** Untested!

sheets("sheetname").visible = xlveryhidden

will make the sheet hidden, and hide it from the unhide context menu... Will that do?

In order to display it again, VBA must run to unhide it.

I don't think you're in formula territory here, but a simple bit of VBA should do the trick for you, if that's an option?