PDA

View Full Version : Solved: VBA - How to re-run a macro?



PJC
07-08-2006, 08:26 AM
Hi,

I have the following code which calculates when a value is input to a cell in B. It works fine. However, if a value is later changed, say in D, the earlier calculation is incorrect.

Is it possible to get around this and have the macro re-run, even if it's a manual command? Code below:


Option Explicit
Private Sub Worksheet_Calculate()
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
Dim TR As Long 'Target Row
Dim PR As Long 'Previous Row
Dim SA As Double 'Sum column F
Dim SubstractThis As Integer
TR = Target.Row
PR = Cells(TR, 2).End(xlUp).Row
SA = Application.Sum(Range("F" & PR & ":F" & TR))
On Error Resume Next
SubstractThis = Range("B" & PR)
On Error GoTo 0
Application.EnableEvents = False
Range("G" & TR - 1) = SA - SubstractThis
Application.EnableEvents = True
End Sub


Appreciate any help you can give. Thanks, Peter.

malik641
07-08-2006, 11:46 AM
Hey Peter, and welcome to VBAX :hi:

Is there a way you can give us a dummy workbook so we can take a look at what's going wrong?

You say if you change a value in "D" then the macro won't run, and it shouldn't because of your first line of code.

Also, you should probably consider making this a function. And you end up with an error if you enter data in "B1" so you should try to incorporate that in your code :)

PJC
07-08-2006, 01:21 PM
Hi Malik, Thanks for the welcome. I'm new to VBA so hope my comments make some sense!

I've attached a dummy workbook. The user will input a value in B and then select various service codes and then price and quantity.

Service codes are selected via a data validation list and prices via vlookup using the service codes.

As you can see as soon as a new value is input to B the macro will calculate the value in G. This works fine. :thumb

However, if I were to change the prices in the vlookup table retrospectively the values in F would change, but the value in G would not.

Appreciate any help you can give. Best regards, Peter.

mdmackillop
07-08-2006, 02:32 PM
Hi PJC
Welcome to VBAX
This is some code I wrote some time ago, but never found a practical use for. Maybe this is it. It monitors a calculated cell and will do something when it changes. In your case, it could rerun the macro. The monitored cell here could be the total of Column D (meaningless in itself)
Regards
MD


Option Explicit
'Create variable to hold values
Dim Monitored
Private Sub Worksheet_Activate()
Monitored = [E8].Value 'Read in value prior to any changes
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'Check target to determine if macro is triggered
If Intersect(Target, Union([E6], [E7])) Is Nothing Then Exit Sub
'Prevent looping of code due to worksheet changes
Application.EnableEvents = False
'Compare monitored cell with initial value
If [E8].Value <> Monitored Then
'Do things as a result of a change
DoThings
'Reset Variable with new monitored value
Monitored = [E8].Value
End If
'Reset events
Application.EnableEvents = True
End Sub
Private Sub DoThings()
With [E9]
.Formula = [E6] + [E7]
.Interior.ColorIndex = 6
End With
End Sub

malik641
07-08-2006, 02:44 PM
What I would suggest is to use the Worksheet_Change event to place a formula in Column G that would be in conjunction to what you will use:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Or Target.Row = 1 Or Target.Row = 2 Then Exit Sub
Dim PR As Integer

PR = ActiveSheet.Cells(Target.Row, 1).End(xlUp).Row

ActiveSheet.Cells(Target.Row - 1, 7).Formula = _
"=B" & PR & "-Sum(F" & PR & ":F" & Target.Row - 1 & ")"
End Sub
It's better to have functions be as fast as possible because they're not as near as fast excel's built in functions. The only problem with this method is it won't handle cases where you would insert a row to existing data....:think:

And BTW, you wrote in your workbook that column G is "B minus F" when your code is F minus B......I assume you meant F minus B.

Check out the workbook I have. Just place a value of 2 in cell A5 and you'll see what I mean. I hope this is what you were looking for :thumb

EDIT: I just realized I didn't use the function I wrote at all...sorry!!! I deleted it as it was useless. Along with the text that went with it.

PJC
07-08-2006, 03:05 PM
Hi Malik,

Just what I needed, works like a dream :cloud9:

don't know how you guys write the code so quickly....and yes my text and formulas were wrong in the dummy book:doh:

Thanks again,

Peter

malik641
07-08-2006, 03:15 PM
Just what I needed, works like a dream :cloud9:

don't know how you guys write the code so quickly Glad to see it's what you were looking for :thumb

Practice, plenty of practice. And reading, too :)


Don't forget to mark your thread "solved", btw.

malik641
07-08-2006, 03:22 PM
Hey Peter

I found a small error in my coding. If you try to drag a cell(s) by using that plus sign at the bottom right corner of a cell, you'll have an error. Use this code instead:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column <> 1 Or Target.Row = 1 Or Target.Row = 2 _
Or Target.Value = "" Then Exit Sub

If Err.Number = 2015 Then Exit Sub

Dim PR As Integer

PR = ActiveSheet.Cells(Target.Row, 1).End(xlUp).Row

ActiveSheet.Cells(Target.Row - 1, 7).Formula = _
"=B" & PR & "-Sum(F" & PR & ":F" & Target.Row - 1 & ")"
End Sub

:thumb