Posts by p45cal

Re: Macro To Recalculate Workbook And Rescale A Chart Axis

All 3 subs work OK here. Post a file with the problem?
p45cal

Re: Visible Row Number In Autofiltered List

OK. I may be reinventing the wheel here, but vba seems to be simplest, but it's only being used to create a tiny function.
Create a User Defined Function thus (in a standard code module):

Function RowHidden(myrw As Range)
RowHidden = myrw.EntireRow.Hidden
End Function

In cell B2 (first item in the list) enter the value 1
In cell B3 enter:
[frc]=IF(RowHidden(B2),B2,B2+1)[/frc]
and fill down.

The function returns true or false depending on whether the cell it refers to is in a hidden row or not.

I feel sure I have reinvented the wheel and that there is a more straightforward way..

p45cal

Re: Hide Rows Macro For Cells With 0 In Three Columns

Sub blah()
For Each rw In ActiveSheet.UsedRange.Rows
If rw.Cells(3) = 0 And rw.Cells(5) = 0 And rw.Cells(6) = 0 Then rw.Hidden = True
Next rw
End Sub

p45cal

Re: Visible Row Number In Autofiltered List

in cell B2 (top of the list I used) have:
[frc]=COUNTIF($C$2:C2,C2)[/frc]
copy down, in cell B5 it becomes:
[frc]=COUNTIF($C$2:C5,C5)[/frc] etc.

p45cal

Re: Average Every nth Cell

A considerably more concise and robust formula, about half the length is:

[frc]=AVERAGE(IF((Daily!$A$2:$A$255<=Weekly!A3)*(Daily!$A$2:$A$255>Weekly!A3-5),Daily!$B$2:$B$255,FALSE))[/frc]

In the attached file, it used in the blue shaded area on the Weekly sheet and can be filled down. Array entered, as before.

p45cal

Re: If, Vlookup &amp; Sum

though you may find this variation easier to copy down:
[frc]=SUMIF(Sheet1!$A$1:$B$4,Sheet2!A1,Sheet1!$B$1:$B$4)[/frc]
just adds absolute refs where appropriate.

p45cal

Re: Range Copy And Paste Failed Error

See if this is the same:

Range("A" & rr).Copy
Range("A" & rr + 1).PasteSpecial xlValues
Range(Cells(rr, 1), Cells(rr, 38)).Delete Shift:=xlUp

or

Range("A" & rr + 1) = Range("A" & rr).Value
Range(Cells(rr, 1), Cells(rr, 38)).Delete Shift:=xlUp

in yours, the delete line is wiping out the stuff to be copied.

p45cal

Re: Convert Numbers to Times

Sub blah()
    For Each cll In Selection.Cells
        If (cll.Value <> "" Or Not IsEmpty(cll)) And Not cll.Value = 0 And IsNumeric(cll.Value) Then
            hours = Int(cll.Value / 100)
            minutes = cll.Value Mod 100
            cll.Value = Format(TimeValue(hours & ":" & minutes), "hh:mm")
        End If
    Next cll
End Sub

p45cal

Re: Convert Numbers to Times

I looked at your file and saw a problem, but I don't know how it tallies with your "without using formulas". It points out the failing of a formula. So I wrote a formula which seems to work where yours failed:
[frc]=TIMEVALUE(INT(A5/100) & ":" & MOD(A5,100))[/frc]

However, with vba, this little macro will work on the selected cells, changing their value:

Sub blah()
For Each cll In Selection.Cells
hours = Int(cll.Value / 100)
minutes = cll.Value Mod 100
cll.Value = Format(TimeValue(hours & ":" & minutes), "hh:mm")
Next cll
End Sub

p45cal

Re: Control Ranges Automatically Calculated

