danlu
08-07-2008, 01:41 PM
Hi,
I have a code which is intended to first filter out all unique values from a sheet and paste these values into sheet2.
Then I want to determine the size of this range where the unique values where pasted. For every value within this range it should step through each value and count how many times each such value occurs on the original sheet (that is, the sheet from where the unique values were extracted from)
Though I have not managed to resize the range, called malomrade, properly to make it only contain cells which contains a unique value, and hence make the For Each c mechanism step through only the cells which actually contains values and make a count for every such cell. Any ideas how this is done? I have below marked it something?? to indicate where I have a hard time coming up with how to VBA code it. Of course maybe it can also be done in an alternate way, all ideas are welcome.
Sub test()
Dim malomrade As Range
Dim c As Range
Set malomrade = Sheets(2).Range("A1:A100")
Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:= _
malomrade, Unique:=True
Set malomrade = malomrade.Resize(something??.Rows.Count)
For Each c In malomrade
c.Offset(0, 1) = WorksheetFunction.CountIf(Worksheets(1).Range("A:A"), c.Value)
Next c
End Sub
I want it to look as it does in the attached file on sheet2. (Though there I have manually erased the values in column B for values from row 19 and below since it goes through down to row 100 because malomrade is as described above no resized properly).
On sheet1 is the intitial values from where the unique values are extracted and from where the count should be made in the last step of the code.
I have a code which is intended to first filter out all unique values from a sheet and paste these values into sheet2.
Then I want to determine the size of this range where the unique values where pasted. For every value within this range it should step through each value and count how many times each such value occurs on the original sheet (that is, the sheet from where the unique values were extracted from)
Though I have not managed to resize the range, called malomrade, properly to make it only contain cells which contains a unique value, and hence make the For Each c mechanism step through only the cells which actually contains values and make a count for every such cell. Any ideas how this is done? I have below marked it something?? to indicate where I have a hard time coming up with how to VBA code it. Of course maybe it can also be done in an alternate way, all ideas are welcome.
Sub test()
Dim malomrade As Range
Dim c As Range
Set malomrade = Sheets(2).Range("A1:A100")
Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:= _
malomrade, Unique:=True
Set malomrade = malomrade.Resize(something??.Rows.Count)
For Each c In malomrade
c.Offset(0, 1) = WorksheetFunction.CountIf(Worksheets(1).Range("A:A"), c.Value)
Next c
End Sub
I want it to look as it does in the attached file on sheet2. (Though there I have manually erased the values in column B for values from row 19 and below since it goes through down to row 100 because malomrade is as described above no resized properly).
On sheet1 is the intitial values from where the unique values are extracted and from where the count should be made in the last step of the code.