checking if sheet updated

[wakwak1]

I have some code that checks to see how many rows of data are present (starting from row 3) and then a formula is inputted into certain cells. I run it via clicking a button in the sheet.

Function findRows()
Range("B3").Select
Do While IsEmpty(ActiveCell.Offset(1, 0)) = False
ActiveCell.Offset(1, 0).Select
Loop
findRows = ActiveCell.Row

End Function

This is the some of the relevant code under the CommandButton click part:
x = findRows
Cells(2, 10).Formula = "=(SUMPRODUCT(ABS(B3:B" & x & "),F3:F" & x & "))/100"
Cells(4, 10).Formula = "=SUMPRODUCT(B3:B" & x & ",F3:F" & x & ")/100"

The problem is that if I am to add new rows of data, then I need to keep hitting this button (so that "x" is updated). I want it so that it automatically detects that there is new data, finds the new number of rows of data (i.e. "x" in my example) and then inputs the formulae. Thx!

[wakwak1]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("B3:G100")) Is Nothing Then
x = findRows
Cells(2, 10).Formula = "=(SUMPRODUCT(ABS(B3:B" & x & "),F3:F" & x & "))/100"
Cells(4, 10).Formula = "=SUMPRODUCT(B3:B" & x & ",F3:F" & x & ")/100"
End If
End Sub

This seems to work, but 2 minor problems: 1. B3:G100 is a bit arbitrary, it would be good if it could "detect" it byitself.
2. Whenever I enter any number manually in any cell in the range B3:G100 and hit enter, you need to hit enter AGAIN in order to go down the next cell.....something to do with the code?? Any better way to do this ? Thx!

[Bob Phillips]

What do you want to check against?

[wakwak1]

what do you mean exactly? I am just wanting to check if any additional rows are added of data are added (data goes from col A to col G) and if they are, i need formulae in cell J2 and J5 to to calculate stuff (based on the fact that there is now additional data .... ). I am happy to provide further clarification.

[mikerickson]

It looks like you could use a couple of dynamic named ranges.

Name: columnB
Refers to: = OFFSET(!$B$3,0,0,COUNTA(!$B:$B)-2,1)

Name: columnF
Refers to: = OFFSET(!$F$3,0,0,COUNTA(!$B:$B)-2,1)

Then the formulas
=(SUMPRODUCT(ABS(columnB),columnF))/100
and
=SUMPRODUCT(columnB,columnF)/100

can be left in I2 and I4 and don't need to be updated.

[wakwak1]

You made a small typo for the 2nd one, should have F's instead of B's for the 2nd COUNTA part.

I tried a simple example with my previous formula.... say we had in column B: 1, 2 and in colF 5,10 (going down) so we would expect 0.25, but what you gave me return 0.05 ?

[mikerickson]

I left the B in name of columnF so both would have the same number of rows, no matter what the user did.

The COUNTA(!$B:$B)-COUNTA(!$B$1:$B$2) might need to be adjusted to meet your layout.

[wakwak1]

thanks, i figured it out.

final question on this topic: how do you assign a hotkey to a button? i.e. I want to hit say, F5, and then the button will be selectetd (which will hence run the macro) ? Thx!

[mikerickson]

Go to the Macro Dialog box. Select the macro and use Options to set the hot key.

[wakwak1]

ok, but the macro I have is "run" once i click the button. i.e. it is contained under the Private Sub CommandButton1_Click()

So is there a hotkey that will "click" this button ?

Ta!

[mikerickson]

Put
Private Sub CommandButton1_Click()
Call myRoutine
End Sub
in the Sheet code module.

In a normal module

Sub myRoutine()
Rem your code
End Sub


Assign the hot key to "myRoutine".
After assigning the key, you can make myRoutine Private to avoid cluttering the Macro Dialog box.