Insert a quote mark, then repeat
 

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

Insert a quote mark, then repeat
 

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" wrote:

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

Insert a quote mark, then repeat
 

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


"FSt1" wrote:

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" wrote:

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

Insert a quote mark, then repeat
 

Nevermind. Have done it manually.

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

"sam" wrote:

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


"FSt1" wrote:

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" wrote:

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

Insert a quote mark, then repeat
 

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 FormatCellsTextOK. 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.


"sam" wrote:

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

Insert a quote mark, then repeat
 

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/und.../odc_super.asp

regards
FSt1

"sam" wrote:

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


"FSt1" wrote:

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" wrote:

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

Insert a quote mark, then repeat
 

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???).

JLGWhiz wrote:

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 FormatCellsTextOK. 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.

"sam" wrote:

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

--

Dave Peterson