Alternative suggestions to this select case code?

Microsoft Office 2013 professional produ...
April 30, 2016 at 09:27:43
Specs: Windows 8.1
Hello all

I have a macro that copies all the sheets from two workbooks into a new workbook. That is all good and working. Each sheet contains x-number of rows with items in them. Each of those items has a serial number attached to it. Some of those items are duplets and I want to count the number of occurrences across all of the worksheets in that combined workbook and display it in the column next to the item being tested.

The way I have done it at the moment is by using a code that Counts the number of worksheets and then selects a given case corresponding to that number of worksheets.

When the case is selected, it puts in a formula like this in all the U-colomns in each worksheet:

=COUNTIF('Sheet1'!T:T;T1)+COUNTIF('Sheet2'!T:T;T1)+COUNTIF('Sheet3'!T:T;T1)

This gives me the correct number of occurrences, but it is not very easy to maintaine. As you all surely can see, the above formula that gets inserted is if case 3 is selected. The problem is that I have up to 20 cases, where I have 20 countif-functions added together.

Any suggestions to how to make this simpler? I know there must be a way. It doesnt have to put in a formula at all in that range("T:T"), the result of the above formula is all that I need.

Thanks in advance

/Jacob

message edited by JacobJ


See More: Alternative suggestions to this select case code?

Report •

#1
April 30, 2016 at 11:58:11
Try this,

=SUMPRODUCT(COUNTIF(INDIRECT("'"&"Sheet"&{1,2,3}&"'!T:T"),T1))

Only tested it out a bit, so no guarantees.

MIKE

http://www.skeptic.com/


Report •

#2
April 30, 2016 at 18:26:48
If all you need is the result of the COUNTIF, how about something like the following suggestion?

I don't know if this will fit your exact needs, but it's the loop concept that's important. Modify as required.

Sub Count_TT()
   For shtNum = 1 To Sheets.Count
    myCount = _
     myCount + WorksheetFunction.CountIf(Sheets(shtNum).Range("T:T"), Range("T1"))
   Next
'Substitute your use of the answer here
    MsgBox myCount
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
April 30, 2016 at 23:23:19
Thanks for the answers. The solution you give derbydad03 is sort of what I need. But it only gives the answer for the value in T1 and not the Whole range Down in column T.

I think I need a loop within that loop, that does this counting for every cell in that list of items in column T on each sheet. But I am a bit stuck after I have calculated the number of times needed to run the loop:

  For shtNum = 1 To Sheets.Count
   For singlecell = 1 To Sheets(shtNum).Range("B" & Rows.Count).End(xlUp).Row
    myCount = _
     myCount + WorksheetFunction.CountIf(Sheets(shtNum).Range("T:T"), Range("T1"))
        Sheets(shtNum).Range("U1").Value = myCount
   Next singlecell
  Next shtNum

I think that something has to be done in the range("T1") and range("U1") but not sure how to loop those ranges..


Report •

Related Solutions

#4
May 1, 2016 at 07:18:51
First, a posting tip related to posting VBA code in this forum. You may have noticed that I edited your post so that the VBA code fits within the borders of the post frame. If the code contained within the pre tags extends beyond the right hand border, it causes the rest of the text (the non-code text) to also extend beyond the right hand border. That means that readers have to use the horizontal scroll bar to move the frame back and forth in order to read what was posted. (It's nothing that you are doing "wrong", it's just a limitation of the text editor in this forum). By using the Line Continuation character within the code, you can keep the code within the frame but also be able to Paste the code right into the VBA editor.

OK, that said, I'm a little confused by your requirements. Please keep in mind that we can't see your workbook from where we're sitting so, so we're at a bit of a disadvantage.

I think that you want to COUNTIF T1, then T2, then T3, placing those values in U1, then U2 then U3, respectively. I also think that you want to do this on each sheet individually. In other words, you are not aggregating Column T values across all sheets.

If that's the case, then try the code suggested below. If that's not the case, then you may need to post some example data, along with the expected results based on that data so we can fully understand what you are trying to do.

Sub Count_TT()
'Loop through all sheets
  For shtNum = 1 To Sheets.Count
'Loop through Column T based on length of Column B
   For singlecell = 1 To Sheets(shtNum).Range("B" & Rows.Count).End(xlUp).Row
