Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
HSFJKB
 
Posts: n/a
Default 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   Report Post  
JulieD
 
Posts: n/a
Default


"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   Report Post  
HSFJKB
 
Posts: n/a
Default

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   Report Post  
Ken Wright
 
Posts: n/a
Default

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   Report Post  
HSFJKB
 
Posts: n/a
Default

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   Report Post  
Ken Wright
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Make default insert sheet to have grid lines Bruce Roberson New Users to Excel 4 January 20th 05 01:27 AM
Challenging Charting C TO Charts and Charting in Excel 0 January 17th 05 07:57 PM
change the default word in the insert comment option in excel excel Setting up and Configuration of Excel 2 December 24th 04 01:51 PM
How do I change the default color of the tag for insert c... [email protected] Excel Discussion (Misc queries) 1 December 18th 04 12:01 AM
How do I change the default color of the tag for insert c... ntwrkengnr Excel Discussion (Misc queries) 1 December 17th 04 07:34 PM


All times are GMT +1. The time now is 01:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"