Almost There (1 Viewer)
- Thread starter dcavaiani
- Start date
NauticalGent
Ignore List Poster Boy
- Local time
- Today, 10:28
- Joined
- Apr 27, 2015
- Messages
- 6,322
Excel is not my forte, but it seems like you are trying to use an IIF statement and not and IF statement. If so, your IIF statement is missing a parameter. Either the True or False part.
I think you need to tell us more about what you are trying to do. For example where does this SumByColor function come from? Is it the one found in here? Also why are you using the return value from this function as the logical test argument to the IF function. And as NauticalGent noticed there's no False part to the IF.
I took this function
I found here and modified it to come up with this MaxOfColor function
Excel isn't my forte either so there was some guessing in this conversion but this is suppose to return the maximum of in the rRange with the background color CellColor. It seems to work. If that's what you are trying to do you can give it a try.
Code:
Function SumByColor(CellColor As Range, rRange As Range)
Dim cSum As Long
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cSum = WorksheetFunction.SUM(cl, cSum)
End If
Next cl
SumByColor = cSum
End FunctionI found here and modified it to come up with this MaxOfColor function
Code:
Function MaxOfColor(CellColor As Range, rRange As Range)
Dim cMax As Double
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
If cl.Interior.ColorIndex = ColIndex Then
cMax = WorksheetFunction.Max(cl, cMax)
End If
Next cl
MaxOfColor = cMax
End FunctionExcel isn't my forte either so there was some guessing in this conversion but this is suppose to return the maximum of in the rRange with the background color CellColor. It seems to work. If that's what you are trying to do you can give it a try.
Rx_
Nothing In Moderation
- Local time
- Today, 08:28
- Joined
- Oct 22, 2009
- Messages
- 2,803
Sorry to apply so late, have some big deadlines on my side of the Pond....
While this won't directly answer your question, it might provide another way to sort and copy the results to another worksheet.
This is only code segment, but it might give some ideas about SpecialCells.
It is just a small part of an automated process of 70 code pages that runs from within MSAccess VBA (with Linked tables to SQL server), Excel workbooks and CSV data from mainframes loaded into multiple Excel with 100,000 rows each.
The OBJXL is an object variable set to an Excel Application.
XlWB is a Workbook object within the Excel Application.
Key: .Range("$A$2:Z" & LastRow).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
- By using SpecialCells (because I am soooo Special) delete all the cells that I didn't want.
This segment is part of a procedure that opens an Excel Workbook, turns off the Alerts so no pesky messages show up.
Of possible interest to your post (finally!):
Make a copy
Turn on the AutoFilter - and set the criteria - in this case choose all that you DONT WANT. This leaves the cells (hidden) that you did want.
Because the Excel filter, criteria, hidden cells are part of a built-in binary function, these functions tend to run very fast.
Once all the unnecessary cells are deleted in a copy, show the results and the result is a nice package without row spaces.
Searching for the SpecialCells keyword might offer some new ideas for sorting or other purposes.
If a download shows up as a dozen 100,000 rows (50 columns wide) of CSV for example, this can use multiple filters to par down the records to the 3,000 needed records, then past that result into a new single Excel workbook for the next process.
In Access, this might represent a Delete Query with criteria.
While this won't directly answer your question, it might provide another way to sort and copy the results to another worksheet.
This is only code segment, but it might give some ideas about SpecialCells.
It is just a small part of an automated process of 70 code pages that runs from within MSAccess VBA (with Linked tables to SQL server), Excel workbooks and CSV data from mainframes loaded into multiple Excel with 100,000 rows each.
The OBJXL is an object variable set to an Excel Application.
XlWB is a Workbook object within the Excel Application.
Key: .Range("$A$2:Z" & LastRow).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
- By using SpecialCells (because I am soooo Special) delete all the cells that I didn't want.
This segment is part of a procedure that opens an Excel Workbook, turns off the Alerts so no pesky messages show up.
Of possible interest to your post (finally!):
Make a copy
Turn on the AutoFilter - and set the criteria - in this case choose all that you DONT WANT. This leaves the cells (hidden) that you did want.
Because the Excel filter, criteria, hidden cells are part of a built-in binary function, these functions tend to run very fast.
Once all the unnecessary cells are deleted in a copy, show the results and the result is a nice package without row spaces.
Searching for the SpecialCells keyword might offer some new ideas for sorting or other purposes.
If a download shows up as a dozen 100,000 rows (50 columns wide) of CSV for example, this can use multiple filters to par down the records to the 3,000 needed records, then past that result into a new single Excel workbook for the next process.
In Access, this might represent a Delete Query with criteria.
Code:
EPSENG_FilteredCSV = "C:\Users\" & usernamepath & "\Desktop\NoProjExcel\OutPutFiltered\" & WBType & " CAPEX Transaction Detail.xlsx"
Set xlWB = Objxl.Workbooks.Open(EPSENG_FilteredCSV) ' OPEN CSV filtered wB
Objxl.DisplayAlerts = False ' if file save as already exist, automatically overwrite it
xlWB.SaveAs FileName:=ExcelBuildNewReport_Name & ".xlsx", CreateBackup:=False ' output to folder
Objxl.Sheets(1).Name = "AP Pmts " & DatePart("m", Now()) & CStr(DatePart("d", Now()))
Objxl.Sheets(1).Select
Objxl.Sheets(1).Copy After:=Objxl.Sheets(1)
Objxl.Sheets(2).Select
Objxl.Sheets(2).Name = "Inv Iss " & DatePart("m", Now()) & CStr(DatePart("d", Now()))
Objxl.Sheets(1).Select
Objxl.Sheets(1).Rows("1:1").Select
Objxl.Selection.AutoFilter
Objxl.Sheets(2).Select
Objxl.Rows("1:1").Select
Objxl.Selection.AutoFilter
Objxl.Sheets(1).Select
' filter columns
'LastRow = (objXL.Rows.Count) - 1 ' get last count of row - since we just copied WS they are both the same and can be used for each WS
With Objxl
LastRow = (.Cells(Objxl.Rows.Count, "A").End(xlUp).Row) ' get last row
' ****************************************
' Filter to AP Payments on tab 1
' ***************************************
.Rows("1:1").Select
' Adjust to select entire data range minus header - to do Set variable here to reuse for other filters
TransactionCategory = "AP Payments"
.Range("A" & .Rows.Count).AutoFilter Field:=1, Criteria1:= _
"<>" & TransactionCategory, Operator:=xlAnd
LastRow = (.Cells(Objxl.Rows.Count, "A").End(xlUp).Row)
.Range("$A$2:Z" & LastRow).Select
.Range("$A$2:Z" & LastRow).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
.ActiveSheet.Range("$A$1:$CG$" & LastRow).AutoFilter Field:=1
.Rows("1:1").Select
'.Selection.AutoFilter ' turn off autofilter ****************************************
' Filter to NO PROJ Payments on tab 1
' ***************************************
'.ActiveSheet.Range("$A$1:$CG$932").AutoFilter Field:=16, Criteria1:="NO PROJ"
.Rows("1:1").Select
' Adjust to select entire data range minus header - to do Set variable here to reuse for other filters
TransactionCategory = "NO PROJ" ' Filter to NO Proj around Column O
.Range("A" & .Rows.Count).AutoFilter Field:=16, Criteria1:= _
"<>" & TransactionCategory, Operator:=xlAnd
LastRow = (.Rows.Count) ' - 1 ' comment out -1 the Last Record was not filtered correctly
.Range("$A$2:Z" & LastRow).Select
.Range("$A$2:Z" & LastRow).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
.ActiveSheet.Range("$A$1:$CG$" & LastRow).AutoFilter Field:=16
.Rows("1:1").Select
'.Selection.AutoFilter ' turn off autofilter '
