insert default value

[Guest]

For every cell in a coloumn that has a value I would like it to be preceded
by one given value. For example if values in a certain coloum were a,b,c,d .
I would like it to read HD:a , HD:b , HD:c , HD:d but the balnk cells must
reamin blank.

 

[JulieD]

For every cell in a coloumn that has a value I would like it to be
preceded
by one given value. For example if values in a certain coloum were a,b,c,d
.
I would like it to read HD:a , HD:b , HD:c , HD:d but the balnk cells must
reamin blank.

 

[Don Guillett]

try
Sub putleadingltr()
For Each c In Selection
If Len(c) > 0 Then c.Value = "HD:" & c
Next
End Sub

 

[Guest]

Ideally I would like to do an entire column's contents in 'one stroke of the
pen' if possible.

 

[Ken Wright]

Is your data all text? If so do you need the HD: bit to be real or will visual
only suffice. If visual is good enough then select column, do format / cells /
custom and use "HD:"@ as the custom format.

 

[Guest]

All the data is text, the HD: needs to show up only in the cells that have a
value. The reason being is the xl data is exported to a web database where
the HD: identifeies the property of the field. Values with nothing show
nothing.

 

[Ken Wright]

Ok then, just expanding on Don's answer a bit to use specialcells where you are
able, but looping through all if not:-

Sub LeadingLtr()

Dim rng As Range
Dim c As Range
Dim cnt As Long
Dim cntdat As Long

Set rng = Cells.SpecialCells(xlCellTypeConstants, 23)

If rng.Areas.Count > 1 Then
For Each c In rng
c.Value = "HD:" & c
Next
Else
Set rng = ActiveSheet.UsedRange
cnt = rng.Rows.Count * rng.Columns.Count
cntdat = 1
For Each c In rng
Application.StatusBar = "Approx " & Round((cntdat / cnt) * 100, 0) & "%
complete"
cntdat = cntdat + 1
If Len(c) > 0 Then c.Value = "HD:" & c
Next
Application.StatusBar = False
End If

End Sub

Special cells work a lot quicker than looping through the entire range, but you
have to be careful that if you have more than 8,192 non-contiguous areas and you
use the specialcells method it will return just one area and you won't want this
to be applied to all the blanks within that area, so the routine checks to see
how many areas you have, and if you actually have more than 8,192 Specialcells
will report that you only have 1, so it will default to looping, but if less
then it will use the specialcells method.