VBA - assign variable to formula

[akmal1981]

Dear MVPs,

Appreciate your kind help.

What I am trying to do is the following:

1) subtotal column in sheet2 and return the value in sheet1: macro runs well

2) the value is then assigned to a formula in sheet2 : didnt work out

my code looks like this:


Sub akmal()

Dim result As Integer
Dim A As Integer

result = 100 + A

With Sheet2 'Sheet2 is where the file resides

.AutoFilterMode = False
.Range("A1:D1").AutoFilter
.Range("A1:D1").AutoFilter Field:=1, Criteria1:="ben" 'Autofilter ben

Dim mySubtotal As Double

mySubtotal = Application.WorksheetFunction.Subtotal(109, Worksheets_("Sheet2").Range("b2:b3000")) 'subtotal for ben
Sheet1.Range("b1").Formula = mySubtotal 'Put in the total into sheet1

Sheet1.Range("b1") = A
Sheet1.Range("c1").Formula = result 'this line didnt work

.Range("A1:D1").AutoFilter Field:=1, Criteria1:="james" 'Autofilter james

Dim mySubtotal1 As Double

mySubtotal1 = Application.WorksheetFunction.Subtotal(109, Worksheets_("Sheet2").Range("b2:b3000")) 'subtotal for james
Sheet1.Range("b2").Formula = mySubtotal1 'Put in the total into sheet1

Sheet1.Range("b2") = A
Sheet1.Range("c2").Formula = result 'this line didnt work

End With

End Sub

I tried not to use result = 100 + mySubtotal1 or result = 100 + mySubtotal2 because there are many more "mySubtotals" . Can anyone please help to give some tips / solutions ?

p/s = i tried many combinations to assign mySubtotal to A in the formula but it always return 100

Cheers !

 

[akmal1981]

Sorry , slight amendment:

2) the value is then assigned to a formula in sheet1 : didnt work out

 

[Sektor]

Instead of Worksheets_("Sheet2") write Worksheets("Sheet2").
The underscore gives error.

 

[akmal1981]

Sorry , didnt mean to have the underscore . I added the underscore in the message box when I am posting (thought the message box is 'small' and hence added underscore to show next line).

 

[Sektor]

If you wanted to add underscore then it'd look like this: Worksheets _("Sheet2"), because there must be a space before underscore.

 

[akmal1981]

If you wanted to add underscore then it'd look like this: Worksheets _("Sheet2"), because there must be a space before underscore.

Thanks Sektor

But could you / anyone help to shed more lights on my problem . I hope I explain my problem well.

Please ignore the underscore , didnt mean to have that in my code.

Many thanks !