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.

[VBA]
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 [/VBA]

[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]

[VBA]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
[/VBA]

[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.
[vba]

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

[/vba]

[satyen]

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

[VBA] 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 [/VBA]

[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:

[vba]
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
[/vba]

[satyen]

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