PDA

View Full Version : Solved: Adding pre-fixes



satyen
03-29-2010, 08:02 AM
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
03-29-2010, 08:45 AM
So you are looking at 3 different columns and want to add a prefix to everything in the columns except the header row?

satyen
03-29-2010, 08:49 AM
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
03-29-2010, 11:33 AM
can anyone help with this?

mdmackillop
03-29-2010, 01:37 PM
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
03-29-2010, 02:29 PM
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
03-29-2010, 02:48 PM
Also what is (pre)cant see it being defined anywhere and yet it still works?

mdmackillop
03-29-2010, 03:04 PM
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
03-29-2010, 11:05 PM
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
03-30-2010, 07:28 AM
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
03-30-2010, 07:47 AM
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
03-31-2010, 12:31 AM
this worked a charm, thanks both for your help. have a great day!