how do i prefix a column with letters

P

prefixing columns

I am deigning a catalogue and want to prefix a column with 4 letters. How can
i do this?
 
P

Peter Rooney

Hi,

You could paste something like this into the code window of the worksheet in
question (NOT a normal code module)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim NoOfCellsToFormat As Integer
NoOfCellsToFormat = Target.Value

On Error GoTo ws_exit:
Application.EnableEvents = False

If Target.Column = 1 Then
If Target.Row > 4 Then
If Target.Row < 11 Then
Target.Value = "ABCD" & Target.Formula
End If
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

The example above would prefix any entry you typed into A5 to A10 with "ABCD".
You can modify the IF statements to define any range of cells you want, as I
don't imagine you'd want to prefix everything - what about field headers for
example?

Hope this helps

Pete




once you'd
 
P

Peter Rooney

Here's a neater version that you can use to apply to a named range on your
worksheet - it's dynamic, so you don't need to keep changing the code to
refer to different rows and columns.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ModSheet As Worksheet
Set ModSheet = ActiveWorkbook.Worksheets("PrefixEntries")

Dim ModRange As Range
Set ModRange = ModSheet.Range("ModRange")

Dim TopLeft, TopRight, BottomLeft, BottomRight As Range
Set TopLeft = ModRange(1, 1)
Set TopRight = ModRange(1, ModRange.Columns.Count)
Set BottomLeft = ModRange(ModRange.Rows.Count, 1)
Set BottomRight = ModRange(ModRange.Rows.Count, ModRange.Columns.Count)

On Error GoTo ws_exit:

Application.EnableEvents = False

If Target.Column >= TopLeft.Column Then
If Target.Column <= TopRight.Column Then
If Target.Row >= TopLeft.Row Then
If Target.Row <= BottomLeft.Row Then
Target.Value = "XYZ" & Target.Formula
End If
End If
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub
 
Top