#### austinandrei

##### Board Regular

- Joined
- Jun 7, 2014

- Messages
- 117

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:

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!

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:

Code:

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

Last edited: