I cannot for the life of me figure out why I am getting a mismatch in the following code. I'm trying to have it just count the number of cells in a range with two digits. I highlighted the spot where I'm getting a mismatch error:
Thanks in advance for the help!Code:lastrow = foswork.Cells(foswork.Rows.Count, 2).End(xlUp).row For x = 1 To 5 Set rng = foswork.Range(foswork.Cells(1, x + 5), foswork.Cells(lastrow, x + 5)) staffing.Cells(x + 4, (3 * i) - 1) = _ Application.WorksheetFunction.SumProduct(--(Len(rng) = 2)) - _ Application.WorksheetFunction.CountIfs(rng, "SB") Next x
Using Windows 10, Excel 2013.
Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments
SUMPRODUCT works differently in VBA
Try this
Hope this helpsCode:lastrow = foswork.Cells(foswork.Rows.Count, 2).End(xlUp).Row For x = 1 To 5 Set rng = foswork.Range(foswork.Cells(1, x + 5), foswork.Cells(lastrow, x + 5)) staffing.Cells(x + 4, (3 * i) - 1) = _ Evaluate("=SUMPRODUCT(--(Len(" & rng.Address & ") = 2))") - _ Application.WorksheetFunction.CountIfs(rng, "SB") Next x
M.
Rick's "mini" blog... http://www.excelfox.com/forum/f22/
.
Want to post a small screen shot? See Part B here.
This gave me a value of 0. (which is not correct)
This just put a #VALUE in the cell.
If it helps at all, the column that rng is referencing contains combinations of two letters as well as numbers (eg. TL, EX, 24, SB). In this example, if rng was only referencing those 4, i would want the formula to return a 3 in the cell (count all cells with 2 digits except for "SB")
Whenever I have trouble mimicing a worksheet formula in VBA.
I find it easiest to first create a working version of the formula in a regular cell formula.
Then go from there.
Can you post a regular formula that you would put in a cell that returns the desired value?
Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
Find a link in post number 31
The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Ferris Bueller A.K.A. John Hughes, 1986
However, I think the issue with Marcelo's code is that rng.Address does not include the SHEET reference
and rng is built using the sheet foswork...
Try
Code:lastrow = foswork.Cells(foswork.Rows.Count, 2).End(xlUp).Row For x = 1 To 5 Set rng = foswork.Range(foswork.Cells(1, x + 5), foswork.Cells(lastrow, x + 5)) staffing.Cells(x + 4, (3 * i) - 1) = _ Evaluate("=SUMPRODUCT(--(Len('" foswork.Name & "'!" & rng.Address & ") = 2))") - _ Application.WorksheetFunction.CountIfs(rng, "SB") Next x
Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
Find a link in post number 31
The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Ferris Bueller A.K.A. John Hughes, 1986
Rick's "mini" blog... http://www.excelfox.com/forum/f22/
.
Want to post a small screen shot? See Part B here.
Like this thread? Share it with others