Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,564
- Office Version
- 365
- 2016
- Platform
- Windows
I've run into a bit of a hurdle with some logic for some code:
I have a dynamic database, in my example, consisting of 46 rows (row 1 is header). variable lastrow holds the calculated number of rows in the database (46)
Column CW is a result of formula .range("CW" & rnum1) = .range("E" & rnum1) + .range("S" & rnum1). rnum1 is the row number of the current record being worked on. In this example, rnum1 = 2.
The value created will be semi-unique from the other records in the database, and will take a format of DRHPL1 (DR = .range("E2")), and (HPL1 = .range("S2))
This is where I am struggling. In column CX, I need to calculate how many times the value "DR"+that row's value of column S occurs in the entire column CW. In this example, CX2 = the number of occurrances of "DRHPL1" (DR+S2(HPL1) in range CW:CW. CX will be populated for each row in the database unique to it's corresponding value in CW.
Here is my failed attempt:
I hope I was able to explain myself clearly. It was awkward.
I have a dynamic database, in my example, consisting of 46 rows (row 1 is header). variable lastrow holds the calculated number of rows in the database (46)
Column CW is a result of formula .range("CW" & rnum1) = .range("E" & rnum1) + .range("S" & rnum1). rnum1 is the row number of the current record being worked on. In this example, rnum1 = 2.
The value created will be semi-unique from the other records in the database, and will take a format of DRHPL1 (DR = .range("E2")), and (HPL1 = .range("S2))
This is where I am struggling. In column CX, I need to calculate how many times the value "DR"+that row's value of column S occurs in the entire column CW. In this example, CX2 = the number of occurrances of "DRHPL1" (DR+S2(HPL1) in range CW:CW. CX will be populated for each row in the database unique to it's corresponding value in CW.
Here is my failed attempt:
Code:
lastrow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("CX2:CX" & lastrow) = Application.CountIf(.Range("CW:CW"), "DR" + .Range("S" & ???))
I hope I was able to explain myself clearly. It was awkward.