Thanks for sharing the knowledge of VBA. I am growing more proficient, at least I hope, with this language. Now, I am asking specific scenarios that are not easy to look up in a Microsoft Excel files.
Here was my previous scenario:
I created a workbook that had 15 sheets of 1 formated Page.
Each sheets had 10 Rows; One cell in each row loaded a Userform that allowed the user to select using a ListBox.
I wanted and was guided to collect an instance in which a Function Code was used.
Ex. Row 1- Cell with Function Codes(2,4). I got the concatenation of the selected items into the cell and sorted.
Row 2 - Cell with Function Code (2,3,5).
I had wrote VBA code to count once the value used once.
I had a cells that contained Job function Codes Used and Job Functions Not Used.
Result using example above. (Assuming Range is 1-5)
Job Function Used : 2, 3, 4, 5
Job Function Not Used : 1
This was possible because all 15 sheets were formatting exactly the same, consequency, I could Loop through the sheets and according to the Cell.Address, I was able to Write Selected values in ListBox in a Worksheet cell.
$D$11 - Wrote Values Down column (AB1.value = 2 , AB2.Value =3)
$D$12 - Wrote Values Down Column (AC1.value = 2, AC2.Value =3, AC3.Value = 5).
Then I would Loop through the Worksheets and copy into one large cell matrix (Range),duplicate values were there and use Countif >0 for the Set Range for find instance of Job code and added it to Job Code Used.
New Scenario:
I have combined all the sheets(User Preference) to consolidate 15 sheets into 1 Worksheet Tab with 15 Page (Multiple copies) on that Sheet.
I no longer can use cell.address. Instead of 15 Pages of 10 row each, I have 150 rows on 1 page.
I do not know I select the appropriate condition (If then else) to collect all the values again. I tried ActiveCell.column and ActiveCell.Row Ranges as the condition but I only keeps the last change on that sheet when condition is met.
I know this is longwinded but if you need the code or the file to look at, It would be OK.
The main problem know is the condition in I go to a certain column, the Userform pops up with selection criteria.
I need to collect all the multiple values from each cell (from 360 rows + ...) into one column (preferable) or multiple column, sort, and write into cell only once the job codes used on the fly.
The big wrinkle is that there was 15 sheets of the same format, old version, using cell.address mind you; I had other pages with different information but the Userform was activated on column 5 on these pages. The cell.address was a neat way for this.
If you need the code or willing to take the attachment and look at it, I would be willing to send it out.
I just need to know if it is still possible without using some type of an array.
Thanks again for reading this. Help me!
Here was my previous scenario:
I created a workbook that had 15 sheets of 1 formated Page.
Each sheets had 10 Rows; One cell in each row loaded a Userform that allowed the user to select using a ListBox.
I wanted and was guided to collect an instance in which a Function Code was used.
Ex. Row 1- Cell with Function Codes(2,4). I got the concatenation of the selected items into the cell and sorted.
Row 2 - Cell with Function Code (2,3,5).
I had wrote VBA code to count once the value used once.
I had a cells that contained Job function Codes Used and Job Functions Not Used.
Result using example above. (Assuming Range is 1-5)
Job Function Used : 2, 3, 4, 5
Job Function Not Used : 1
This was possible because all 15 sheets were formatting exactly the same, consequency, I could Loop through the sheets and according to the Cell.Address, I was able to Write Selected values in ListBox in a Worksheet cell.
$D$11 - Wrote Values Down column (AB1.value = 2 , AB2.Value =3)
$D$12 - Wrote Values Down Column (AC1.value = 2, AC2.Value =3, AC3.Value = 5).
Then I would Loop through the Worksheets and copy into one large cell matrix (Range),duplicate values were there and use Countif >0 for the Set Range for find instance of Job code and added it to Job Code Used.
New Scenario:
I have combined all the sheets(User Preference) to consolidate 15 sheets into 1 Worksheet Tab with 15 Page (Multiple copies) on that Sheet.
I no longer can use cell.address. Instead of 15 Pages of 10 row each, I have 150 rows on 1 page.
I do not know I select the appropriate condition (If then else) to collect all the values again. I tried ActiveCell.column and ActiveCell.Row Ranges as the condition but I only keeps the last change on that sheet when condition is met.
I know this is longwinded but if you need the code or the file to look at, It would be OK.
The main problem know is the condition in I go to a certain column, the Userform pops up with selection criteria.
I need to collect all the multiple values from each cell (from 360 rows + ...) into one column (preferable) or multiple column, sort, and write into cell only once the job codes used on the fly.
The big wrinkle is that there was 15 sheets of the same format, old version, using cell.address mind you; I had other pages with different information but the Userform was activated on column 5 on these pages. The cell.address was a neat way for this.
If you need the code or willing to take the attachment and look at it, I would be willing to send it out.
I just need to know if it is still possible without using some type of an array.
Thanks again for reading this. Help me!
