Assign title and subtitle number VBA

[mdelandro]

Hi,

I would like to run a code that runs through all cells in column C (Title/Subtitle), Looks for the word "Title" and assigns the correct number in column A (Title), the number being integers (1, 2, 3 , 4.....).
Then I would like to do the same thing but look for subtitle in cell C and assign decimals in cell A (1.1, 2.1, 2.2, 2, 3.1 ecc) as appropriate.

I have created an example below of what my final table should look like. Basically i want it to auto populate column A(Title)

I was wondering if anyone could help me with some pointers, lines of code or what to think about. I'm just starting with VBA so any help would be much appreciated

Thank you

Mario

​

A​	B​	C​
Item​	Description​	Title/Subtitle​
1​	Main title​	Title​
1.1​	subtitle​	Subtitle​
​	Text​	​
2​	Main Title​	Title​
2.1​	Subtitle​	Subtitle​
2.2​	Subtitle​	Subtitle​
3​	Title​	Title​
​	Text​	​

 

[Fluff]

Why not just a formula

+Fluff.xlsm
	A	B	C
1	Item	Description	Title/Subtitle
2	1	Main title	Title
3	1.1	subtitle	Subtitle
4		Text
5	2	Main Title	Title
6	2.1	Subtitle	Subtitle
7	2.2	Subtitle	Subtitle
8	3	Title	Title
9		Text
10
Data

Cell Formulas
Range	Formula
A2:A9	A2	=IF(C2="","",IF(C2="Title",COUNTIF(C$2:C2,C2),A1+0.1))

 

[mdelandro]

Why not just a formula

+Fluff.xlsm
	A	B	C
1	Item	Description	Title/Subtitle
2	1	Main title	Title
3	1.1	subtitle	Subtitle
4		Text
5	2	Main Title	Title
6	2.1	Subtitle	Subtitle
7	2.2	Subtitle	Subtitle
8	3	Title	Title
9		Text
10
Data

Cell Formulas
Range	Formula
A2:A9	A2	=IF(C2="","",IF(C2="Title",COUNTIF(C$2:C2,C2),A1+0.1))

Brilliant, thanks.
To be honest this is more of an exercise i was trying to solve for myself to learn VBA to get started with some simple examples, rather than a real life situation, and i hadn’t even considered a formula. However now that you mention it and show me how it can be done with a formula, i guess i can try and “translate” that to VBA code as an exercise.
Thank you

 

[Fluff]

You're welcome & thanks for the feedback.
To do it in VBA you would need the same logic, possibly using counters to keep track of what numbers you want

 

[Fluff]

To do it with a macro, you could use something like

Sub mdelandro()
   Dim Cl As Range
   Dim TitleCount As Long
   Dim SubtitleCount As Double
   
   For Each Cl In Range("C2", Range("C" & Rows.Count).End(xlUp))
      If Cl.Value = "Title" Then
         TitleCount = TitleCount + 1
         SubtitleCount = TitleCount
         Cl.Offset(, -2).Value = TitleCount
      ElseIf Cl.Value = "Subtitle" Then
         SubtitleCount = SubtitleCount + 0.1
         Cl.Offset(, -2).Value = SubtitleCount
      End If
   Next Cl
End Sub