I was able to get the following VBA code to successfully work when there was only one worksheet involved. Unfortunately, once I added a second worksheet, the code no longer worked. However, I think I may understand the problem, I just don't know how to fix it.
When I had only one worksheet (named CommonData), the code below worked perfectly. I believe that's because any reference to cells or range of cells contained within the VBA code had only one worksheet to refer to.
Once I added a second worksheet (named Sheet1) and placed that worksheet in the first position, then the code below seem to referred to Sheet1 by default because when the code was run the results reflected the numbers contained with the ranges within the Sheet1 worksheet. Hope that makes sense.
What I'm trying to do is get the following code to refer to the CommonData worksheet. My first thought was to put Worksheets("CommonData").Range in front of the cell/range references in the code below but that didn't work for me so I must be messing up that code/logic somehow. Because I'm dealing with VBA, I'm getting myself a little confused with regards to where and when to appropriately use the "" to make this work.
If someone would be so kind as to provide some guidance on how to resolve this problem, I would very much appreciate it.
When I had only one worksheet (named CommonData), the code below worked perfectly. I believe that's because any reference to cells or range of cells contained within the VBA code had only one worksheet to refer to.
Once I added a second worksheet (named Sheet1) and placed that worksheet in the first position, then the code below seem to referred to Sheet1 by default because when the code was run the results reflected the numbers contained with the ranges within the Sheet1 worksheet. Hope that makes sense.
What I'm trying to do is get the following code to refer to the CommonData worksheet. My first thought was to put Worksheets("CommonData").Range in front of the cell/range references in the code below but that didn't work for me so I must be messing up that code/logic somehow. Because I'm dealing with VBA, I'm getting myself a little confused with regards to where and when to appropriately use the "" to make this work.
If someone would be so kind as to provide some guidance on how to resolve this problem, I would very much appreciate it.
VBA Code:
Max1 = Evaluate("=LARGE(E5:INDEX(E5:X24," & n & "," & n & "),1)")
Max2 = Evaluate( _
"=MAX(IF(E5:INDEX(E5:X24," & n & "," & n & ")<" & Max1 & ",E5:INDEX(E5:X24," & n & "," & n & ")))")
