Yes, my excel looks to be 2016 which would explain that.
Thanks, the formula above works as it should.
Cam
Yes, my excel looks to be 2016 which would explain that.
Thanks, the formula above works as it should.
Cam
I run a report from an external source that will include historical data over months and years and copy the data in column A-C. Then I have a formula in column F that works great to find the monthly averages. I am now trying to create a formula that will find the maximum number for each month.
In the attachment you will see in G2 and G3 I have attempted to create a formula to do such a thing and one returns #NAME error and one returns 0 value.
I have created the monthly date format in columns I-P since the report from the external source comes out as data and I don't want to manipulate the report.
#NAME Error Fomula =MAXifs($B$2:$B$1000,$M$2:$M$1000,">="&$E2,$M$2:$M$1000,"<=" &EDATE(E2,1))
0 Value Formula =IFERROR(MAXifs($B$2:$B$5000,$M$2:$M$5000,">="&$E3,$M$2:$M$5000,"<="&EOMONTH($E3,0)),0)
Thanks
Cam
I have created an excel formula to count the days of the month but it counts it from tomorrow until the end of the month. I'm looking to have it count and include the days starting from today.
Here is the formula I have:
=IF(C124>TODAY(),1,EOMONTH(TODAY(),0)-TODAY())
In C124 it has the first of the month in date format.
Thanks
Yes, that works. I was wondering what the ,,,395 meant in the first formula, now I know.
Thanks,
Palchy
One last question.....if I have only monthly totals instead of daily how would my formula change. I used the existing one and it returned nothing.
Attached file for example.....
Palchy
Thank you so much...formulas work beautifully.
Palchy
Ok, that ok.
Here is another scenario that I was looking to do that involves many numerical locations that I was hoping you could help me with. I've added a few lines as an example of what I am looking at.
Thanks,
Thanks...I can make that work.
Is there also way of doing it to search for month/day and not the year. So when I add more data I don't need to update the from and to dates?
Sorry about that.....example workbook as requested.
Hi there,
I have one year of Dates between B2:OF2
I have one year of Data between B6:OF6
In one cell I would like to take the average of data from months between November 1 - March 31.
In another cell I would like to take the average of data from months between April 1 - Oct 31.
The spreadsheet will continuously have one year of data so right now the spreadsheet has April 1, 2020 to April 30, 2021. Next month I'll drop April 2020 and add May 2021. So I'm hoping to do this without having to adjust the formula going forward.
Thanks for your help.
Hi Roy,
That code worked when I manually ran it....also worked with the time.value function.
Last question....will the macro continue to work when I save a new copy of the worksheet. New month requires a new sheet.
Thanks again for your help.
Thanks Roy,
I was able to get the first option to work no problem. But the second option didn't work and when I ran it manually I received a runtime error.
Thanks,
Hi there,
I have a macro that works as it should when I manually run it. I've tried using Application.Ontime to have it run at 3pm but so far that hasn't worked.
Sub SaveAs()
Application.OnTime TimeValue("15:00:00"), "SaveAs"
Dim MyFile As String
MyFile = ActiveWorkbook.Name
' Do not display the message about overwriting the existing file.
Application.DisplayAlerts = False
' Save the active workbook with the name of the
' active workbook. Save it on the Desktop
ActiveWorkbook.SaveAs Filename:="C:\Users\camp\Desktop\Backup\" & MyFile
' Close the workbook by using the following.
ActiveWorkbook.Close
End Sub
Display More
Thanks
Stephen,
When I use the following code it works instantaneous but two things. I need to run the code between P76:P500 (not just the P column), then I need to toggle back to unhide the zeros by running the same code. Is that possible?
Sub HideZeroRows()
Dim LRow As Long
LRow = Cells(Rows.Count, "P76:P276").End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For x = LRow To 1 Step -1
If Cells(x, "P76:P276").Value = "0" Then
Rows(x).Hidden = True
End If
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Display More
Thanks,
Hi Stephen,
Thanks, that did speed things up.
Anyway for it to be even faster? Only asking as the second code I submitted was less then a second to work but it wasn't working as I intended it.
Thanks again.
Hi there,
What I am attempting to do is create one VBA script attached to a button that will toggle to either hide or unhide cells based on it containing zero. I have created the simply script below that works but it takes to long to run as it goes line by line which you can see happening right before your eyes.
Sub ToggleHideRows()
Dim c As Range
For Each c In Range("P76:P500")
If Not IsEmpty(c) And c.Value = 0 Then
c.EntireRow.Hidden = Not c.EntireRow.Hidden
End If
Next
End Sub
To try and speed things up I pieced together the following script but I could only get it to hide ALL the zeros in column "P" instead of a selected portion such as P76:P500. Then it would need to have code to toggle back and forth between hiding and unhiding zeros which I couldn't figure out.
Sub HideZeroRows()
Dim LRow As Long
LRow = Cells(Rows.Count, "P76:P276").End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For x = LRow To 1 Step -1
If Cells(x, "P76:P276").Value = "0" Then
Rows(x).Hidden = True
End If
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Display More
Thank You and look forward to your response.
Hey Carim,
Since moving the files off my desktop to one of the drives on my computer I'm still finding that it doesn't delete the files from the folder after attaching them to the email. I
Is there anything else you can think of that would help me out.
Thanks
Thanks Carim, that did work. It wasn't a shortcut and it was the only folder but for whatever reason the "kill" script just wouldn't work until I moved it to another drive.
Thanks again.
Hello,
All I'm attempting to do, is take files from a folder and attach them to an email, then delete those files from the folder. The code that I have pieced together will attach the file(s) to outlook with the mailing address, subject heading and my company signature but it doesn't delete the file(s) from the folder. At the moment I don't want it to automatically send as I would like to check it over first but in the future I would like to switch to automatically send.
I'm hoping that I'm added the code tags to this post correctly.
Sub SendFilesbyEmail()
Call SendFiles("M:\Desktop\EOD\")
End Sub
Function SendFiles(fldName As String, Optional FileType As String = "*.*")
Dim fName As String
Dim sAttName As String
Dim olApp As Outlook.Application
Dim olMsg As Outlook.MailItem
Dim olAtt As Outlook.Attachments
Set olApp = Outlook.Application
Set olMsg = olApp.CreateItem(0) ' email
Set olAtt = olMsg.Attachments
' to send all
fName = Dir(fldName)
'to send only certain extensions
'fName = Dir(fldName & FileType)
Do While Len(fName) > 0
olAtt.Add fldName & fName
sAttName = fName & "<br /> " & sAttName
Debug.Print fName
fName = Dir
Loop
' send message
With olMsg
.Subject = "EOD Confirms"
.To = "[email protected]"
.Display
End With
On Error Resume Next
Kill "M:\Desktop\EOD\*.*"
On Error GoTo 0
End Function
Display More
Thanks in advance for your help.