Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert single quote symbol | Excel Worksheet Functions | |||
how do you insert lines in a quote form | Excel Discussion (Misc queries) | |||
Foot mark aka single quote mark? | Excel Worksheet Functions | |||
Extra Quote Mark | Excel Programming | |||
Help - 'finding' a quote mark (") ??? | Excel Programming |