Consulting

Results 1 to 13 of 13

Thread: Test cells for scientific formatting

  1. #1
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,005
    Location

    Test cells for scientific formatting

    How can you test to see if a cell is formatted this way? Thanks
    Peace of mind is found in some of the strangest places.

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    Try something like this.

    [vba]

    Option Explicit

    Sub Scientific()

    If Left(ActiveCell.NumberFormat, 5) = "0.00E" Then
    MsgBox "Yes"
    Else
    MsgBox "No"
    End If

    End Sub
    [/vba]

  3. #3
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,005
    Location
    OK Thanks. Another question instead of starting a new thread.

    I have code to concantonates 5 cells into one that works fine using a for loop except when it encounters a blank row any of the following lines do not concantonate. Why is that? (Cells A thru E are concantonated into A). Thanks
    Peace of mind is found in some of the strangest places.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    Quote Originally Posted by austenr
    OK Thanks. Another question instead of starting a new thread.

    I have code to concantonates 5 cells into one that works fine using a for loop except when it encounters a blank row any of the following lines do not concantonate. Why is that? (Cells A thru E are concantonated into A). Thanks
    Probably because the code looks for the first blank line and stops.

    Show the code.

  5. #5
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,005
    Location
    DRJ..What if you have a number in the cell like this:


    1.47608E+20
    1.47608E+20 1.47608E+20
    1.47608E+20
    Peace of mind is found in some of the strangest places.

  6. #6
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Quote Originally Posted by austenr
    OK Thanks. Another question instead of starting a new thread.

    I have code to concantonates 5 cells into one that works fine using a for loop except when it encounters a blank row any of the following lines do not concantonate. Why is that? (Cells A thru E are concantonated into A). Thanks
    AustenR

    It might actually be better if you started a new thread with your other question.

    And also as xld has suggested show the code.

  7. #7
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,005
    Location
    [VBA] Sub ConcantonateColA()
    Dim I As Long

    For I = 1 To 6

    Range("A" & I).Value = Trim( _
    Range("A" & I).Value & " " & Range("B" & I).Value & " " & _
    Range("C" & I).Value & " " & Range("D" & I).Value & " " & _
    Range("E" & I).Value)
    Range("B1:E6").Select
    Selection.ClearContents

    Next I
    End Sub
    [/VBA]
    Peace of mind is found in some of the strangest places.

  8. #8
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,005
    Location
    I suspect that it quits because A is blank. Is this correct?
    Peace of mind is found in some of the strangest places.

  9. #9
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    Quote Originally Posted by austenr
    DRJ..What if you have a number in the cell like this:


    1.47608E+20
    1.47608E+20 1.47608E+20
    1.47608E+20

    The format is still the same so it will still work.

  10. #10
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,005
    Location
    I ran your sub against one cell with the value above and got the NO msgbox
    Peace of mind is found in some of the strangest places.

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    Quote Originally Posted by austenr
    [VBA] Sub ConcantonateColA()
    Dim I As Long

    For I = 1 To 6

    Range("A" & I).Value = Trim( _
    Range("A" & I).Value & " " & Range("B" & I).Value & " " & _
    Range("C" & I).Value & " " & Range("D" & I).Value & " " & _
    Range("E" & I).Value)
    Range("B1:E6").Select
    Selection.ClearContents

    Next I
    End Sub
    [/VBA]
    Try this

    [VBA]
    Sub ConcantonateColA()
    Dim I As Long

    For I = 1 To 6
    Range("A" & I).Value = Trim( _
    Range("A" & I).Value & " " & Range("B" & I).Value & " " & _
    Range("C" & I).Value & " " & Range("D" & I).Value & " " & _
    Range("E" & I).Value)
    Next I

    Range("B1:E6").ClearContents
    End Sub

    [/VBA]

  12. #12
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    Quote Originally Posted by austenr
    I ran your sub against one cell with the value above and got the NO msgbox
    Are you sure you don't have text in that cell? Where does the v alue come from, a download from somewhere?

  13. #13
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,005
    Location
    Good question xld. That idea hit me last night. It is a download from a text file but if you select that cell and do FOrmat>Cells, it does say scientific. Could I use the Trim function and move from the right three characters and look for "+"? If it is present then clear the contents? Thanks
    Peace of mind is found in some of the strangest places.

Posting Permissions

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