VBA Script
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: VBA Script

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com