0
Sub GrossSplit()

Dim i As Integer

Set governmentShare = Range("C9")
Set contractorShare = Range("G9")
Set grossRevenue = Range("E6")
Set DMO = Range("E12")
Set cost = Range("I12")
Set taxableIncome = Range("G14")
Set tax = Range("G17")
Set governmentTake = Range("C21")
Set contractortake = Range("G21")

For i = 0 To WorksheetFunction.Count(Worksheets("GS CALCULATION").Range("F6:F40"))

    If Range("J5").Value = i Then
        governmentShare.Value = Worksheets("GS CALCULATION").Cells(i + 6, "AA")
        contractorShare.Value = Worksheets("GS CALCULATION").Cells(i + 6, "Z")
        grossRevenue.Value = governmentShare + contractorShare
        DMO.Value = Worksheets("GS CALCULATION").Cells(i + 6, "AB") - Worksheets("GS CALCULATION").Cells(i + 6, "AC")
        cost.Value = Application.WorksheetFunction.Sum(Sheets("GS CALCULATION").Range(Cells(i + 6, "AD"), Cells(i + 6, "AF")))
        taxableIncome.Value = Worksheets("GS CALCULATION").Cells(i + 6, "AG")
        tax.Value = Worksheets("GS CALCULATION").Cells(i + 6, "AH")
        governmentTake.Value = Worksheets("GS CALCULATION").Cells(i + 6, "AL")
        contractortake.Value = Worksheets("GS CALCULATION").Cells(i + 6, "AI")
    Exit For
    End If
    
Next i

End Sub

Here, I have some following codes that try to calculate some parameters and then stored in a variable. But i got some obstacles here that i can't applied this line of code:

cost.Value = Application.WorksheetFunction.Sum(Sheets("GS CALCULATION").Range(Cells(i + 6, "AD"), Cells(i + 6, "AF")))

I tried to sum values from another sheet but it didn't work by using this line of code. Can anyone help how to overcome this? Thank you.

Grha Gandana P
  • 5
  • 1
  • 3
  • Apparently, you need to specify sheet for `Cells(i + 6, "AD")`. Else it will be Activesheet. See [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Алексей Р Jul 04 '21 at 17:53
  • Your unqualified `Cells` references do not automatically assume you mean the same worksheet as you would think. They need to be fully qualified as well. – braX Jul 04 '21 at 19:52

0 Answers0