Error in populating Numbers

[bengaluru]

I have a spreadsheet which populates some numbers from another software

One column has CUSIP numbers of securities and all these are 9 characters (both numbers and alphabets). I find whereever there is an E in the string, Excel probably confuses to be an error and throws out an error number

for example if original is 89151E109 , then Excel is showing as 8.9151E+113

Is there a formula or code to get rid of this ?

[TMS]

Format the cell as text before you put the CUSIP into it or prefix the CUSIP with a single quote (')


Regards

[bengaluru]

Format the cell as text before you put the CUSIP into it or prefix the CUSIP with a single quote (')

Regards

I tried that , but it does not waork because the CUSIP numbers gets populated when I run a Macro, as soon as the macro is run, any formula / formatiing I have in the resultant sheet goes away.

Thanks for your help.

[TMS]

Build the formatting into the macro. ;-)

[bengaluru]

Build the formatting into the macro. ;-)

thanks. Can you tell me how to do this ?

[TMS]

I'd need to see the code that populates the spreadsheet.

Regards

[bengaluru]

I'd need to see the code that populates the spreadsheet.

Regards

Here it is

Sub BigIdea()
'
' Macro1 Macro
' Macro recorded 27/09/2007 '

'
Application.ScreenUpdating = False

Dim strCusip, strPort, strCur, strClient, strDesc, strType As String
Dim strSettle, strClear, strPrimary As String
Dim intRef, i As Integer

On Error Resume Next
Application.DisplayAlerts = False
Sheets("Summary").Delete
Application.DisplayAlerts = True

Sheets("PasteHere").Copy Before:=Sheets(1)
Sheets("PasteHere (2)").Name = "Summary"

Range("A1").EntireColumn.Insert

For i = 1 To Range("b65000").End(xlUp).Row
If Cells(i, "b").Value Like "*Internal:*" Then
intRef = Trim(Mid(Cells(i, "b").Value, InStr(Cells(i, "b").Value, ":") + 1, 6))
strType = Trim(Right(Cells(i, "b").Value, 30))
ElseIf Cells(i, "b").Value Like "*Acct/Pt:*" Then
strPort = Trim(Mid(Cells(i, "b").Value, InStr(Cells(i, "b").Value, ":") + 1, 14))
strCur = Left(Trim(Cells(i + 1, "b").Value), 3)
strClient = Trim(Right(Cells(i, "b").Value, Len(Cells(i, "b").Value) - (InStr(Cells(i, "b").Value, ".") + 1)))
ElseIf Cells(i, "b").Value Like "*Security:*" Then
strCusip = CStr(Trim(Mid(Cells(i, "b").Value, InStr(Cells(i, "b").Value, ":") + 1, 12)))
strDesc = Trim(Right(Cells(i, "b").Value, (Len(Cells(i, "b").Value)) - (InStr(Cells(i, "b").Value, ":") + 15)))
ElseIf Cells(i, "b").Value Like "*Total Units:*" Then
dblUnits = CDbl(Mid(Cells(i, "b").Value, (InStr(Cells(i, "b").Value, ":") + 1), 11))
dblBook = Mid(Cells(i + 1, "b").Value, InStr(Cells(i + 1, "b").Value, ":") + 1, 11)
strSettle = Trim(Mid(Cells(i + 1, "b").Value, InStr(25, Cells(i + 1, "b").Value, ":") + 1, 11))
dblBase = Mid(Cells(i + 4, "b").Value, InStr(Cells(i + 4, "b").Value, ":") + 1, 11)
dblFed = Mid(Cells(i + 4, "b").Value, InStr(30, Cells(i + 4, "b").Value, ":") + 1, 11)
ElseIf Cells(i, "b").Value Like "*Clearing:*" Then
strClear = Trim(Right(Trim(Cells(i, "b").Value), 10))
strPrimary = Trim(Right(Trim(Cells(i + 1, "b").Value), 15))

'Output
Range("f:f").NumberFormat = "@"
Cells(i, "a").Value = "@"
Cells(i, "b").Value = strType
Cells(i, "c").Value = strPort
Cells(i, "d").Value = strClient
Cells(i, "e").Value = strCur
Cells(i, "f").Value = strCusip
Cells(i, "g").Value = strDesc
Cells(i, "h").Value = dblUnits
Cells(i, "i").Value = dblBook
Cells(i, "j").Value = dblBase
Cells(i, "k").Value = dblFed
Cells(i, "l").Value = intRef
Cells(i, "m").Value = strSettle
Cells(i, "n").Value = strClear
Cells(i, "o").Value = strPrimary
End If
Next i

Columns("A:A").AutoFilter Field:=1, Criteria1:="<>@", Operator:=xlAnd
Range("b1").SpecialCells(xlCellTypeVisible).EntireRow.Delete
Range("a1").EntireColumn.Delete

Range("a1").EntireRow.Insert
Range("a1").Value = "TranType"
Range("b1").Value = "Portfolio"
Range("c1").Value = "Client"
Range("d1").Value = "Curr"
Range("e1").Value = "Cusip"
Range("f1").Value = "Security"
Range("g1").Value = "Share"
Range("h1").Value = "Book Cost"
Range("i1").Value = "Base Cost"
Range("j1").Value = "Fed Cost"
Range("k1").Value = "GP Internal Ref"
Range("l1").Value = "SettleDate"
Range("m1").Value = "ClearingBrkr"
Range("n1").Value = "ClearingBrkrAcct"

Cells.EntireColumn.AutoFit
Range("a1").Select
Range("a1").CurrentRegion.Name = "Transfer"

End Sub

[TMS]

You need to put CODE Tags around your code or the Forum Moderators will be unhappy ... with both of us.

On the assumption that you will do that, I think you need:

Please Login or Register  to view this content.

As an example:

Please Login or Register  to view this content.

Regards

[bengaluru]

Thanks mate Unfortunately it does not work.

Now that you have seen my macro, can you tell what I should add to make the data appear from Row 4. I have a heading from rows 1 to 3 in my resultant spreadsheet and I want the result to start from Row 4.

Now it is sstating from A2

[TMS]

So it's overwriting your headings?

Ok, for that, I could do with seeing a sample workbook with some typical data. It probably won't take long to fix if I can see what happens as the code executes.

Regards

[bengaluru]

Sorry my friend. The workbook has too much information that I will not be able to share.

Thanks very much for all your help. If it is possible to tell me just by looking at the macro, how I can get the data to start from row 4 that would be great. If not, that's OK

Have a great weekend my friend.