Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif 3D
Hello again
I know I've been posting a lot, but the workbook I'm currently working on has no end of problems, and is probably beyond my capabilities. But there, I have to do it! So I really appreciate any help that you could give me. I'm trying to use a UDF (that was posted a while ago, so I can't ask the author) that I found on: http://www.dailydoseofexcel.com/arch...ned-functions/ I want to Count every time a name appears in the same selection of cells across multiple worksheets. I have copied the code in, and plugged in the numbers but I get #VALUE! returned. My only theory is that some cells will be empty and that confuses excel? What am I doing wrong!?!? Thanks in advance! The formula I enter is: =CountIf3D2('Blank 1:Blank 2'!A9:A10;A43) The code relating to CountIf3D2 is as follows ( I think it also utilises the function Parse3DRange2, which is also pasted below): Function CountIf3D2(Range3D As String, Criteria As String) _ As Variant Dim i As Long Dim Count As Long Dim vaRng1 As Variant Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, Range3D) Count = 0 For i = LBound(vaRng1) To UBound(vaRng1) Count = Count + Application.WorksheetFunction.CountIf(vaRng1(i), Criteria) Next i CountIf3D2 = Count End Function Function Parse3DRange2(wb As Workbook, _ SheetsAndRange As String) As Variant Dim sTemp As String Dim i As Long, j As Long Dim Sheet1 As String, Sheet2 As String Dim aRange() As Range Dim sRange As String Dim lFirstSht As Long, lLastSht As Long Dim rCell As Range Dim rTemp As Range On Error GoTo Parse3DRangeError sTemp = SheetsAndRange 'if it's 3d, rtemp will be nothing On Error Resume Next Set rTemp = Range(sTemp) On Error GoTo Parse3DRangeError 'if it's 3d, parse it If rTemp Is Nothing Then i = InStr(sTemp, "!") If i = 0 Then Err.Raise 9999 'next line will generate an error if range is invalid 'if it's OK, it will be converted to absolute form sRange = Range(Mid$(sTemp, i + 1)).Address sTemp = Left$(sTemp, i - 1) i = InStr(sTemp, ":") Sheet2 = Trim(Mid$(sTemp, i + 1)) If i 0 Then Sheet1 = Trim(Left$(sTemp, i - 1)) Else Sheet1 = Sheet2 End If 'next lines will generate errors if sheet names are invalid With wb lFirstSht = .Worksheets(Sheet1).Index lLastSht = .Worksheets(Sheet2).Index 'swap if out of order If lFirstSht lLastSht Then i = lFirstSht lFirstSht = lLastSht lLastSht = i End If 'load each cell into an array j = 0 For i = lFirstSht To lLastSht For Each rCell In .Sheets(i).Range(sRange) ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Next i End With Parse3DRange2 = aRange Else 'range isn't 3d, so just load each cell into array For Each rCell In rTemp.Cells ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Parse3DRange2 = aRange End If Parse3DRangeError: On Error GoTo 0 Exit Function End Function 'Parse3DRange Category: Excel Experts E-Letter | Comment (RSS) | Trackback |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif 3D
Joanne,
The Original code doesn't properly handle sheet names with spaces. Either rename your sheets to take out the spaces, or change Sheet2 = Trim(Mid$(sTemp, i + 1)) If i 0 Then Sheet1 = Trim(Left$(sTemp, i - 1)) to Sheet2 = Replace(Trim(Mid$(sTemp, i + 1)), "'", "") If i 0 Then Sheet1 = Replace(Trim(Left$(sTemp, i - 1)), "'", "") HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Hello again I know I've been posting a lot, but the workbook I'm currently working on has no end of problems, and is probably beyond my capabilities. But there, I have to do it! So I really appreciate any help that you could give me. I'm trying to use a UDF (that was posted a while ago, so I can't ask the author) that I found on: http://www.dailydoseofexcel.com/arch...ned-functions/ I want to Count every time a name appears in the same selection of cells across multiple worksheets. I have copied the code in, and plugged in the numbers but I get #VALUE! returned. My only theory is that some cells will be empty and that confuses excel? What am I doing wrong!?!? Thanks in advance! The formula I enter is: =CountIf3D2('Blank 1:Blank 2'!A9:A10;A43) The code relating to CountIf3D2 is as follows ( I think it also utilises the function Parse3DRange2, which is also pasted below): Function CountIf3D2(Range3D As String, Criteria As String) _ As Variant Dim i As Long Dim Count As Long Dim vaRng1 As Variant Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, Range3D) Count = 0 For i = LBound(vaRng1) To UBound(vaRng1) Count = Count + Application.WorksheetFunction.CountIf(vaRng1(i), Criteria) Next i CountIf3D2 = Count End Function Function Parse3DRange2(wb As Workbook, _ SheetsAndRange As String) As Variant Dim sTemp As String Dim i As Long, j As Long Dim Sheet1 As String, Sheet2 As String Dim aRange() As Range Dim sRange As String Dim lFirstSht As Long, lLastSht As Long Dim rCell As Range Dim rTemp As Range On Error GoTo Parse3DRangeError sTemp = SheetsAndRange 'if it's 3d, rtemp will be nothing On Error Resume Next Set rTemp = Range(sTemp) On Error GoTo Parse3DRangeError 'if it's 3d, parse it If rTemp Is Nothing Then i = InStr(sTemp, "!") If i = 0 Then Err.Raise 9999 'next line will generate an error if range is invalid 'if it's OK, it will be converted to absolute form sRange = Range(Mid$(sTemp, i + 1)).Address sTemp = Left$(sTemp, i - 1) i = InStr(sTemp, ":") Sheet2 = Trim(Mid$(sTemp, i + 1)) If i 0 Then Sheet1 = Trim(Left$(sTemp, i - 1)) Else Sheet1 = Sheet2 End If 'next lines will generate errors if sheet names are invalid With wb lFirstSht = .Worksheets(Sheet1).Index lLastSht = .Worksheets(Sheet2).Index 'swap if out of order If lFirstSht lLastSht Then i = lFirstSht lFirstSht = lLastSht lLastSht = i End If 'load each cell into an array j = 0 For i = lFirstSht To lLastSht For Each rCell In .Sheets(i).Range(sRange) ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Next i End With Parse3DRange2 = aRange Else 'range isn't 3d, so just load each cell into array For Each rCell In rTemp.Cells ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Parse3DRange2 = aRange End If Parse3DRangeError: On Error GoTo 0 Exit Function End Function 'Parse3DRange Category: Excel Experts E-Letter | Comment (RSS) | Trackback |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif 3D
I'd check your data first.
The majority of that code parses the formula to figure out what worksheets (and ranges) should be used. The line that does the "real" work is this: Count = Count + Application.WorksheetFunction.CountIf(vaRng1(i), Criteria) The first thing I would do is build an equivalent =countif() in an empty cell on each worksheet--just for that worksheet. Do you see a number returned in each of those formulas? (I'd look for errors in any of the ranges in any of the worksheets.) Joanne wrote: Hello again I know I've been posting a lot, but the workbook I'm currently working on has no end of problems, and is probably beyond my capabilities. But there, I have to do it! So I really appreciate any help that you could give me. I'm trying to use a UDF (that was posted a while ago, so I can't ask the author) that I found on: http://www.dailydoseofexcel.com/arch...ned-functions/ I want to Count every time a name appears in the same selection of cells across multiple worksheets. I have copied the code in, and plugged in the numbers but I get #VALUE! returned. My only theory is that some cells will be empty and that confuses excel? What am I doing wrong!?!? Thanks in advance! The formula I enter is: =CountIf3D2('Blank 1:Blank 2'!A9:A10;A43) The code relating to CountIf3D2 is as follows ( I think it also utilises the function Parse3DRange2, which is also pasted below): Function CountIf3D2(Range3D As String, Criteria As String) _ As Variant Dim i As Long Dim Count As Long Dim vaRng1 As Variant Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, Range3D) Count = 0 For i = LBound(vaRng1) To UBound(vaRng1) Count = Count + Application.WorksheetFunction.CountIf(vaRng1(i), Criteria) Next i CountIf3D2 = Count End Function Function Parse3DRange2(wb As Workbook, _ SheetsAndRange As String) As Variant Dim sTemp As String Dim i As Long, j As Long Dim Sheet1 As String, Sheet2 As String Dim aRange() As Range Dim sRange As String Dim lFirstSht As Long, lLastSht As Long Dim rCell As Range Dim rTemp As Range On Error GoTo Parse3DRangeError sTemp = SheetsAndRange 'if it's 3d, rtemp will be nothing On Error Resume Next Set rTemp = Range(sTemp) On Error GoTo Parse3DRangeError 'if it's 3d, parse it If rTemp Is Nothing Then i = InStr(sTemp, "!") If i = 0 Then Err.Raise 9999 'next line will generate an error if range is invalid 'if it's OK, it will be converted to absolute form sRange = Range(Mid$(sTemp, i + 1)).Address sTemp = Left$(sTemp, i - 1) i = InStr(sTemp, ":") Sheet2 = Trim(Mid$(sTemp, i + 1)) If i 0 Then Sheet1 = Trim(Left$(sTemp, i - 1)) Else Sheet1 = Sheet2 End If 'next lines will generate errors if sheet names are invalid With wb lFirstSht = .Worksheets(Sheet1).Index lLastSht = .Worksheets(Sheet2).Index 'swap if out of order If lFirstSht lLastSht Then i = lFirstSht lFirstSht = lLastSht lLastSht = i End If 'load each cell into an array j = 0 For i = lFirstSht To lLastSht For Each rCell In .Sheets(i).Range(sRange) ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Next i End With Parse3DRange2 = aRange Else 'range isn't 3d, so just load each cell into array For Each rCell In rTemp.Cells ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Parse3DRange2 = aRange End If Parse3DRangeError: On Error GoTo 0 Exit Function End Function 'Parse3DRange Category: Excel Experts E-Letter | Comment (RSS) | Trackback -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif 3D
Thank you, I made the change to the code you sugested.
However when testing, although I now no longer get "#VALUE!", I get a zero when I know there should be 4. "Bernie Deitrick" wrote: Joanne, The Original code doesn't properly handle sheet names with spaces. Either rename your sheets to take out the spaces, or change Sheet2 = Trim(Mid$(sTemp, i + 1)) If i 0 Then Sheet1 = Trim(Left$(sTemp, i - 1)) to Sheet2 = Replace(Trim(Mid$(sTemp, i + 1)), "'", "") If i 0 Then Sheet1 = Replace(Trim(Left$(sTemp, i - 1)), "'", "") HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Hello again I know I've been posting a lot, but the workbook I'm currently working on has no end of problems, and is probably beyond my capabilities. But there, I have to do it! So I really appreciate any help that you could give me. I'm trying to use a UDF (that was posted a while ago, so I can't ask the author) that I found on: http://www.dailydoseofexcel.com/arch...ned-functions/ I want to Count every time a name appears in the same selection of cells across multiple worksheets. I have copied the code in, and plugged in the numbers but I get #VALUE! returned. My only theory is that some cells will be empty and that confuses excel? What am I doing wrong!?!? Thanks in advance! The formula I enter is: =CountIf3D2('Blank 1:Blank 2'!A9:A10;A43) The code relating to CountIf3D2 is as follows ( I think it also utilises the function Parse3DRange2, which is also pasted below): Function CountIf3D2(Range3D As String, Criteria As String) _ As Variant Dim i As Long Dim Count As Long Dim vaRng1 As Variant Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, Range3D) Count = 0 For i = LBound(vaRng1) To UBound(vaRng1) Count = Count + Application.WorksheetFunction.CountIf(vaRng1(i), Criteria) Next i CountIf3D2 = Count End Function Function Parse3DRange2(wb As Workbook, _ SheetsAndRange As String) As Variant Dim sTemp As String Dim i As Long, j As Long Dim Sheet1 As String, Sheet2 As String Dim aRange() As Range Dim sRange As String Dim lFirstSht As Long, lLastSht As Long Dim rCell As Range Dim rTemp As Range On Error GoTo Parse3DRangeError sTemp = SheetsAndRange 'if it's 3d, rtemp will be nothing On Error Resume Next Set rTemp = Range(sTemp) On Error GoTo Parse3DRangeError 'if it's 3d, parse it If rTemp Is Nothing Then i = InStr(sTemp, "!") If i = 0 Then Err.Raise 9999 'next line will generate an error if range is invalid 'if it's OK, it will be converted to absolute form sRange = Range(Mid$(sTemp, i + 1)).Address sTemp = Left$(sTemp, i - 1) i = InStr(sTemp, ":") Sheet2 = Trim(Mid$(sTemp, i + 1)) If i 0 Then Sheet1 = Trim(Left$(sTemp, i - 1)) Else Sheet1 = Sheet2 End If 'next lines will generate errors if sheet names are invalid With wb lFirstSht = .Worksheets(Sheet1).Index lLastSht = .Worksheets(Sheet2).Index 'swap if out of order If lFirstSht lLastSht Then i = lFirstSht lFirstSht = lLastSht lLastSht = i End If 'load each cell into an array j = 0 For i = lFirstSht To lLastSht For Each rCell In .Sheets(i).Range(sRange) ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Next i End With Parse3DRange2 = aRange Else 'range isn't 3d, so just load each cell into array For Each rCell In rTemp.Cells ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Parse3DRange2 = aRange End If Parse3DRangeError: On Error GoTo 0 Exit Function End Function 'Parse3DRange Category: Excel Experts E-Letter | Comment (RSS) | Trackback |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif 3D
Joanne,
Make sure that your actual values return something other than zero (meybe, for example, you have an extra space somewhere). Use COUNTIF with the same parameters except for just one sheet: =COUNTIF('Blank 1'!A9:A10;A43) Or, if Blank 1 is a "Bookend" sheet, replace it with a sheet name that has data. HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Thank you, I made the change to the code you sugested. However when testing, although I now no longer get "#VALUE!", I get a zero when I know there should be 4. "Bernie Deitrick" wrote: Joanne, The Original code doesn't properly handle sheet names with spaces. Either rename your sheets to take out the spaces, or change Sheet2 = Trim(Mid$(sTemp, i + 1)) If i 0 Then Sheet1 = Trim(Left$(sTemp, i - 1)) to Sheet2 = Replace(Trim(Mid$(sTemp, i + 1)), "'", "") If i 0 Then Sheet1 = Replace(Trim(Left$(sTemp, i - 1)), "'", "") HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Hello again I know I've been posting a lot, but the workbook I'm currently working on has no end of problems, and is probably beyond my capabilities. But there, I have to do it! So I really appreciate any help that you could give me. I'm trying to use a UDF (that was posted a while ago, so I can't ask the author) that I found on: http://www.dailydoseofexcel.com/arch...ned-functions/ I want to Count every time a name appears in the same selection of cells across multiple worksheets. I have copied the code in, and plugged in the numbers but I get #VALUE! returned. My only theory is that some cells will be empty and that confuses excel? What am I doing wrong!?!? Thanks in advance! The formula I enter is: =CountIf3D2('Blank 1:Blank 2'!A9:A10;A43) The code relating to CountIf3D2 is as follows ( I think it also utilises the function Parse3DRange2, which is also pasted below): Function CountIf3D2(Range3D As String, Criteria As String) _ As Variant Dim i As Long Dim Count As Long Dim vaRng1 As Variant Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, Range3D) Count = 0 For i = LBound(vaRng1) To UBound(vaRng1) Count = Count + Application.WorksheetFunction.CountIf(vaRng1(i), Criteria) Next i CountIf3D2 = Count End Function Function Parse3DRange2(wb As Workbook, _ SheetsAndRange As String) As Variant Dim sTemp As String Dim i As Long, j As Long Dim Sheet1 As String, Sheet2 As String Dim aRange() As Range Dim sRange As String Dim lFirstSht As Long, lLastSht As Long Dim rCell As Range Dim rTemp As Range On Error GoTo Parse3DRangeError sTemp = SheetsAndRange 'if it's 3d, rtemp will be nothing On Error Resume Next Set rTemp = Range(sTemp) On Error GoTo Parse3DRangeError 'if it's 3d, parse it If rTemp Is Nothing Then i = InStr(sTemp, "!") If i = 0 Then Err.Raise 9999 'next line will generate an error if range is invalid 'if it's OK, it will be converted to absolute form sRange = Range(Mid$(sTemp, i + 1)).Address sTemp = Left$(sTemp, i - 1) i = InStr(sTemp, ":") Sheet2 = Trim(Mid$(sTemp, i + 1)) If i 0 Then Sheet1 = Trim(Left$(sTemp, i - 1)) Else Sheet1 = Sheet2 End If 'next lines will generate errors if sheet names are invalid With wb lFirstSht = .Worksheets(Sheet1).Index lLastSht = .Worksheets(Sheet2).Index 'swap if out of order If lFirstSht lLastSht Then i = lFirstSht lFirstSht = lLastSht lLastSht = i End If 'load each cell into an array j = 0 For i = lFirstSht To lLastSht For Each rCell In .Sheets(i).Range(sRange) ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Next i End With Parse3DRange2 = aRange Else 'range isn't 3d, so just load each cell into array For Each rCell In rTemp.Cells ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Parse3DRange2 = aRange End If Parse3DRangeError: On Error GoTo 0 Exit Function End Function 'Parse3DRange Category: Excel Experts E-Letter | Comment (RSS) | Trackback |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif 3D
Yes the range has a start/end bookend (Blank 1,Blank 2), I tried with just
one of the middle ones which does have data: =CountIf3D2("'Product 1'!A9:A10";"A43") And still recieve zero when I know there should be 2. (It would be very difficult for me to remove spaces in the names of worksheets because this is a template that will be used by many others. I have a macro that creates new tabs on a key command depending on something they enter in the summary sheet.) "Bernie Deitrick" wrote: Joanne, Make sure that your actual values return something other than zero (meybe, for example, you have an extra space somewhere). Use COUNTIF with the same parameters except for just one sheet: =COUNTIF('Blank 1'!A9:A10;A43) Or, if Blank 1 is a "Bookend" sheet, replace it with a sheet name that has data. HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Thank you, I made the change to the code you sugested. However when testing, although I now no longer get "#VALUE!", I get a zero when I know there should be 4. "Bernie Deitrick" wrote: Joanne, The Original code doesn't properly handle sheet names with spaces. Either rename your sheets to take out the spaces, or change Sheet2 = Trim(Mid$(sTemp, i + 1)) If i 0 Then Sheet1 = Trim(Left$(sTemp, i - 1)) to Sheet2 = Replace(Trim(Mid$(sTemp, i + 1)), "'", "") If i 0 Then Sheet1 = Replace(Trim(Left$(sTemp, i - 1)), "'", "") HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Hello again I know I've been posting a lot, but the workbook I'm currently working on has no end of problems, and is probably beyond my capabilities. But there, I have to do it! So I really appreciate any help that you could give me. I'm trying to use a UDF (that was posted a while ago, so I can't ask the author) that I found on: http://www.dailydoseofexcel.com/arch...ned-functions/ I want to Count every time a name appears in the same selection of cells across multiple worksheets. I have copied the code in, and plugged in the numbers but I get #VALUE! returned. My only theory is that some cells will be empty and that confuses excel? What am I doing wrong!?!? Thanks in advance! The formula I enter is: =CountIf3D2('Blank 1:Blank 2'!A9:A10;A43) The code relating to CountIf3D2 is as follows ( I think it also utilises the function Parse3DRange2, which is also pasted below): Function CountIf3D2(Range3D As String, Criteria As String) _ As Variant Dim i As Long Dim Count As Long Dim vaRng1 As Variant Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, Range3D) Count = 0 For i = LBound(vaRng1) To UBound(vaRng1) Count = Count + Application.WorksheetFunction.CountIf(vaRng1(i), Criteria) Next i CountIf3D2 = Count End Function Function Parse3DRange2(wb As Workbook, _ SheetsAndRange As String) As Variant Dim sTemp As String Dim i As Long, j As Long Dim Sheet1 As String, Sheet2 As String Dim aRange() As Range Dim sRange As String Dim lFirstSht As Long, lLastSht As Long Dim rCell As Range Dim rTemp As Range On Error GoTo Parse3DRangeError sTemp = SheetsAndRange 'if it's 3d, rtemp will be nothing On Error Resume Next Set rTemp = Range(sTemp) On Error GoTo Parse3DRangeError 'if it's 3d, parse it If rTemp Is Nothing Then i = InStr(sTemp, "!") If i = 0 Then Err.Raise 9999 'next line will generate an error if range is invalid 'if it's OK, it will be converted to absolute form sRange = Range(Mid$(sTemp, i + 1)).Address sTemp = Left$(sTemp, i - 1) i = InStr(sTemp, ":") Sheet2 = Trim(Mid$(sTemp, i + 1)) If i 0 Then Sheet1 = Trim(Left$(sTemp, i - 1)) Else Sheet1 = Sheet2 End If 'next lines will generate errors if sheet names are invalid With wb lFirstSht = .Worksheets(Sheet1).Index lLastSht = .Worksheets(Sheet2).Index 'swap if out of order If lFirstSht lLastSht Then i = lFirstSht lFirstSht = lLastSht lLastSht = i End If 'load each cell into an array j = 0 For i = lFirstSht To lLastSht For Each rCell In .Sheets(i).Range(sRange) ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Next i End With Parse3DRange2 = aRange Else 'range isn't 3d, so just load each cell into array For Each rCell In rTemp.Cells ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Parse3DRange2 = aRange End If Parse3DRangeError: On Error GoTo 0 Exit Function End Function 'Parse3DRange Category: Excel Experts E-Letter | Comment (RSS) | Trackback |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif 3D
Joanne,
A43 should not be in quotes, unless you area actually looking for the string "A43". If you are looking for the value that matches the string entered in cell A43, then use A43 without the quotes. HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Yes the range has a start/end bookend (Blank 1,Blank 2), I tried with just one of the middle ones which does have data: =CountIf3D2("'Product 1'!A9:A10";"A43") And still recieve zero when I know there should be 2. (It would be very difficult for me to remove spaces in the names of worksheets because this is a template that will be used by many others. I have a macro that creates new tabs on a key command depending on something they enter in the summary sheet.) "Bernie Deitrick" wrote: Joanne, Make sure that your actual values return something other than zero (meybe, for example, you have an extra space somewhere). Use COUNTIF with the same parameters except for just one sheet: =COUNTIF('Blank 1'!A9:A10;A43) Or, if Blank 1 is a "Bookend" sheet, replace it with a sheet name that has data. HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Thank you, I made the change to the code you sugested. However when testing, although I now no longer get "#VALUE!", I get a zero when I know there should be 4. "Bernie Deitrick" wrote: Joanne, The Original code doesn't properly handle sheet names with spaces. Either rename your sheets to take out the spaces, or change Sheet2 = Trim(Mid$(sTemp, i + 1)) If i 0 Then Sheet1 = Trim(Left$(sTemp, i - 1)) to Sheet2 = Replace(Trim(Mid$(sTemp, i + 1)), "'", "") If i 0 Then Sheet1 = Replace(Trim(Left$(sTemp, i - 1)), "'", "") HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Hello again I know I've been posting a lot, but the workbook I'm currently working on has no end of problems, and is probably beyond my capabilities. But there, I have to do it! So I really appreciate any help that you could give me. I'm trying to use a UDF (that was posted a while ago, so I can't ask the author) that I found on: http://www.dailydoseofexcel.com/arch...ned-functions/ I want to Count every time a name appears in the same selection of cells across multiple worksheets. I have copied the code in, and plugged in the numbers but I get #VALUE! returned. My only theory is that some cells will be empty and that confuses excel? What am I doing wrong!?!? Thanks in advance! The formula I enter is: =CountIf3D2('Blank 1:Blank 2'!A9:A10;A43) The code relating to CountIf3D2 is as follows ( I think it also utilises the function Parse3DRange2, which is also pasted below): Function CountIf3D2(Range3D As String, Criteria As String) _ As Variant Dim i As Long Dim Count As Long Dim vaRng1 As Variant Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, Range3D) Count = 0 For i = LBound(vaRng1) To UBound(vaRng1) Count = Count + Application.WorksheetFunction.CountIf(vaRng1(i), Criteria) Next i CountIf3D2 = Count End Function Function Parse3DRange2(wb As Workbook, _ SheetsAndRange As String) As Variant Dim sTemp As String Dim i As Long, j As Long Dim Sheet1 As String, Sheet2 As String Dim aRange() As Range Dim sRange As String Dim lFirstSht As Long, lLastSht As Long Dim rCell As Range Dim rTemp As Range On Error GoTo Parse3DRangeError sTemp = SheetsAndRange 'if it's 3d, rtemp will be nothing On Error Resume Next Set rTemp = Range(sTemp) On Error GoTo Parse3DRangeError 'if it's 3d, parse it If rTemp Is Nothing Then i = InStr(sTemp, "!") If i = 0 Then Err.Raise 9999 'next line will generate an error if range is invalid 'if it's OK, it will be converted to absolute form sRange = Range(Mid$(sTemp, i + 1)).Address sTemp = Left$(sTemp, i - 1) i = InStr(sTemp, ":") Sheet2 = Trim(Mid$(sTemp, i + 1)) If i 0 Then Sheet1 = Trim(Left$(sTemp, i - 1)) Else Sheet1 = Sheet2 End If 'next lines will generate errors if sheet names are invalid With wb lFirstSht = .Worksheets(Sheet1).Index lLastSht = .Worksheets(Sheet2).Index 'swap if out of order If lFirstSht lLastSht Then i = lFirstSht lFirstSht = lLastSht lLastSht = i End If 'load each cell into an array j = 0 For i = lFirstSht To lLastSht For Each rCell In .Sheets(i).Range(sRange) ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Next i End With Parse3DRange2 = aRange Else 'range isn't 3d, so just load each cell into array For Each rCell In rTemp.Cells ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Parse3DRange2 = aRange End If Parse3DRangeError: On Error GoTo 0 Exit Function End Function 'Parse3DRange Category: Excel Experts E-Letter | Comment (RSS) | Trackback |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif 3D
Perfect! It even works across the range of worksheets. I cannot tell you how
much this will help me, thank you very much! "Bernie Deitrick" wrote: Joanne, A43 should not be in quotes, unless you area actually looking for the string "A43". If you are looking for the value that matches the string entered in cell A43, then use A43 without the quotes. HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Yes the range has a start/end bookend (Blank 1,Blank 2), I tried with just one of the middle ones which does have data: =CountIf3D2("'Product 1'!A9:A10";"A43") And still recieve zero when I know there should be 2. (It would be very difficult for me to remove spaces in the names of worksheets because this is a template that will be used by many others. I have a macro that creates new tabs on a key command depending on something they enter in the summary sheet.) "Bernie Deitrick" wrote: Joanne, Make sure that your actual values return something other than zero (meybe, for example, you have an extra space somewhere). Use COUNTIF with the same parameters except for just one sheet: =COUNTIF('Blank 1'!A9:A10;A43) Or, if Blank 1 is a "Bookend" sheet, replace it with a sheet name that has data. HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Thank you, I made the change to the code you sugested. However when testing, although I now no longer get "#VALUE!", I get a zero when I know there should be 4. "Bernie Deitrick" wrote: Joanne, The Original code doesn't properly handle sheet names with spaces. Either rename your sheets to take out the spaces, or change Sheet2 = Trim(Mid$(sTemp, i + 1)) If i 0 Then Sheet1 = Trim(Left$(sTemp, i - 1)) to Sheet2 = Replace(Trim(Mid$(sTemp, i + 1)), "'", "") If i 0 Then Sheet1 = Replace(Trim(Left$(sTemp, i - 1)), "'", "") HTH, Bernie MS Excel MVP "Joanne" wrote in message ... Hello again I know I've been posting a lot, but the workbook I'm currently working on has no end of problems, and is probably beyond my capabilities. But there, I have to do it! So I really appreciate any help that you could give me. I'm trying to use a UDF (that was posted a while ago, so I can't ask the author) that I found on: http://www.dailydoseofexcel.com/arch...ned-functions/ I want to Count every time a name appears in the same selection of cells across multiple worksheets. I have copied the code in, and plugged in the numbers but I get #VALUE! returned. My only theory is that some cells will be empty and that confuses excel? What am I doing wrong!?!? Thanks in advance! The formula I enter is: =CountIf3D2('Blank 1:Blank 2'!A9:A10;A43) The code relating to CountIf3D2 is as follows ( I think it also utilises the function Parse3DRange2, which is also pasted below): Function CountIf3D2(Range3D As String, Criteria As String) _ As Variant Dim i As Long Dim Count As Long Dim vaRng1 As Variant Application.Volatile vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, Range3D) Count = 0 For i = LBound(vaRng1) To UBound(vaRng1) Count = Count + Application.WorksheetFunction.CountIf(vaRng1(i), Criteria) Next i CountIf3D2 = Count End Function Function Parse3DRange2(wb As Workbook, _ SheetsAndRange As String) As Variant Dim sTemp As String Dim i As Long, j As Long Dim Sheet1 As String, Sheet2 As String Dim aRange() As Range Dim sRange As String Dim lFirstSht As Long, lLastSht As Long Dim rCell As Range Dim rTemp As Range On Error GoTo Parse3DRangeError sTemp = SheetsAndRange 'if it's 3d, rtemp will be nothing On Error Resume Next Set rTemp = Range(sTemp) On Error GoTo Parse3DRangeError 'if it's 3d, parse it If rTemp Is Nothing Then i = InStr(sTemp, "!") If i = 0 Then Err.Raise 9999 'next line will generate an error if range is invalid 'if it's OK, it will be converted to absolute form sRange = Range(Mid$(sTemp, i + 1)).Address sTemp = Left$(sTemp, i - 1) i = InStr(sTemp, ":") Sheet2 = Trim(Mid$(sTemp, i + 1)) If i 0 Then Sheet1 = Trim(Left$(sTemp, i - 1)) Else Sheet1 = Sheet2 End If 'next lines will generate errors if sheet names are invalid With wb lFirstSht = .Worksheets(Sheet1).Index lLastSht = .Worksheets(Sheet2).Index 'swap if out of order If lFirstSht lLastSht Then i = lFirstSht lFirstSht = lLastSht lLastSht = i End If 'load each cell into an array j = 0 For i = lFirstSht To lLastSht For Each rCell In .Sheets(i).Range(sRange) ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Next i End With Parse3DRange2 = aRange Else 'range isn't 3d, so just load each cell into array For Each rCell In rTemp.Cells ReDim Preserve aRange(0 To j) Set aRange(j) = rCell j = j + 1 Next rCell Parse3DRange2 = aRange End If Parse3DRangeError: On Error GoTo 0 Exit Function End Function 'Parse3DRange Category: Excel Experts E-Letter | Comment (RSS) | Trackback |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CountIf | Excel Worksheet Functions | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |