Insert a quote mark, then repeat

[sam]

Hi -

This used to be so easy in Lotus!

I want to convert values to labels in a column of 50 cells. Best way to do
so is to insert a quote mark at the beginning of every cell. Attempted to
create a relative macro to do so by: F2 (edit) R1C1\Home\' \Stop. Assigned it
Control Q. Since I have no clue how to make a macro repeat (downwards), I
thought I would just arrow down to the next cell manually, and then Control Q.

All this "brilliant" macro did was copy the contents of R1C1 to R2C1.

I miss Lotus. Sigh..

Thanks to anyone who helps! Tried to search this, BTW.

sam

 

[FSt1]

hi
i miss lotus too. but the business world went microsoft crazy. sigh.
any way maybe this will help you
add quote mark to value.
do loop.
Sub addquote()
Dim r As Range
Dim rd As Range
Range("A1").Select
Set r = Range("A1")
Do While Not IsEmpty(r)
Set rd = r.Offset(1, 0)
r = "'" & r
Set r = rd
Loop
End Sub

for next loop
Sub addstuff()
Dim r As Range
Dim rd As Range
Set r = Range("A1")
lc = Cells(Rows.Count, "A").End(xlUp).Rows
For c = 1 To lc
Set rd = r.Offset(1, 0)
r = "'" & r
Set r = rd
Next c
End Sub

regards
FSt1

 

[sam]

This was EXTREMELY nice of you to write this for me. Looks like I can't
perform my macro by using the "Record Macro" command? Cuz if I have to write
VBA for this I'm doomed.

Here's what the macro looks like in VBA:

Sub makelabel()
'
' makelabel Macro
' Macro recorded 2/25/2008 by
'
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.FormulaR1C1 = "'BN4125298 Count"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub

I'm sitting here with the Excel 2000 manual open, clueless. All I can think
of to do is to copy what you have written and dump it in to the routine, and
pray (fat chance). Is there a diifferent way? Why doesn't the Record Macro
command do this???

If I copy your routine in, should I start at ActiveCell.?

I really appreciate your help, but Ugh on this! I'm gonna start
copying/replacing, maybe you'll reply.

Thx much

sam

 

[sam]

Nevermind. Have done it manually.

Once again it has been affirmed that creating easy repetitive macros in
excel is a joke...

 

[JLGWhiz]

If by labels you mean text, then the easy way is just to fromate the entire
range as text. You numbers will still look like numbers but will have a
string data type. You can do that from the menu by first selecting the
range you want to format, the click Format>Cells>Text>OK. You can also use
in VBA:

ActiveSheet.Range("A1:A50").NumberFormat = "Text"

If it is necessary to change the format while the code is running. You can
specify any size range with code up to the entire sheet.

 

[FSt1]

hi
apparently i assumed too much. you did post under programming. take a look
at this site. good for starters. i was forced to give up lotus back in 99.
bummer.
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://msdn.microsoft.com/office/un...ibrary/en-us/odc_xl2003_ta/html/odc_super.asp

regards
FSt1

 

[Dave Peterson]

ActiveSheet.Range("A1:A50").NumberFormat = "Text"
should be:
ActiveSheet.Range("A1:A50").NumberFormat = "@"

But this doesn't change the underlying values from numbers to text. You'd still
need to convert them some way (looping through the cells or data|text to
columns???).