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 !
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 !