VBA SUMIF syntax issues

I'm trying to write the following SUMIF statement, but the syntax it screwing me up. I'm doing something wrong...

In a For...Next statement:

Cell.Offset(0, 3) = Application.WorksheetFunction.SumIf(AWB.sSh.Range("A8:A" & lastRow), Cell.Value, AWB.sSh.Range("G8:G" & lastRow))

Here's what I'm trying to do:

1. use SUMIF via Application.WorksheetFunction.Sumif()

2. Set the first range to a workbook I have previously defined as "AWB", to a sheet on that workbook that I have previously defined as "sSH", within the range "A8:A" & lastRow), I have previously defined "lastRow"

3. Set the second range to the current Cell.Value in the For statement

4. Set the third range again to AWB.sSH.Range("G8:G" & lastRow)

Logically, I understand what I'm trying to do, but I'm not sure how to make this work in VBA.

Hi

AWB.sSh.Range("A8:A" & lastRow)

This syntax is wrong. sSh is not a method or a property of AWB.

If you defined sSh as a worksheet in workbook AWB, use instead:

cell.Offset(0, 3) = Application.WorksheetFunction.SumIf(sSh.Range("A8:A" & lastRow), cell.Value, sSh.Range("G8:G" & lastRow))

This should work. If it doesn't please post the initialisation of AWB and sSh.

I've previously defined a certain workbook in my sub as AWB, and a sheet in that workbook as sSH.

I can't use

.SumIf(sSh.Range("A8:A" & lastRow)

because that doesn't link back to the AWB workbook at all. The first and third arguments in this link to this other workbook.

The syntax may be totally wrong, but the variables are defined correctly, because I can do something like this:

AWB.sSH.Range("A1").Value = "hello"

and it works just fine.

Dim sSH   as Worksheet
Dim AWB  as Workbook

Set AWB = Activeworkbook
Set sSH = Sheets(1)

I used the macro recorder to see how Excel did it itself, and then used that to work out the error in my code....

This is now working:

Cell.Offset(0, 3) = "=SUMIF('[" & AWB.Name & "]Data'!$A$8:A" & lastRow & ",A2,'[" & AWB.Name & "]Data'!$G$8:G" & lastRow & ")"

I'd still like (for other applications) to know whether or not using "sSH" instead of "Data!" (the actual sheet name) is possible, but I haven't gotten it to work yet. This statement works however, so for now it's good enough