Excel VBA Help

M_S

New Member
Joined
Aug 6, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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 AColumn B
22Apple
22Apple
22Apple
23Banana
23Banana
22Apple
23Banana
24Orange
24Orange
23Banana
22Apple
 
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?
 
Upvote 0

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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!
 
Upvote 0
Ok, how about
VBA Code:
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
 
Upvote 0
Ok, how about
VBA Code:
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":

1628640676843.png
 
Upvote 0
It will do that if the row is hidden.
 
Upvote 0

Forum statistics

Threads
1,223,244
Messages
6,170,976
Members
452,372
Latest member
Natalie18

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top