Yes it has to be Sub Worksheet_Change, it resides in the worksheet code module, (there's a max of one for each sheet). It detects a worksheet event, in this case a cell or cells changing, and when it does it runs the code in it. Of course, there's nothing to stop that code calling other subs, be they in the same code modules or a standard code module.

While you're coding this, above where you're typing, you'll see two dropdown menus. Choose Worksheet in the left one, and take your choice of events in the right hand one.

p45cal

Re: Average

I see there aren't always 5 day's data each week. In the attached sorkbook I have shaded in green formulae that can be copied down. It's an array entered formula (don't need to worry about that to copy them down, they're already array entered). It's a long formula, and I feel sure there must be a much more elegant solution, but it seems to give the right answers.

[frc]=SUM(IF(Daily!$A$2:$A$255<=A3,1,FALSE)*IF(Daily!$A$2:$A$255>=A3-4,1,FALSE)*Daily!$B$2:$B$255)/(COUNT(IF(Daily!$A$2:$A$255>A3-5,1,FALSE))-COUNT(IF(Daily!$A$2:$A$255>A3,1,FALSE)))[/frc]

p45cal

Re: Get Data From Other Files By Vba

try

Range("I9").Value = Application.Sum(Range("B" & x & ":B" & y))

p45cal

Re: Stop Close Form Button Working On Userforms

credit to j walkenbach, put this in the ThisWorkbook code module:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
    MsgBox "click the OK button to close"
    Cancel = True
    End If
End Sub

p45cal

Re: Stop Close Form Button Working On Userforms

deleted

Re: Control Ranges Automatically Calculated

Almost. On testing myself I found that you weren't directly editing column G, so a mildly different approach. This one does something only if any cell in a row lower than row 9 gets changed, then it only looks at the cell in column G's value and changes things on only that row, if appropriate.

Again, at its simplest:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 And Target.Row > 9 Then
    Application.EnableEvents = False
    If Cells(Target.Row, Columns("G").Column) = "Closed" Then Cells(Target.Row, Columns("C").Column) = Cells(Target.Row, Columns("C").Column).Value
    If Cells(Target.Row, Columns("G").Column) = "Open" Then Cells(Target.Row, Columns("F").Column) = Cells(Target.Row, Columns("F").Column).Value
End If
Application.EnableEvents = True
End Sub

Tested this time, by changing values in the Enter and Exit columns.
p45cal

Re: Control Ranges Automatically Calculated

Make sure that application.EnableEvents is True:
in the immediate pane type:
?application.EnableEvents
and Enter. If it returns False then type:
application.EnableEvents = true
and Enter.

Try again. Note that the macro now only affects a cell or two in one row.

p45cal

Re: Control Ranges Automatically Calculated

1. for 'not equal' try <>.
2. I thought you might want to try to put the code to restore the formulae into the same sub, but I deliberately didn't. At one point, you are guaranteed to press the delete key while in column G on the wrong row. As soon as you have done so, the formulae will be restored and immediately calculated. If the trade was a closed one then you will have lost, permanently, that trade record.
There might be several ways to deal with this, but my preference would be to keep the restoring of the formulae a completely separate process. My second best would be to only have the formulae restored on something specific being typed into column G such as "Reset" or "Cancel".
3. I did say the first line was overkill in my first post. Any change, anywhere on the sheet will cause column G to be processed. Take out the For..Next loop and replace every occurence of 'cll' with 'target'. Now only the row you're changing gets examined. Simplified, something on the lines of

Private Sub Worksheet_Change(ByVal Target As Range)
'the next line checks you're only changing one cell in column G below row 9
If Target.Cells.Count = 1 And Target.Column = Columns("G").Column And Target.Row > 9 Then
    Application.EnableEvents = False
    If Target.Value = "Closed" Then Target.Offset(0, -4) = Target.Offset(0, -4).Value
    Application.EnableEvents = True
End If
End Sub

4.
Where you have

If cll.Value = "" Then cll.Offset(0, -4).Formula = _
"=IF(RC[-1]="""","""",IF(R1C2=""Standard"",(VLOOKUP(RC[-1],Currency_Pair,2,0)),0)+IF(R1C2=""Mini"",(VLOOKUP(RC[-1],Currency_Pair,3,0)),0)+IF(R1C2=""Micro"",(VLOOKUP(RC[-1],Currency_Pair,4,0)),0))"
If cll.Value = "" Then cll.Offset(0, -1).Formula = _
"=IF(RC[-4]="""","""",(((IF(R1C2=""Standard"",(100000),0))+(IF(R1C2=""Mini"",(10000),0))+(IF(R1C2=""Micro"",(1000),0)))*VLOOKUP(RC[-4],Conv,9,0))*RC[-1])"

replace with

If cll.Value = "" Then
    cll.Offset(0, -4).Formula = _
    "=IF(RC[-1]="""","""",IF(R1C2=""Standard"",(VLOOKUP(RC[-1],Currency_Pair,2,0)),0)+IF(R1C2=""Mini"",(VLOOKUP(RC[-1],Currency_Pair,3,0)),0)+IF(R1C2=""Micro"",(VLOOKUP(RC[-1],Currency_Pair,4,0)),0))"
    cll.Offset(0, -1).Formula = _
    "=IF(RC[-4]="""","""",(((IF(R1C2=""Standard"",(100000),0))+(IF(R1C2=""Mini"",(10000),0))+(IF(R1C2=""Micro"",(1000),0)))*VLOOKUP(RC[-4],Conv,9,0))*RC[-1])"
End If

It saves testing the same condition twice, but more importantly it means you only have to edit one line to change those conditions.

p45cal

Re: Control Ranges Automatically Calculated

This sub will put what I think are the correct formulae in columns C or F, in the currently selected cell. Check 'em. It checks which column the active cell is in to decide which formula to put in.

Sub blah()
Application.EnableEvents = False 'needed cos if you've still got Open or Closed it'll swap it straight back!
If ActiveCell.Column = Columns("F").Column Then
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-4]="""","""",(((IF(R1C2=""Standard"",(100000),0))+(IF(R1C2=""Mini"",(10000),0))+(IF(R1C2=""Micro"",(1000),0)))*VLOOKUP(RC[-4],Conv,9,0))*RC[-1])"
End If
If ActiveCell.Column = Columns("C").Column Then
ActiveCell.FormulaR1C1 = _
        "=IF(RC2="""","""",IF(R1C2=""Standard"",(VLOOKUP(RC2,Currency_Pair,2,0)),0)+IF(R1C2=""Mini"",(VLOOKUP(RC2,Currency_Pair,3,0)),0)+IF(R1C2=""Micro"",(VLOOKUP(RC2,Currency_Pair,4,0)),0))"
