Posts by chitosunday

Re: Solver Automation VBA Code

Excel solver in vba acts unreasonable. So my suggestion is without reason also. Kindly try it because i have this same experience and what i did was change the decimal to %.

Re: Solver Automation VBA Code

try to convert all your decimal condition up 1 as a percentage. like 100%

Re: Public and Private Sub

Private sub hides your macro when you want to view it in tools, macro, macros. Combined with making your project protected and locked(w/ password), it becomes personal in nature, meaning you are the only one who can identify it.

Public sub makes you macro viewable in tools, macro, macros.

Re: error handling

In short, it returns the normal way of handling errors which is stopping your macro code. So you don't need "on error goto 0" if you don't have previous "on error resume next" or "on error goto somwhere statement" . The two latter statements make your macro run continuously.

Re: Clearing Arrays

Same command redim

Sub test()
    Dim i As Integer
    Dim myarray() As Integer
    ReDim myarray(5, 6)
    For i = 1 To 5
        myarray(1, i) = 3
    Next i
    MsgBox myarray(1, 1)
    ReDim myarray(5, 6)
    MsgBox myarray(1, 1)
End Sub

Re: Clearing Arrays

if it is only one item you can just put

myarray(5,6)=""

Re: Solver ignores contraints

My guess is that you are using excel 2000. Sounds like a bug. Work around is save the settings of solver manually then load in again via macro

Re: Time puzzle

See the attached file for the reference unsocial hours.

Re: Time puzzle

=if(b5<a5,min(b5,6/24)+1-max(a5,22/24)+max(0,6/24-a5),if(a5<=6/24,b5-a5-max(0,b5-6/24)+max(0,b5-22/24)+if(b5<a5,b5,0),max(0,b5-22/24)-max(0,a5-22/24)))*24

Re: Time puzzle

You must specify date and time here . Assuming start date is a4 and enddate is b4
=((INT(B4)+MIN(MOD(B4,1),6/24))-(INT(A4)+IF(INT(B4)>INT(A4),MAX(MOD(A4,1),22/24),MIN(MOD(A4,1),6/24))))*24

Re: Loan calculator

Just read your post. You need to increase the width of your column through dragging the column header or clicking format column width and giving a larger number width

Re: Loan calculator

Why drag when you can automate see my file

Re: transfer data from form to worksheet

This code brings you to the last row . I think you need to delete this code

ActiveCell.Offset(Selection.Rows.Count, 0).Activate

Re: window with relative reference option gone missing

Click view, toolbars , stop macro recording

Re: Employee Time Sheet

You can use our previous formula even for a text file because excel converts it to number unless the text file is not in the cell but directly in your formula. In your example, i don't see any problem.

Re: Employee Time Sheet

Use n() function and you don't need sum() if you use +

Re: file in XLStart folder does not open

Click tools, option, general , put that path in the box of "At start up , open all files at

Re: Range as a variable

c = ActiveCell.Address
Sheets(2).activate
Range(c).Select

Re: Variable not preserved

The test variable should be on top of the module sheet

public test as integer


public sub test()
test = test + 1
end sub

Re: Any ideas the variable not preserved?

Can you put a break point after you assign a value to frow then check if there is a value other than 0 . I believe the value did not change from 0