PDA

View Full Version : [SOLVED] Test cells for scientific formatting



austenr
05-30-2005, 02:47 PM
How can you test to see if a cell is formatted this way? Thanks

Jacob Hilderbrand
05-30-2005, 02:51 PM
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
05-30-2005, 03:31 PM
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
05-30-2005, 03:32 PM
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
05-30-2005, 03:35 PM
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
05-30-2005, 04:33 PM
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
05-30-2005, 05:57 PM
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
05-30-2005, 05:58 PM
I suspect that it quits because A is blank. Is this correct?

Jacob Hilderbrand
05-30-2005, 08:07 PM
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
05-30-2005, 09:17 PM
I ran your sub against one cell with the value above and got the NO msgbox

Bob Phillips
05-31-2005, 01:11 AM
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
05-31-2005, 01:13 AM
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
05-31-2005, 07:28 AM
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