End If
Application.EnableEvents = True
End Sub

In your adaptation, you can lose these two lines in the middle:

Next cll 
    For Each cll In UsedRange.Columns("G").Cells

saves going down the column twice.

p45cal

Re: Format Label Control

depends on the context, but something like this?

userform1.Label1.Caption=format(userform1.Label1.Caption,"##.00")

or

userform1.Label1.Caption=format(x,"##.000")

x being your value.
p45cal

Re: Control Ranges Automatically Calculated

Quote

column C to update automatically as it does Until the corosponding cell in Column G has a value of Closed

I think you will need vba code to do this.
The following approach should get you started. Every time the sheet is changed (this is overkill at the moment and could be refined by checking for what's changed), it runs through the cells in column G to see if any read "Closed", if they do, the cell 4 cells to the left (column C) gets changed from a formula to a value. Once it's a value then calculation won't change it anymore.

The code goes in the worksheet code module for the sheet concerned (right click the sheet tab and choose 'View code').

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
For Each cll In UsedRange.Columns("G").Cells
If cll.Value = "Closed" Then cll.Offset(0, -4) = cll.Offset(0, -4).Value
Next cll
Application.EnableEvents = True
End Sub

As far as

Quote

column F to calculate automatically one time and never again

is concerned, a similar thing can be done, but what is it that signals (in terms of what change occurs in the workbook that can be recognised by the code) that this one time calculation should take place?

p45cal