Re: Macro To Recalculate Workbook And Rescale A Chart Axis
All 3 subs work OK here. Post a file with the problem?
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):
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: 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 & 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: 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
Display More
In your adaptation, you can lose these two lines in the middle:
saves going down the column twice.
p45cal
Re: Control Ranges Automatically Calculated
Quotecolumn 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
Quotecolumn 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