SUM IF VBA Code

[austinandrei]

Hi,
I was trying to write the SUMIF formula in VBA code and trying to replace the name of the sheet as a variant as it will not be fix but I am having an error.
I have 2 sheets.
First Sheet(Sheet1):
Column A is having the criteria
Column H is where the result of the sumif should go
Second Sheet(Sheet2):
Column B is where the range are
Column J is where the sum_range are
in excel function, the formula that will be written in Column H of Sheet1 is:
=SUMIF(Sheet2!B:B,Sheet1!A2,Sheet2!J:J)
Sheet2 is not fix but it is always the sheet next to Sheet1.
writing in VBA code is below:

Sub Macro1()


lastrow = Cells(Rows.Count, 1).End(xlUp).Row
currsheet = ActiveSheet.Next.Name


For currrange = 2 To lastrow


    Range("H"&currrange).Value = "=SUMIF('currsheet'!B:B,"A" & currrange,'currsheet'!J:J)"

Next

End Sub

There is an error in the codes because I put "" in A but I just want to show the arrangement of the codes that I need. Thanks!

 

[vds1]

Your code should be like,

[COLOR=#0000ff]Sub[/COLOR] Macro1()


Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
currsheet = ActiveSheet.Next.Name
[COLOR=#0000ff]For[/COLOR] currrange = 2 [COLOR=#0000ff]To[/COLOR] Lastrow
    Range("H" & currrange).Value = "=SUMIF(" & currsheet & "!B:B, A" & currrange & ", " & currsheet & "!J:J)"
[COLOR=#0000ff]Next[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

Another way to perform ,

[COLOR=#0000ff]Sub[/COLOR] Macro2()


Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
currsheet = ActiveSheet.Next.Name
Range("H2:H" & Lastrow).Formula = "=SUMIF(" & currsheet & "!B:B, A2," & currsheet & "!J:J)"


[COLOR=#0000ff]End Sub[/COLOR]

 

[steve the fish]

Try:

Range("H" & currrange).Value = "=SUMIF('" & currsheet & "'!B:B,A" & currrange & ",'" & currsheet & "'!J:J)"

Note all your variables are now outside the double quotes.

 

[austinandrei]

hi VDS1, that solved my problem! thanks a lot!

 

[JackDanIce]

Try:

Sub Macro2()

Dim ws As Worksheet:    Set ws = ActiveSheet.Next
Dim x As Long:              x = Range("A" & Rows.Count).End(xlUp).Row
Dim sFormula As String

sFormula = "=SUMIF('@'!B:B,A2,'@'!J:J)"
sFormula = Replace(sFormula, "@", ws.Name)

Range("H2:H" & x).Formula = sFormula
    
End Sub

I've recently started using REPLACE alot as I find it's easier to 'read' a formula in VBA code without all the '&' and speach mark symbols littered everywhere.