Home |
Search |
Today's Posts |
#1
|
|||
|
|||
insert default value
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. |
#2
|
|||
|
|||
"HSFJKB" wrote in message ... 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. |
#3
|
|||
|
|||
try
Sub putleadingltr() For Each c In Selection If Len(c) 0 Then c.Value = "HD:" & c Next End Sub -- Don Guillett SalesAid Software "HSFJKB" wrote in message ... 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. |
#4
|
|||
|
|||
Ideally I would like to do an entire column's contents in 'one stroke of the
pen' if possible. "Don Guillett" wrote: try Sub putleadingltr() For Each c In Selection If Len(c) 0 Then c.Value = "HD:" & c Next End Sub -- Don Guillett SalesAid Software "HSFJKB" wrote in message ... 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. |
#5
|
|||
|
|||
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. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "HSFJKB" wrote in message ... 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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.789 / Virus Database: 534 - Release Date: 07/11/2004 |
#6
|
|||
|
|||
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" wrote: 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. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "HSFJKB" wrote in message ... 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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.789 / Virus Database: 534 - Release Date: 07/11/2004 |
#7
|
|||
|
|||
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. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "HSFJKB" wrote in message ... 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" wrote: 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. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "HSFJKB" wrote in message ... 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. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.789 / Virus Database: 534 - Release Date: 07/11/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.789 / Virus Database: 534 - Release Date: 07/11/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Make default insert sheet to have grid lines | New Users to Excel | |||
Challenging Charting | Charts and Charting in Excel | |||
change the default word in the insert comment option in excel | Setting up and Configuration of Excel | |||
How do I change the default color of the tag for insert c... | Excel Discussion (Misc queries) | |||
How do I change the default color of the tag for insert c... | Excel Discussion (Misc queries) |