Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to find and change text within a range of cells...
Hi all--
I'd like to be able to select a range of cells, search that range for any cells that contain a number (format is set to "General"), and then, if a cell contains a number, change the Format to "Text" and then add a space after the number. Currently, I have a conditional format set to "=ISNUMBER(cellref)", which highlights the cell in red if true. Then I have a short little macro--listed at the very end--that changes the Format and then adds the space. (Note: all I know about vba is what I've looked at after recording a macro and what I've read or found online... so I'm pretty proud that I could do at least this much!) But I soon tired of clicking on each cell (highlighted by cond. formt) and then clicking my macro button, so that got me wishing I could just select the entire range and check it all at once. But that's totally beyond my current vba capabilities. [start short rant here...] By the way, yes, I could just set the Cell Format for everything to "Text" but then I'd have no way to check if a "text" "4" will be interpreted as a "number" 4 in my mail merge Word doc using ODBC (and either changed to 4.0 or, even wose, just left blank!). And this all wouldn't be a problem if I could do the mail merge using the default DDE (which has worked completely fine on my computer for years now), but all of a sudden, Word started hanging when trying to open the mail merge base documents using DDE. Aargh! And after much online searching and reading of newsgroups, I've only managed to determine that answers to *WHY* DDE mail merges suddenly stop working and *HOW* on earth to fix them rank right up there with what's the meaning of life and when will the Cubs win a World Series again... [rant over] Thanks for any help, Karin (using Office 2000, if that's important) And here's my little macro: Sub ChangeCellToText() ' ' ChangeCellToText Macro ' change cell format to text, then add space after number ' Selection.NumberFormat = "@" Dim CellText As String CellText = ActiveCell.Text CellText = CellText & " " ActiveCell.Value = CellText End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to find and change text within a range of cells...
Here's one that would do it and takes a 'hands-off' approach - it will select
all cells on a sheet that were ever used (within the sheet's UsedRange area) and evaluate them. Since same cells may get evaluated more than once over time, I added a check to keep from adding extra space characters to the end of an already converted number: Sub NumToText() Dim anyCell As Object 'this is probably overkill, but 'it's quick and sure ActiveSheet.UsedRange.Select For Each anyCell In Selection If IsNumeric(anyCell.Value) Then anyCell.NumberFormat = "@" 'keep from continually adding spaces If Right(anyCell.Text, 1) < " " Then anyCell.Value = anyCell.Text & " " End If End If Next Range("A1").Select ' clean up appearance End Sub Now, if you'd rather choose the range of cells to test yourself, then change it to look like this and then select the cells to be tested before running the code: Sub NumToText() Dim anyCell As Object For Each anyCell In Selection If IsNumeric(anyCell.Value) Then anyCell.NumberFormat = "@" 'keep from continually adding spaces If Right(anyCell.Text, 1) < " " Then anyCell.Value = anyCell.Text & " " End If End If Next Range("A1").Select ' clean up appearance End Sub "schwammrs" wrote: Hi all-- I'd like to be able to select a range of cells, search that range for any cells that contain a number (format is set to "General"), and then, if a cell contains a number, change the Format to "Text" and then add a space after the number. Currently, I have a conditional format set to "=ISNUMBER(cellref)", which highlights the cell in red if true. Then I have a short little macro--listed at the very end--that changes the Format and then adds the space. (Note: all I know about vba is what I've looked at after recording a macro and what I've read or found online... so I'm pretty proud that I could do at least this much!) But I soon tired of clicking on each cell (highlighted by cond. formt) and then clicking my macro button, so that got me wishing I could just select the entire range and check it all at once. But that's totally beyond my current vba capabilities. [start short rant here...] By the way, yes, I could just set the Cell Format for everything to "Text" but then I'd have no way to check if a "text" "4" will be interpreted as a "number" 4 in my mail merge Word doc using ODBC (and either changed to 4.0 or, even wose, just left blank!). And this all wouldn't be a problem if I could do the mail merge using the default DDE (which has worked completely fine on my computer for years now), but all of a sudden, Word started hanging when trying to open the mail merge base documents using DDE. Aargh! And after much online searching and reading of newsgroups, I've only managed to determine that answers to *WHY* DDE mail merges suddenly stop working and *HOW* on earth to fix them rank right up there with what's the meaning of life and when will the Cubs win a World Series again... [rant over] Thanks for any help, Karin (using Office 2000, if that's important) And here's my little macro: Sub ChangeCellToText() ' ' ChangeCellToText Macro ' change cell format to text, then add space after number ' Selection.NumberFormat = "@" Dim CellText As String CellText = ActiveCell.Text CellText = CellText & " " ActiveCell.Value = CellText End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to find and change text within a range of cells...
Thanks so much. I'm using the second one and it works great!
"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Here's one that would do it and takes a 'hands-off' approach - it will select all cells on a sheet that were ever used (within the sheet's UsedRange area) and evaluate them. Since same cells may get evaluated more than once over time, I added a check to keep from adding extra space characters to the end of an already converted number: Sub NumToText() Dim anyCell As Object 'this is probably overkill, but 'it's quick and sure ActiveSheet.UsedRange.Select For Each anyCell In Selection If IsNumeric(anyCell.Value) Then anyCell.NumberFormat = "@" 'keep from continually adding spaces If Right(anyCell.Text, 1) < " " Then anyCell.Value = anyCell.Text & " " End If End If Next Range("A1").Select ' clean up appearance End Sub Now, if you'd rather choose the range of cells to test yourself, then change it to look like this and then select the cells to be tested before running the code: Sub NumToText() Dim anyCell As Object For Each anyCell In Selection If IsNumeric(anyCell.Value) Then anyCell.NumberFormat = "@" 'keep from continually adding spaces If Right(anyCell.Text, 1) < " " Then anyCell.Value = anyCell.Text & " " End If End If Next Range("A1").Select ' clean up appearance End Sub "schwammrs" wrote: Hi all-- I'd like to be able to select a range of cells, search that range for any cells that contain a number (format is set to "General"), and then, if a cell contains a number, change the Format to "Text" and then add a space after the number. Currently, I have a conditional format set to "=ISNUMBER(cellref)", which highlights the cell in red if true. Then I have a short little macro--listed at the very end--that changes the Format and then adds the space. (Note: all I know about vba is what I've looked at after recording a macro and what I've read or found online... so I'm pretty proud that I could do at least this much!) But I soon tired of clicking on each cell (highlighted by cond. formt) and then clicking my macro button, so that got me wishing I could just select the entire range and check it all at once. But that's totally beyond my current vba capabilities. [start short rant here...] By the way, yes, I could just set the Cell Format for everything to "Text" but then I'd have no way to check if a "text" "4" will be interpreted as a "number" 4 in my mail merge Word doc using ODBC (and either changed to 4.0 or, even wose, just left blank!). And this all wouldn't be a problem if I could do the mail merge using the default DDE (which has worked completely fine on my computer for years now), but all of a sudden, Word started hanging when trying to open the mail merge base documents using DDE. Aargh! And after much online searching and reading of newsgroups, I've only managed to determine that answers to *WHY* DDE mail merges suddenly stop working and *HOW* on earth to fix them rank right up there with what's the meaning of life and when will the Cubs win a World Series again... [rant over] Thanks for any help, Karin (using Office 2000, if that's important) And here's my little macro: Sub ChangeCellToText() ' ' ChangeCellToText Macro ' change cell format to text, then add space after number ' Selection.NumberFormat = "@" Dim CellText As String CellText = ActiveCell.Text CellText = CellText & " " ActiveCell.Value = CellText End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to find and change text within a range of cells...
Great to hear that it's working well for you. Thanks for the feedback.
"schwammrs" wrote: Thanks so much. I'm using the second one and it works great! "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Here's one that would do it and takes a 'hands-off' approach - it will select all cells on a sheet that were ever used (within the sheet's UsedRange area) and evaluate them. Since same cells may get evaluated more than once over time, I added a check to keep from adding extra space characters to the end of an already converted number: Sub NumToText() Dim anyCell As Object 'this is probably overkill, but 'it's quick and sure ActiveSheet.UsedRange.Select For Each anyCell In Selection If IsNumeric(anyCell.Value) Then anyCell.NumberFormat = "@" 'keep from continually adding spaces If Right(anyCell.Text, 1) < " " Then anyCell.Value = anyCell.Text & " " End If End If Next Range("A1").Select ' clean up appearance End Sub Now, if you'd rather choose the range of cells to test yourself, then change it to look like this and then select the cells to be tested before running the code: Sub NumToText() Dim anyCell As Object For Each anyCell In Selection If IsNumeric(anyCell.Value) Then anyCell.NumberFormat = "@" 'keep from continually adding spaces If Right(anyCell.Text, 1) < " " Then anyCell.Value = anyCell.Text & " " End If End If Next Range("A1").Select ' clean up appearance End Sub "schwammrs" wrote: Hi all-- I'd like to be able to select a range of cells, search that range for any cells that contain a number (format is set to "General"), and then, if a cell contains a number, change the Format to "Text" and then add a space after the number. Currently, I have a conditional format set to "=ISNUMBER(cellref)", which highlights the cell in red if true. Then I have a short little macro--listed at the very end--that changes the Format and then adds the space. (Note: all I know about vba is what I've looked at after recording a macro and what I've read or found online... so I'm pretty proud that I could do at least this much!) But I soon tired of clicking on each cell (highlighted by cond. formt) and then clicking my macro button, so that got me wishing I could just select the entire range and check it all at once. But that's totally beyond my current vba capabilities. [start short rant here...] By the way, yes, I could just set the Cell Format for everything to "Text" but then I'd have no way to check if a "text" "4" will be interpreted as a "number" 4 in my mail merge Word doc using ODBC (and either changed to 4.0 or, even wose, just left blank!). And this all wouldn't be a problem if I could do the mail merge using the default DDE (which has worked completely fine on my computer for years now), but all of a sudden, Word started hanging when trying to open the mail merge base documents using DDE. Aargh! And after much online searching and reading of newsgroups, I've only managed to determine that answers to *WHY* DDE mail merges suddenly stop working and *HOW* on earth to fix them rank right up there with what's the meaning of life and when will the Cubs win a World Series again... [rant over] Thanks for any help, Karin (using Office 2000, if that's important) And here's my little macro: Sub ChangeCellToText() ' ' ChangeCellToText Macro ' change cell format to text, then add space after number ' Selection.NumberFormat = "@" Dim CellText As String CellText = ActiveCell.Text CellText = CellText & " " ActiveCell.Value = CellText End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find text in a range of cells? | Excel Discussion (Misc queries) | |||
Change text color of specific date range by macro in Excel | Excel Programming | |||
Need macro to delete all text cells in an Excel range | Excel Worksheet Functions | |||
Macro to Create text file from range of cells? | Excel Programming | |||
Can Range.Find search a Cells' Text property? | Excel Programming |