Posts by pangolin

Re: Date format a textbox?

Can the textbox be formatted only to accept date in the "DD/MM/YYYY" format

Re: Pniter Defult

perhaps by going to "Settings" "Printers" right clicking the required icon and selcting "Set as defaut printer"

I hope this helps

Re: printing a chart - runtime error subscript out of range

I think the command should be

Sheets("StudentChart").PrintOut Copies:=iPages, Collate:=True

pl nsure tht the location of the chart is "new chart" with that name

I hope this helps

Important
IF In posts code is not tagged....
THEN moderator will spank u

Re: Transition navigation key

I think it has something to do with Lotus 1-2-3. I remember using it while miagrating files from Lotus to excel. Although now I have completely forgotten how exactly to use them. Why dont you look it up on help

pangolin

Re: Automatic Time Format

Hi svenjafour

I think the time format works best with the colon ':' rather than the semi-colon ';'
Using this format it should be able to read and process (i.e say find minutes between two transactions) data in the way you seem to require

I hope this helps

Re: Copy from one worksheet to multiple workseets

hi

I had prepared a similar worksheet so I can share the code with you. In your case the data is not coming in properly...that actually makes the code lil bit lengthy (as compared to the original)

The code as such works reasonably well but still hopefully someone here may offer improvements.

In my case the solution is product focussed (as against your client base). Also the data does not contain headers mid-way so that the processing is much faster....I think you should follow Bills suggestion and rework the data import formats for better presentation

hope it helps

Dim lastrow As Integer
Dim zname As String
Sheets.Add.Name = "sheet2"
Sheets("sheet1").Select
Range("A:A").Select
lastrow = Cells.Find(what:="*", After:=[A1], _
    Searchorder:=xlByRows, _
    searchdirection:=xlPrevious).Row
For i = 1 To lastrow
b = Sheets("sheet1").Range("A1").Offset(i - 1, 0)
If Left(b, 5) = "BUYER" Then
x = x + 1
Sheets("sheet2").Range("b1").Offset(x, 0) = b
Sheets("sheet2").Range("c1").Offset(x, 0) = i - 1
End If
Next i
For i = 1 To x
If i = x Then
Sheets("sheet2").Range("d1").Offset(i, 0) = lastrow
Else
Sheets("sheet2").Range("d1").Offset(i, 0) = Sheets("sheet2").Range("c1").Offset(i + 1, 0) - 1
End If
Next i
Set nms = ActiveWorkbook.Worksheets
r = nms.Count
For i = 1 To x
b = Sheets("sheet2").Range("B1").Offset(i, 0)
Do
For a = 1 To r
zname = Worksheets(a).Name
If zname = b Then
tr = Sheets("sheet2").Range("c1").Offset(i, 0)
lr = Sheets("sheet2").Range("d1").Offset(i, 0)
Sheets("sheet1").Select
Rows(tr).Select
Selection.Resize(lr - tr + 1).Select
Selection.Copy
Worksheets(a).Select
If WorksheetFunction.CountA(Range("A:A")) = 0 Then
lrow = 1
Else
lrow = Cells.Find(what:="*", After:=[A1], _
Searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row
End If
Range("a1").Offset(lrow, 0).Select
ActiveSheet.Paste
lrow = 0
Exit Do
Else
y = y + 1
End If
Next a
Loop Until zname = b Or y = r
If y = r Then
Sheets.Add.Name = Sheets("sheet2").Range("B1").Offset(i, 0)
r = r + 1
tr = Sheets("sheet2").Range("c1").Offset(i, 0)
lr = Sheets("sheet2").Range("d1").Offset(i, 0)
Sheets("sheet1").Select
Rows(tr).Select
Selection.Resize(lr - tr + 1).Select
Selection.Copy
For a = 1 To r
zname = Worksheets(a).Name
If zname = b Then
Worksheets(a).Select
Range("a1").Select
ActiveSheet.Paste
lrow = 0
End If
Next a
End If
y = 0
Next i
Sheets("Sheet2").Select
ActiveWindow.SelectedSheets.Delete
End Sub

Re: Saving spreadsheet looks when closed

MILLISAN

perhaps you mean working with "split" windows. In that case you may write a macro for that worksheet something like this

Private Sub Worksheet_Activate()
With ActiveWindow
        .SplitColumn = 5
        .SplitRow = 0
    End With
End Sub

I hope this helps

Re: WorkSheet function NA to VB

Hi john

thanx for ur quick reply...now I am making use of the rounddown function...

but I would really like to know how to call such functions since later on I am making use of XIRR function and this also is not available from the "Worksheet Function".

Alternatively I have to put this on the worksheet and get the value which involves paste and selection and therefore not a time optimum solution. Or so you say maybe the solution is to write UDF

thanx all the same

Pl excuse if this sounds funny

It seems that certain worksheet functions are not available to VB. Now I am writing a code whereby the decimals have to be truncated for further validation. However I find that the TRUNC function is not available to VB. Hence I am forced to put the value on a particular cell to which the trunc formula is linked and then collect it again. This is entirely time consuming and ridiculous.

Can someone guide me as to how to call formulas from Excel which are not "available to VB".

Alternatively how to trunc decimals in VB- I mean even the MOD function is not available. I have just not able to figure out how a number that has decimals gets identified...

pl note that the values as internally generated and not user inputs

thanx in advance for all the guidance

Hi Aaaron

thanx for ur help

I think ur advise should be pretty acceptable.

usually I write macro to dispense off the ciscular problem. However these guys were adamant that macros were just not acceptable.

thanx once again

j

hi there

thanx for ur reply. I have attached an excel file that very clearly demonstrates how the circular function arises in the model I am working on

It is very self-explanatory. Actaully the circular function can get removed if a copy + paste special but that will involve manual intervention - a strict no no for financial models.

hope you can help me. but anyways thanx a million

regards

joshua

Hi forum

perhaps this question is not very relevant to the forum but wud feel thankful if someone can help me on this

I am doing financial appraisal for an infrastructure project having a long gestation period. During the project construction period (i.e before commercial ops) the interest payable on the debt gets funded by the financial institutions. In other words the interest known as INTERST DURING CONSTRUCTION becomes part of the project cost. for eg

let project cost before IDC be US$100
at DE of 1:1 debt is $50
assuming for one year at 5% the IDC is $5

therefore project cost becomes 105 & debt raised will become 52.50 and so on unless due to the iterative process Project Cost before IDC + IDC is equal to Debt + Equity at the DE ratio. Besides IDC syndication fees also have to be added to the Project Cost and that makes the problem more vexing.

While modelling the cash flows in excel this gives rise to a Circular function.

A circular function can be removed using Macro VBA but the bankers to whom I have to submit the model refuse to accept excel workbooks containing VBA. Also the circular function have to be removed since they do not know whether the function is converging or diverging, besides a "bad" circular function may get hidden.

So can someone let me know how to remove circular functions for such a model without using VBA.

would feel exteremely obliged if someone can help me out on this pronto as the deadline is fast approaching

thanx n ciao