VBA Script

[Brian_Richmond]

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

[Jay Petrulis]

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

[Brian_Richmond]

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.