I want to know the VBA codes workings on an excel formula i.e."SUMPRODUCT".
Please refer my attach file.In this file,I want to achieve the results through VBA codes of columns Z to AD only.Yes,an extra working,which is required is Column Y i.e."Remarks" through automate input by VBA working.
Is that possible through VBA codes scenario?
Required VBA codes solution of "SUMPRODUCT" formula
-
- 3StarLounger
- Posts: 354
- Joined: 27 Oct 2013, 15:11
- Location: Gurgaon INDIA
Required VBA codes solution of "SUMPRODUCT" formula
Regards
Pradeep Kumar Gupta
INDIA
Pradeep Kumar Gupta
INDIA
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Required VBA codes solution of "SUMPRODUCT" formula
Hi Pradeep,PRADEEPB270 wrote:I want to know the VBA codes workings on an excel formula i.e."SUMPRODUCT".
Please refer my attach file.In this file,I want to achieve the results through VBA codes of columns Z to AD only.Yes,an extra working,which is required is Column Y i.e."Remarks" through automate input by VBA working.
Is that possible through VBA codes scenario?
Is it really necessary to use VBA codes for this?
If formulas are not efficient enough, how about using Pivot Tables that can give you the same results in less than a minute?
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 3StarLounger
- Posts: 354
- Joined: 27 Oct 2013, 15:11
- Location: Gurgaon INDIA
Re: Required VBA codes solution of "SUMPRODUCT" formula
Thanks Rudi for suggestions but it is really necessary to use VBA codes for this.As I want to know the VBA codes process involving an excel formula.Could you please provide the VBA codes for that purpose?
Thanks in advance.
Thanks in advance.
Regards
Pradeep Kumar Gupta
INDIA
Pradeep Kumar Gupta
INDIA
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Required VBA codes solution of "SUMPRODUCT" formula
Try this:
Change starting cell references as needed...
Change starting cell references as needed...
Code: Select all
Sub CalcsQTY()
Dim rStart As Range
Set rStart = Intersect(Range("Y2").CurrentRegion, Range("Y2").CurrentRegion.Offset(2, 1))
rStart.FormulaR1C1 = "=SUMPRODUCT((R6C22:R99960C22=RC25)*(R6C5:R99960C5=R3C)*R6C13:R99960C13)"
rStart.Columns(rStart.Columns.Count).FormulaR1C1 = "=SUM(RC[-5]:RC[-1])"
End Sub
Sub CalcsAMT()
Dim rStart As Range
Set rStart = Intersect(Range("Y10").CurrentRegion, Range("Y10").CurrentRegion.Offset(2, 1))
rStart.FormulaR1C1 = "=SUMPRODUCT((R6C22:R99960C22=RC25)*(R6C5:R99960C5=R11C)*R6C14:R99960C14)"
rStart.Columns(rStart.Columns.Count).FormulaR1C1 = "=SUM(RC[-5]:RC[-1])"
End Sub
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 3StarLounger
- Posts: 354
- Joined: 27 Oct 2013, 15:11
- Location: Gurgaon INDIA
Re: Required VBA codes solution of "SUMPRODUCT" formula
Macro is working well related to Qty and Amount except left to automate insert text in column Y as "Remarks".Thanks for nice solution provide.
Regards
Pradeep Kumar Gupta
INDIA
Pradeep Kumar Gupta
INDIA
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Required VBA codes solution of "SUMPRODUCT" formula
This should add the Remarks values too...
Code: Select all
Sub CalcsQTY()
Dim rSource As Range, rStart As Range
Application.ScreenUpdating = False
Set rSource = Range("B5").CurrentRegion.Columns(Range("V5").CurrentRegion.Columns.Count)
rSource.AdvancedFilter Action:=xlFilterCopy, Unique:=True, CopyToRange:=Range("Y3")
Set rStart = Intersect(Range("Y2").CurrentRegion, Range("Y2").CurrentRegion.Offset(2, 1))
rStart.FormulaR1C1 = "=SUMPRODUCT((R6C22:R99960C22=RC25)*(R6C5:R99960C5=R3C)*R6C13:R99960C13)"
rStart.Columns(rStart.Columns.Count).FormulaR1C1 = "=SUM(RC[-5]:RC[-1])"
Application.ScreenUpdating = True
End Sub
Sub CalcsAMT()
Dim rSource As Range, rStart As Range
Application.ScreenUpdating = False
Set rSource = Range("B5").CurrentRegion.Columns(Range("V5").CurrentRegion.Columns.Count)
rSource.AdvancedFilter Action:=xlFilterCopy, Unique:=True, CopyToRange:=Range("Y11")
Set rStart = Intersect(Range("Y10").CurrentRegion, Range("Y10").CurrentRegion.Offset(2, 1))
rStart.FormulaR1C1 = "=SUMPRODUCT((R6C22:R99960C22=RC25)*(R6C5:R99960C5=R11C)*R6C14:R99960C14)"
rStart.Columns(rStart.Columns.Count).FormulaR1C1 = "=SUM(RC[-5]:RC[-1])"
Application.ScreenUpdating = True
End Sub
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 3StarLounger
- Posts: 354
- Joined: 27 Oct 2013, 15:11
- Location: Gurgaon INDIA
Re: Required VBA codes solution of "SUMPRODUCT" formula
Yes,it is required for perfect working.Glad to find.Once again,thanks Rudi for your cooperation.
Regards
Pradeep Kumar Gupta
INDIA
Pradeep Kumar Gupta
INDIA
-
- 3StarLounger
- Posts: 354
- Joined: 27 Oct 2013, 15:11
- Location: Gurgaon INDIA
Re: Required VBA codes solution of "SUMPRODUCT" formula
Sorry Rudi,once again.I forget to update you one problem.When,I run the VBA codes ( Qty and Amount ) then,"Sumproduct" formula' with values are appearing.It should not be appear.Only the paste special value should be appear.Can it be possible?
Regards
Pradeep Kumar Gupta
INDIA
Pradeep Kumar Gupta
INDIA
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Required VBA codes solution of "SUMPRODUCT" formula
The new code...
Code: Select all
Sub CalcsQTY()
Dim rSource As Range, rStart As Range
Application.ScreenUpdating = False
Set rSource = Range("B5").CurrentRegion.Columns(Range("B5").CurrentRegion.Columns.Count)
rSource.AdvancedFilter Action:=xlFilterCopy, Unique:=True, CopyToRange:=Range("Y3")
Set rStart = Intersect(Range("Y2").CurrentRegion, Range("Y2").CurrentRegion.Offset(2, 1))
rStart.FormulaR1C1 = "=SUMPRODUCT((R6C22:R99960C22=RC25)*(R6C5:R99960C5=R3C)*R6C13:R99960C13)"
rStart.Columns(rStart.Columns.Count).FormulaR1C1 = "=SUM(RC[-5]:RC[-1])"
rStart.Copy
rStart.PasteSpecial xlPasteValuesAndNumberFormats
rStart.Cells(1).Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Sub CalcsAMT()
Dim rSource As Range, rStart As Range
Application.ScreenUpdating = False
Set rSource = Range("B5").CurrentRegion.Columns(Range("B5").CurrentRegion.Columns.Count)
rSource.AdvancedFilter Action:=xlFilterCopy, Unique:=True, CopyToRange:=Range("Y11")
Set rStart = Intersect(Range("Y10").CurrentRegion, Range("Y10").CurrentRegion.Offset(2, 1))
rStart.FormulaR1C1 = "=SUMPRODUCT((R6C22:R99960C22=RC25)*(R6C5:R99960C5=R11C)*R6C14:R99960C14)"
rStart.Columns(rStart.Columns.Count).FormulaR1C1 = "=SUM(RC[-5]:RC[-1])"
rStart.Copy
rStart.PasteSpecial xlPasteValuesAndNumberFormats
rStart.Cells(1).Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 3StarLounger
- Posts: 354
- Joined: 27 Oct 2013, 15:11
- Location: Gurgaon INDIA
Re: Required VBA codes solution of "SUMPRODUCT" formula
Now,the exact VBA codes in my hand to operate smooth working.Thanks Rudi for your efforts.Glad.
Regards
Pradeep Kumar Gupta
INDIA
Pradeep Kumar Gupta
INDIA
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Required VBA codes solution of "SUMPRODUCT" formula
Pleasure to help.
Have a good weekend...
Have a good weekend...
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.