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

 

[Fluff]

but for some reason it gets applied to hidden cells as well.

Because there was no mention in your OP about ignoring hidden rows, just like there was no mention of an extra column that you added in post#6.
In future please post ALL information, rather than adding things bit by bit.
Is there anything else that you have "forgotten" to mention?

 

[M_S]

Because there was no mention in your OP about ignoring hidden rows, just like there was no mention of an extra column that you added in post#6.
In future please post ALL information, rather than adding things bit by bit.
Is there anything else that you have "forgotten" to mention?

Nope, that'd be all. Sorry about that!

 

[Fluff]

Ok, 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 Cl.EntireRow.Hidden Then
            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)
         End If
      Next Cl
   End With
End Sub

 

[M_S]

Ok, 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 Cl.EntireRow.Hidden Then
            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)
         End If
      Next Cl
   End With
End Sub

Thank you very much!

For some reason, it keeps highlighting "End if":

 

[Fluff]

Do you get an error message?

 

[M_S]

Do you get an error message?

No error message, but if I'm in VBA and press Fn and F8, it keeps switching to "If Not Cl" and "End if" and doesn't move to next line.

 

[Fluff]

It will do that if the row is hidden.