Test cells for scientific formatting

[austenr]

How can you test to see if a cell is formatted this way? Thanks

[Jacob Hilderbrand]

Try something like this.

Option Explicit

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

[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

[Bob Phillips]

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.

[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

[Norie]

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.

[austenr]

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

[austenr]

I suspect that it quits because A is blank. Is this correct?

[Jacob Hilderbrand]

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.

[austenr]

I ran your sub against one cell with the value above and got the NO msgbox

[Bob Phillips]

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

Try this

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

[Bob Phillips]

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?

[austenr]

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