VBA Script

Brian_Richmond

New Member
Joined
Apr 10, 2002
Messages
14
Wrote the script to convert slected range in a column to text. On my machine it works fine, but on my buddys machine it goes to cell A1 and traverses horizontally through all cells in row 1, instead of column selection. There are no frozen panes, split windows, or filters on. Do you have any suggestions about options or preferences that may be set, that would cause this.

SCRIPT:

Option Explicit
Sub Range_Conversion_To_Text()

' You can only select data in one column at a time, I haven't built horizontal
' cell selection checking or incrementation into the script. If you select more
' than one column simultaneously, your data will get injected into the 1st column,
' and corrupt whatever info you thought you had stored.

Dim Selection As Object
Dim Target As Variant
Dim Result As String
Dim Y_Axis As Integer

Y_Axis = 0

For Each Selection In ActiveCell.CurrentRegion.Cells
Target = Selection.Value
Result = Target
ActiveCell.Offset(Y_Axis, 0).ClearContents
ActiveCell.Offset(Y_Axis, 0).NumberFormat = "@"
ActiveCell.Offset(Y_Axis, 0) = Result
Y_Axis = Y_Axis + 1
Next

End Sub
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
On 2002-04-12 08:46, Brian_Richmond wrote:
Wrote the script to convert slected range in a column to text. On my machine it works fine, but on my buddys machine it goes to cell A1 and traverses horizontally through all cells in row 1, instead of column selection. There are no frozen panes, split windows, or filters on. Do you have any suggestions about options or preferences that may be set, that would cause this.

SCRIPT:

Option Explicit
Sub Range_Conversion_To_Text()

' You can only select data in one column at a time, I haven't built horizontal
' cell selection checking or incrementation into the script. If you select more
' than one column simultaneously, your data will get injected into the 1st column,
' and corrupt whatever info you thought you had stored.

Dim Selection As Object
Dim Target As Variant
Dim Result As String
Dim Y_Axis As Integer

Y_Axis = 0

For Each Selection In ActiveCell.CurrentRegion.Cells
Target = Selection.Value
Result = Target
ActiveCell.Offset(Y_Axis, 0).ClearContents
ActiveCell.Offset(Y_Axis, 0).NumberFormat = "@"
ActiveCell.Offset(Y_Axis, 0) = Result
Y_Axis = Y_Axis + 1
Next

End Sub

Hi,

What exactly are you trying to accomplish? It appears that you are turning any cell its value and formatting as text.

Two options (?)
-------------
Sub test()
Dim rng As Range

Set rng = ActiveCell.CurrentRegion
rng = rng.Value
rng.NumberFormat = "@"
End Sub
-------------
-------------
Sub test2()
Selection = Selection.Value
Selection.NumberFormat = "@"
End Sub
-------------

Each of these will do this for rows and columns. The second one requires the range to be selected prior to running the procedure.

HTH,
Jay
 
Upvote 0
We use Office 97 at work, and have run into a problem when a given column, row, or cell is selected and using the Format Cells Control Box change the formating to "Text" so Vlookups will work but after applying the change the cell format has changed, but the data value is still in its original format.

I figure if I pull the data value from each cell in the selection range as variant, convert it to string, clear the old contents from the cell, format the cell as text, and finally put the string value into the cell problem is solved.

The problem is solved on my machine, and several others. But for some weird reason on Jerry's machine when we select a range of cells in a column for conversion the data in row 1 is read in from column "A" thru the entire set of cells available on row 1, and puts the converted data into the column in the appropriate offset for rows.

On my machine, and others it grabs the 1st selected/highligted cell in a column, parses it, moves to the next row down in the column and repeats the parsing against the appropriate cell until all of the selected/highlighted cells are parsed.

We are trying to figure out if there is some sort of Option, or Preference that may be set on his machine that could be causing the input to come entirely from row 1, instead of from each of the selected cells in the range progressively.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top