Home |
Search |
Today's Posts |
#1
|
|||
|
|||
how do i prefix a column with letters
I am deigning a catalogue and want to prefix a column with 4 letters. How can
i do this? |
#2
|
|||
|
|||
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 "prefixing columns" wrote: I am deigning a catalogue and want to prefix a column with 4 letters. How can i do this? |
#3
|
|||
|
|||
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 "prefixing columns" wrote: I am deigning a catalogue and want to prefix a column with 4 letters. How can i do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
no row numbers or column letters DISPLAYED | Excel Discussion (Misc queries) | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
How do i change column headings back to letters in excel? | Excel Discussion (Misc queries) | |||
How do I change a column of cells to all capital letters? | Excel Worksheet Functions | |||
Excel column headings from numbers to letters | Excel Discussion (Misc queries) |