'COUNTIF individual values within Column T of current sheet
    With Sheets(shtNum)
      myCount = _
        WorksheetFunction.CountIf(.Range("T:T"), .Range("T" & singlecell))
'Place result in corresponding Row in Column U of current sheet
        .Range("U" & singlecell).Value = myCount
    End With
'Reset mycount variable
     myCount = 0
   Next singlecell
  Next shtNum
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
May 8, 2016 at 06:35:41
Hello again. I am sorry to say, that this isnt the case. My data is like this.

The data I can export carries "old" data with it. That Means, that if some data was present the first time I exported the data, it will also be present the next time, plus the new items that has been added.

I am only interested in those new data, therefore I am at the moment doing the following:

When I export my data a counting column gets placed in every data set. This simply counts the number of times a given item is present.

I then merge two (and sometimes more) sets of data. Each set of data is put into individual sheets in the same workbook. Now my idea is to count each item accross both(all) sheets and see how many a given item there is present now. If the counting results in twice as many numbers of the given item, that means there has been no addition to this item. If the number is more than twice as much, then items has been added.

Therefore counting how many of a given item there is across all sheets is needed and the results has to be displayed just as it is done in the code you have posted above.


Report •

#6
May 8, 2016 at 18:17:56
I'm not sure if you are saying that the code I offered works for you or not.

First you say:

"I am sorry to say, that this isn't the case."

Then you say:

"Therefore counting how many of a given item there is across all sheets is needed and the results has to be displayed just as it is done in the code you have posted above."

The first part sounds like my solution doesn't work, but the second part makes it sound like it does work.

At this point I'm confused as to whether you need more help or not. If you do, you'll have to tell me what my code doesn't do that you need it to do.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
May 11, 2016 at 12:06:46
Hello Again

Your code Works very nicely for counting the items on each seperate sheet, but the problem is, that I need it to Count across multiple sheets, not just add a countif to Count only on one sheet. For the following examples I have modified the "ranges" in your code as followed.

Sub Count_TT()
'Loop through all sheets
  For shtNum = 1 To Sheets.Count
'Loop through Column T based on length of Column A
   For singlecell = 1 To Sheets(shtNum).Range("A" & Rows.Count).End(xlUp).Row
'COUNTIF individual values within Column B of current sheet
    With Sheets(shtNum)
      myCount = _
        WorksheetFunction.CountIf(.Range("B:B"), .Range("B" & singlecell))
'Place result in corresponding Row in Column C of current sheet
        .Range("C" & singlecell).Value = myCount
    End With
'Reset mycount variable
     myCount = 0
   Next singlecell
  Next shtNum
End Sub


The results are displayed in column C, what I need is in column D:

	Sheet 1			
	A	B	C	D
1	1	a	2	4
2	2	d	1	2
3	3	e	1	1
4	4	c	1	3
5	5	b	2	4
6	6	a	2	4
7	7	b	2	4

	Sheet 2			
	A	B	C	D
1	1	a	2	4
2	2	d	1	2
3	3	b	2	4
4	4	a	2	4
5	5	b	2	4
6	6	c	2	3
7	7	c	2	3

The results in column D is achieved by these two formulas filled down:

Sheet1: =COUNTIF(B:B,B1)+TÆL.HVIS('sheet2'!B:B,B1)
Sheet2: =COUNTIF(B:B,B1)+TÆL.HVIS('sheet1'!B:B,B1)

As you can see, the item "a" is present 2 times on sheet1 and 2 times on sheet2, so it is present 4 times when counted across both sheets.

I hope that is a more clear description.


Report •

#8
May 11, 2016 at 13:06:39
Sub Count_TT()
'Loop through all sheets to Count Items and place values
  For destSht = 1 To Sheets.Count
'Determine number of items to count on current sheet
   lstRw = Sheets(destSht).Range("A" & Rows.Count).End(xlUp).Row
'Loop through items on current sheet
    For myItem = 1 To lstRw
'Loop through all sheets, counting current item
     For srcSht = 1 To Sheets.Count
       myCount = myCount + _
                 WorksheetFunction.CountIf(Sheets(srcSht).Range("B:B"), _
                 Sheets(destSht).Range("B" & myItem))
     Next
'Place Count on current sheet
     Sheets(destSht).Range("C" & myItem) = myCount
'Reset counter
     myCount = 0
'Count next item on current sheet
    Next myItem
'Move on to next sheet
   Next destSht
 End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Ask Question