Solved: Adding pre-fixes

[satyen]

Can anyone help with this? I thought I could insert columns and do concatenations, as easy as this was because I have coded following code too look at column A , I thought to automate it like this may be easier in the long run. Can anyone help? Many thanks in advance.


Sub AddPrefixes()
Dim PreCol As Long 'or range?


For Each PreCol In Sheets("Database")


Select Case PreCol.Value
Case "a", "b", "c"

ActiveCell.Offset(1, 0).Select

Select Case "a"
' Everything under this col heading, uptil next column heading (not including next column heading) should be prefixed with "Test 1 -"

Select Case "b"
' Everything under this col heading, uptil next column heading (not including next column heading) should be prefixed with "Test 2 -"

Select Case "c"
' Everything under this col heading, uptil next column heading (not including next column heading) should be prefixed with "Test 3 -"


End Select

Next PreCol
End Sub

[lucas]

So you are looking at 3 different columns and want to add a prefix to everything in the columns except the header row?

[satyen]

It’s all in one column, not separate columns. Different header names in one column with data underneath it until the next header name. Thanks

[satyen]

can anyone help with this?

[mdmackillop]

Sub Prefix()
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
Select Case Cells(i, 1)
Case "a"
pre = "Test 1 _ "
Case "b"
pre = "Test 2 _ "
Case "c"
pre = "Test 3 _ "
Case Else
Cells(i, 1) = pre & Cells(i, 1)
End Select
Next
End Sub

[satyen]

this works perfectly, thank you, but just one little thing..
I have another column heading under after the data under "c". Would I create a case "d" or use the Case Else. Can you explain what the code in Case Else is doing please. Mind not functioning.

[satyen]

Also what is (pre)cant see it being defined anywhere and yet it still works?

[mdmackillop]

Careless of me!
You can add for further headers as required. Case else is adding the selected prefix to cells which do not contain a header identified in the Select Case code.


Sub Prefix()
Dim i as long
Dim Pre as string
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
Select Case Cells(i, 1)
Case "a"
pre = "Test 1 _ "
Case "b"
pre = "Test 2 _ "
Case "c"
pre = "Test 3 _ "
Case "d"
pre = "Test 4 _ "
Case "e"
pre = "Test 5_ "
Case Else
Cells(i, 1) = pre & Cells(i, 1)
End Select
Next
End Sub

[satyen]

thank you!
I did this for case "d", as i didnt want anything added for this header

Sub Prefix()

Dim i As Long
Dim Pre As String
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
Select Case Cells(i, 1)
Case "a"
Pre = "Test 1 _ "
Case "b"
Pre = "Test 2 _ "
Case "c"
Pre = "Test 3 _ "
Case "d"
Pre = ""

Case Else
Cells(i, 1) = Pre & Cells(i, 1)
End Select
Next
End Sub

[satyen]

Actually this did not work - sorry. I do not want anything to be done to anything else only those three headings. After the third heading there is info in the same column. There is a fourth heading after this but we must not touch it or data underneath. I have tried different options and it is not working as required. –Thank you

[SamT]

Try this:


Sub Prefix()

Dim i As Long
Dim HeadersDone As Boolean
HeadersDone = False
Dim Pre As String

For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
Select Case Cells(i, 1)
Case "a"
Pre = "Test 1 _ "
Case "b"
Pre = "Test 2 _ "
Case "c"
Pre = "Test 3 _ "
Case "d"
HeadersDone = True
Case Else
If Not HeadersDone Then
Cells(i, 1) = Pre & Cells(i, 1)
End IF

[satyen]

this worked a charm, thanks both for your help. have a great day!