VBA - assign variable to formula

akmal1981

New Member
Joined
Jun 20, 2011
Messages
4
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 !
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Instead of Worksheets_("Sheet2") write Worksheets("Sheet2").
The underscore gives error.
 
Upvote 0
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).
 
Upvote 0
If you wanted to add underscore then it'd look like this: Worksheets _("Sheet2"), because there must be a space before underscore.
 
Upvote 0
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 !
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top