# Posts by p45cal

• ## Recalculate Workbook & Rescale Chart

Re: Macro To Recalculate Workbook And Rescale A Chart Axis

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

• ## Visible Row Number In Autofiltered List

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):

Code
``````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

• ## Hide Rows With 0 In x Columns

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

Code
``````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

• ## Visible Row Number In Autofiltered List

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

• ## Average Every nth Cell

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

• ## If, Vlookup & Sum

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

• ## Copy & PasteSpecial Failed Error

Re: Range Copy And Paste Failed Error

See if this is the same:

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

or

Code
``````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

• ## Convert Numbers to Times

Re: Convert Numbers to Times

Code
``````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

• ## Convert Numbers to Times

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:

Code
``````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

• ## Control Ranges Automatically Calculated

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

• ## Average Every nth Cell

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

• ## Get Data From Other Files

Re: Get Data From Other Files By Vba

try

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

p45cal

• ## Stop Close Form Button Working On Userforms

Re: Stop Close Form Button Working On Userforms

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

Code
``````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

• ## Stop Close Form Button Working On Userforms

Re: Stop Close Form Button Working On Userforms

deleted

• ## Control Ranges Automatically Calculated

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:

Code
``````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

• ## Control Ranges Automatically Calculated

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

• ## Control Ranges Automatically Calculated

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

Code
``````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

Code
``````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

Code
``````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

• ## Control Ranges Automatically Calculated

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.

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

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

saves going down the column twice.

p45cal

• ## Format Label Control

Re: Format Label Control

depends on the context, but something like this?

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

or

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

x being your value.
p45cal

• ## Control Ranges Automatically Calculated

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').

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