You're welcome.
Posts by Fluff13
-
-
Glad we could help & thanks for the feedback.
-
If it's going in as a string, make sure the cell format is set to general before running the code.
-
Both Bosco's suggestion & mine work for me.
-
What version of Excel are you using? Or are you using a different spreadsheet app?
Also can you please supply a sample workbook.
-
Why bother using R1C1 notation if the previous code worked?
-
How about
=IF(LOOKUP(2,1/(C14:C37<>""),C14:C37)>10,1,LOOKUP(2,1/(C14:C37<>""),C14:C37)+1)
-
You're welcome & thanks for the feedback.
-
-
You're welcome & thanks for the feedback.
-
You need to qualify the cells portion as well, otherwise they look at the activesheet.
-
-
How about
Display MoreCode- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim Cl As Range, Rng As Range
- If Not Intersect(Target, Range("D4:D4000,G4:G4000")) Is Nothing Then
- On Error Resume Next
- Set Rng = Range("test")
- On Error GoTo 0
- If Rng Is Nothing Then Exit Sub
- '-----------Counting No. of unique values in named range :'test'-----------------------
- With CreateObject("scripting.dictionary")
- For Each Cl In Range("test")
- .Item(Cl.Value) = .Item(Cl.Value) + Cl.Offset(, 3).Value
- Next Cl
- Application.EnableEvents = False
- Sheet1.Range("k2") = .Count
- Sheet1.Range("K7:l" & Rows.Count).ClearContents
- Sheet1.Range("K7").Resize(.Count, 2).Value = Application.Transpose(Array(.keys, .items))
- Application.EnableEvents = True
- End With
- End If
- End Sub
-
There is nothing in col A, which is why you get the #
-
How about
Display MoreCode- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim Cl As Range, Rng As Range
- On Error Resume Next
- Set Rng = Range("test")
- On Error GoTo 0
- If Rng Is Nothing Then Exit Sub
- '-----------Counting No. of unique values in named range :'test'-----------------------
- With CreateObject("scripting.dictionary")
- For Each Cl In Range("test")
- .Item(Cl.Value) = .Item(Cl.Value) + Cl.Offset(, 3).Value
- Next Cl
- Application.EnableEvents = False
- Sheet1.Range("k2") = .Count
- Sheet1.Range("K7:l" & Rows.Count).ClearContents
- Sheet1.Range("K7").Resize(.Count, 2).Value = Application.Transpose(Array(.keys, .items))
- Application.EnableEvents = True
- End With
- End Sub
-
Whilst you were using a collection just to count the number of unique values, I used a dictionary to store those numbers & add the price, then printed out the dictionary at the end.
For more info on dictionaries have a look here
-
Ok, how about
Display MoreCode- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim Cl As Range
- '-----------Counting No. of unique values in named range :'test'-----------------------
- With CreateObject("scripting.dictionary")
- For Each Cl In Range("test")
- .Item(Cl.Value) = .Item(Cl.Value) + Cl.Offset(, 3).Value
- Next Cl
- Application.EnableEvents = False
- Sheet1.Range("k2") = .Count
- Sheet1.Range("K7").Resize(.Count, 2).Value = Application.Transpose(Array(.keys, .items))
- Application.EnableEvents = True
- End With
- End Sub
-
Unfortunately I don't know why that is happening as I don't get any problems.
-
Do you want to sum the price for duplicated items?
-
Thanks for that, however it's working quite happily for me.
When you get the error, check that the sheet is not protected.