Excel VBA Help

[M_S]

Hello Everyone,

I need some help with the data below. So, I'm trying to assign numbers to the Values in Column B. For example ,if Column B has Apple in it and I assign it 22, I want the next item on the list to be 23 and so on. Same items should have same value, as you can see in Column A.

How can I achieve this using VBA? Any help would be much appreciated! Thanks!

Column A	Column B
22	Apple
22	Apple
22	Apple
23	Banana
23	Banana
22	Apple
23	Banana
24	Orange
24	Orange
23	Banana
22	Apple

 

[Dan_W]

Does it need to be in VBA - you can accomplish the same goal with a formula - as below...

Book1
	A	B
1	Column A	Column B
2	22	apple
3	22	apple
4	22	apple
5	23	banana
6	23	banana
7	22	apple
8	23	banana
9	24	orange
10	24	orange
11	23	banana
12	22	apple
13	25	watermelon
14	22	apple
15	23	banana
16	23	banana
17	25	watermelon
18	26	strawberry
19	27	raspberries
20	25	watermelon
21	26	strawberry
22	22	apple
23	22	apple
24	23	orange
25	24	orange
Sheet1

Cell Formulas
Range	Formula
A3:A25	A3	=IF(COUNTIF($B$2:$B2,B3)>0,INDEX($A$2:$B2,MATCH(B3,$B$2:$B2),1),MAX($A$2:$A2)+1)

It may not be a particularly elegant formula, and it requires a starting number for the first item (see A2 = 22), but other than that it seems to work.

 

[M_S]

Does it need to be in VBA - you can accomplish the same goal with a formula - as below...

Book1
	A	B
1	Column A	Column B
2	22	apple
3	22	apple
4	22	apple
5	23	banana
6	23	banana
7	22	apple
8	23	banana
9	24	orange
10	24	orange
11	23	banana
12	22	apple
13	25	watermelon
14	22	apple
15	23	banana
16	23	banana
17	25	watermelon
18	26	strawberry
19	27	raspberries
20	25	watermelon
21	26	strawberry
22	22	apple
23	22	apple
24	23	orange
25	24	orange
Sheet1

Cell Formulas
Range	Formula
A3:A25	A3	=IF(COUNTIF($B$2:$B2,B3)>0,INDEX($A$2:$B2,MATCH(B3,$B$2:$B2),1),MAX($A$2:$A2)+1)

It may not be a particularly elegant formula, and it requires a starting number for the first item (see A2 = 22), but other than that it seems to work.

Thank you, Dan. Yes, I was actually looking for something in VBA.

Thanks for you help tho, appreciate it!

 

[Dan_W]

Curious - what do you need it for? I may be able to help if you can give some more information.

 

[Fluff]

How about

Sub M_S()
   Dim Cl As Range
   Dim StartNo As Variant
   
   StartNo = InputBox("Please enter a start number")
   If StartNo = "" Then Exit Sub
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then .Add Cl.Value, StartNo + .Count
         Cl.Offset(, -1).Value = .Item(Cl.Value)
      Next Cl
   End With
End Sub

 

[M_S]

How about

Sub M_S()
   Dim Cl As Range
   Dim StartNo As Variant
  
   StartNo = InputBox("Please enter a start number")
   If StartNo = "" Then Exit Sub
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then .Add Cl.Value, StartNo + .Count
         Cl.Offset(, -1).Value = .Item(Cl.Value)
      Next Cl
   End With
End Sub

Thank you so much! It worked like Magic, you're awesome!
Is it possible, if there's another column "C" and I want to match if it falls in the same category. So, everything in Bucket 1 will have a series and then Bucket 2 will have continuation of the series, regardless if column B has same value?

Column A	Column B	Column C
1	Apple	Bucket 1
1	Apple	Bucket 1
2	Banana	Bucket 1
3	Orange	Bucket 1
4	Orange	Bucket 2
5	Apple	Bucket 2
5	Apple	Bucket 2
6	Banana	Bucket 3
7	Apple	Bucket 3
8	Orange	Bucket 4
8	Orange	Bucket 4
9	Apple	Bucket 4
10	Banana	Bucket 4

 

[Fluff]

How about

Sub M_S()
   Dim Cl As Range
   Dim StartNo As Variant
   
   StartNo = InputBox("Please enter a start number")
   If StartNo = "" Then Exit Sub
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value & "|" & Cl.Offset(, 1).Value) Then .Add Cl.Value & "|" & Cl.Offset(, 1).Value, StartNo + .Count
         Cl.Offset(, -1).Value = .Item(Cl.Value & "|" & Cl.Offset(, 1).Value)
      Next Cl
   End With
End Sub

 

[M_S]

How about

Sub M_S()
   Dim Cl As Range
   Dim StartNo As Variant
  
   StartNo = InputBox("Please enter a start number")
   If StartNo = "" Then Exit Sub
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value & "|" & Cl.Offset(, 1).Value) Then .Add Cl.Value & "|" & Cl.Offset(, 1).Value, StartNo + .Count
         Cl.Offset(, -1).Value = .Item(Cl.Value & "|" & Cl.Offset(, 1).Value)
      Next Cl
   End With
End Sub

This didn't work. Does it have anything to do with "|"?

 

[M_S]

This didn't work. Does it have anything to do with "|"?

It actually did work! Thanks a heap! I'm out of words.

 

[M_S]

It actually did work! Thanks a heap! I'm out of words.

Is there a way to apply this formula to only visible cells? I have a filter on in another column but for some reason it gets applied to hidden cells as well.