Both solutions work - Thank you!
Hello, it's been a while!
First, Sorry if my title isn't good, but I really can't figure out how to summarize what I need for a proper title.
I have a simple workbook that keeps track of odometer readings. I'd like to have a column that shows the Year To Date Odometer reading based on the data entered every quarter. The problem is some cells will be blank because we haven't go to that quarter yet so it is not giving me proper results. The formula that was tried is "=F8-E8+G8-F8+H8-G8+I8-H8". This formula works if all the cells have data, but if there blanks it doesn't work. I've attached a sample for review.
Does anyone have any suggestions? My brain just isn't cooperating today. Thanks in advance.
Re: Only allow "Y" or "N" entries in cells without blanks
HA! I have nothing....That was so simple. I told you I was rusty! Thanks for the info.
Hello everyone, long time no post!
I'm trying to use Data Validation for cells. In these cells I only want a Y or N entry and cannot have blanks. I'm rusty on formulas and could use a little help if possible.
This is what I've come up with, please let me know what you think.
Re: Compare previous years earnings to current year by month
That worked great, thanks!
I'm a bit stumped on finding the correct formula to compare data. Here is what I'm trying to accomplish:
1. There are two sheets, "2011" and "2012"
2. Both sheets have all my offices listed with their earnings for each month
3. I'd like a formula in the 2012 sheet that will compare the year to date earnings for each office to the 2011 sheet, by month. For instance, we are in August right now so my 2012 sheet has earnings listed for January through July. I'd like a running total that will tell me what the year to date earnings were for each office through July in 2011. So if this year my Little Rock office has 500 dollars of earnings through July, I'd like to know what that number was in 2011.
I've attached a sample that will explain it a little more clear. Any help is appreciated as I'm a bit rusty with formulas.
Re: Defining the same variable, repetitively
Yes, makes perfect sense. Thanks for the input, I appreciate it.
I have a variable named public variable named MonthEnd. This variable will always return the month end date as "MMYY". I use this to help name extracted reports for the month.
I use this several times throughout the workbook so I was wondering if there was a way I could define this variable once, instead of in every routine? For instance, every time I use it, it is always:
Is there a more efficient way to define this variable once or is the way I'm doing it now the most efficient?
Any help is appreciated.
Re: Quitting Excel Application through ribbon controlQuote from rory;546969
Worked like charm! Thanks a lot for that. I was racking my brain trying to figure this out. I'll have to do some homework on application.ontime; I've never used it. Thanks again, Rory.
Re: Quitting Excel Application through ribbon controlQuote from rory;546966
Presumably that code is being invoked by your callback? Does it help if you have your callback invoke that code using Application.Ontime rather than calling it directly?
I'm not sure I follow you on the Application.Ontime suggestion. Yes, this code is invoked by the callback. I just edited the code. Notice it is now
I even tried changing the sub just FinishOU() and then create another sub named:
this had the same result.
Re: If statement to select cells only if not blank
What about sorting the data before copying so that the blank cells are the bottom. Then copy/paste?
I some simple code that saves the workbook as a different name and then closes the workbook and quits excel. The problem is that I receive an error "Incorrect Function" just when excel quits. The workbooks close and the application closes fine, but the error is constant.
I found that this error only occurs when the code is executed through the custom ribbon control. If I execute this code through the macros area, or through VBA editor, I don't receive the error.
Any idea why this would happen when executed through the ribbon control? Below is the codeCode
- Sub FinishOU(control as iRibbonControl)
- Dim MonthEndDate As Range, wb1 As Workbook
- With Application
- .ScreenUpdating = False
- .Calculation = xlCalculationManual
- .EnableEvents = False
- End With
- Set wb1 = ActiveWorkbook
- Set MonthEndDate = Sheet1.Range("C2:C3")
- With wb1
- MonthEndDate.Value = MonthEndDate.Value
- MonthEnd = Format(Sheet1.Range("C3"), "MMYY")
- .SaveAs MonthEndDir & MonthEnd & "\" & "OU-" & MonthEnd
- End With
- Set MonthEndDate = Nothing
- With Application
- .ScreenUpdating = True
- .Calculation = xlCalculationAutomatic
- .EnableEvents = True
- wb1.Close False
- End With
- End Sub
Re: Multiple Criteria Lookup for Conditional Formatting
works like a charm! Thanks for the lesson.
Hello again OzGrid!
Attached you will find a sample for a visual....
Basically what I need to do is apply a conditional formatting formula rule so I can highlight certain rows, based on two criteria.
Sheet1 has multiple columns of data. The two columns that need to be checked are:
"state" column and "CTCorp" Column.
Sheet2 has a list of addresses. The only column I need to reference on this sheet is the "state" column.
Here is what I need done. I would like conditional formatting to look at sheet2's first entry which is "AK". Look at sheet1 to see if we have "AK" listed. If we do, then look at the "CT Corp" column. If that reads "Yes", then highlight the address on sheet2. I need this done for all the states listed. I used "AK" as an example.
I've been playing with INDEX and MATCH, but I haven't figured out how check for the second criteria yet.
Thanks for your help (AGAIN),
Re: Find dates within date rangeQuote from daddylonglegs;530024
Small change to PCI's formula will check whether any of the three dates fall in the range
Worked like a charm, thanks guys.
Re: Find dates within date range
The first formula works, but it only checks C7. I need the formula to look at all the date ranges and tell me if any of the dates are within the 12/1/10 to 12/31/10 range.
The second formula looked as if it were checking all the date ranges, but it just didn't give me the result I need.
Re: Find dates within date range
PCI, I do need all date columns tested. I looked at your sample, but the second formula (the one that tests all columns) returns all "False", when there is actually a date in that range.
I was hoping to get a little help with a formula to check a series of date ranges to see if they fall between two dates. The ultimate goal is to apply a filter to only show the rows that contain the date that fails within the two date ranges.
For instance, this month I would run a report for December. So, my date ranges would be from 12/1/10 to 12/31/10 (these two date ranges are in two hidden cells. These two cells are formulas that produce the two date ranges).
I would want a formula that checks each range to see if I have an item that expires within this range. I've attached a sample for better clarification.
Re: Workbook reference returns runtime 1004
I got it to work by saving the Office 2003 created file (xls) as a macro enabled workbook (xlsm). Once I did this, the code ran fine. I'm assuming this has something to do with the rows.count (65536) not being the same in the 2003 version as in the 2007 version (1048576).
I have a macro that I created in Excel 2003 that no longer works in Excel 2007. I've tested several times and it works perfectly in 2003. The code is below.Code
- Dim rCpy As Range, wb2 As Workbook, ws2 As Worksheet, wsValues As Worksheet, wsNew As Worksheet
- Dim lLastRow As Long, lJcLastRow As Long, i As Long, r As Range, F As Range, MonthEnd As String
- Dim JobComDir As String, wbMaster As Workbook, wsMaster As Worksheet, rCopyRng As Range
- Dim lastrow As Long
- Set wbMaster = ThisWorkbook
- Set wsMaster = wbMaster.Sheets("JOBCOM")
- Set r = wsMaster.Range("A6:W6") 'Set filter range
- Set F = wsMaster.Range("S7:S" & Sheet3.Cells(Rows.Count, 2).End(xlUp).Row)
- F = "=Left(B7,2)" 'Pull the left numbers from the job number
- 'Display all contract jobs then delete
- With r
- .AutoFilter Field:=1, Criteria1:="=CON"
- .Offset(1, 0).Resize(.CurrentRegion.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible). _
- End With
- 'Add "Values" worksheet then copy the formula results from JOBCOM to Values sheet
- Set wsValues = wbMaster.Worksheets.Add
- wsValues.Name = "Values"
- Set rCpy = wsMaster.Range("S6:S" & wsMaster.Cells(Rows.Count, 2).End(xlUp).Row)
- wsValues.Range("A1").PasteSpecial (xlValues)
- lLastRow = wsValues.Range("A" & Rows.Count).End(xlUp).Row
- 'Filter copied range and copy to column so that the number rows are sequential
- wsValues.Range("A1:A" & lLastRow).AdvancedFilter Action:=xlFilterCopy, _
- copytorange:=wsValues.Range("B1"), Unique:=True
- 'Create new workbook, create sheets for each office and copy data
- Set wb2 = Workbooks.Add
- 'Loop through filtered range and print each areas entries
- [COLOR="red"]lLastRow = wbMaster.Sheets("Values").Range("B" & Rows.Count).End(xlUp).Row [/COLOR] <----------- THIS IS THE LINE WHERE I RECEIVE THE ERROR, AFTER I ADDED A NEW WORKBOOK ABOVE
- lJcLastRow = wsMaster.Range("A" & Rows.Count).End(xlUp).Row
I've highlighted the line that errors. The only way I can get this macro to continue is to actually select the workbook it is trying to reference so that it is the active workbook. I know I could easily fix this by activating the workbook, but I've never had to do this. Any ideas?