Summing Values

[Egor Crotoe]

I am new to VBA and have a question relating to summing rows in my dynamic spreadsheet. To make matters easier to understand, I am attaching the spreadsheet and the vba code. I would like to sum the values in rows C30:F30 with the total shown in G30, and similar for rows H30:J30 with the total shown in K30. The spreadsheet is dymanic so rows are added and deleted as required.

Code for Spreadsheet

Sub MCC_01()

Application.ScreenUpdating = False

Dim PointsData As Worksheet 'data ccource
Dim MCC01 As Worksheet 'data destination
Dim ShortCode As String 'search string
Dim lrow As Integer 'the last row containing data
Dim i As Integer 'row counter
Dim ZZZ As String 'last row in PointsData
Dim j As Integer 'counter to deletes entire row

'set variables
Set MCC01 = Sheet1

Set PointsData = Sheet999

If Range("M5").Text <> "" Then

ShortCode = MCC01.Range("M5").Value

'goto datasheet and start searching and copying
PointsData.Select
lrow = Cells(Rows.Count, 1).End(xlUp).Row

'loop through the rows to find the matching records
For i = 7 To lrow

If Cells(i, 1) = ShortCode Then 'if the name in column A matches Short Code then copy the row

Range(Cells(i, 2), Cells(i, 20)).Copy 'copy rows B to T
MCC01.Select 'go to MCC01 work sheet
Range("A500").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats 'find the first blank row and paste
Application.CutCopyMode = False

Exit For

End If

If Cells(i, 1) = "ZZZ" Then

GoTo NOTFOUND

End If

Next i

MCC01.Select 'this is so that the report sheet is selected when the procedure ends

'Deletes rows last four rows

For j = 1 To 4

Range("A" & Rows.Count).End(xlUp).Select
ActiveCell.Offset(-1, 0).Select
ActiveCell.EntireRow.Delete

Next j

'Inserts descriptors

lrow = Cells(Rows.Count, 1).End(xlUp).Row

Range("A" & lrow + 2).Value = "Sub Total Points"
Range("A" & lrow + 3).Value = "Spare Capacity %"
Range("A" & lrow + 4).Value = "Total Points"
Range("B" & lrow + 3).Value = "=(B1)"
Range("M" & lrow + 4).Value = "Total Wired Points"

'counts columns values C to F and H to J

Range("C" & lrow + 2).Value = "=Sum(C13:C" & lrow & ")"
Range("D" & lrow + 2).Value = "=Sum(D13:D" & lrow & ")"
Range("E" & lrow + 2).Value = "=Sum(E13:E" & lrow & ")"
Range("F" & lrow + 2).Value = "=Sum(F13:F" & lrow & ")"
Range("G" & lrow + 2).Value = "=Sum(G13:G" & lrow & ")"
Range("H" & lrow + 2).Value = "=Sum(H13:H" & lrow & ")"
Range("I" & lrow + 2).Value = "=Sum(I13:I" & lrow & ")"
Range("J" & lrow + 2).Value = "=Sum(J13:J" & lrow & ")"
Range("N" & lrow + 4).Value = "=Sum(N13:N" & lrow & ")"
Range("O" & lrow + 4).Value = "=Sum(O13:O" & lrow & ")"
Range("P" & lrow + 4).Value = "=Sum(P13:P" & lrow & ")"
Range("Q" & lrow + 4).Value = "=Sum(Q13:Q" & lrow & ")"
Range("R" & lrow + 4).Value = "=Sum(R13:R" & lrow & ")"
Range("S" & lrow + 4).Value = "=Sum(S13:S" & lrow & ")"
Range("T" & lrow + 4).Value = "=Sum(T13:T" & lrow & ")"

'roundsup cells values C to F

Range("C" & lrow + 3).Value = "=Sum(C13:C" & lrow & ")* B3 + 0.5"
Range("D" & lrow + 3).Value = "=Sum(D13:D" & lrow & ")* B3 + 0.5"
Range("E" & lrow + 3).Value = "=Sum(E13:E" & lrow & ")* B3 + 0.5"
Range("F" & lrow + 3).Value = "=Sum(F13:F" & lrow & ")* B3 + 0.5"

'total cells values C to F (Range("C" & lrow + 4).Value = "=Sum(C" & lrow + 2 & ":C" & lrow + 3 & ")")

Range("C" & lrow + 4).Value = "=Sum(C" & lrow + 2 & ":C" & lrow + 3 & ")"
Range("D" & lrow + 4).Value = "=Sum(D" & lrow + 2 & ":D" & lrow + 3 & ")"
Range("E" & lrow + 4).Value = "=Sum(E" & lrow + 2 & ":E" & lrow + 3 & ")"
Range("F" & lrow + 4).Value = "=Sum(F" & lrow + 2 & ":F" & lrow + 3 & ")"

Else

NOTFOUND:

MCC01.Select 'returns to MCC01

MsgBox "ShortCode not found. This may be due to : Incorrect spelling : Short code should be lower case : Code does not exist in PointsData : No code entered. Please re-try"

End If

'clears short code entry
Range("M5").ClearContents

Application.ScreenUpdating = True

End Sub

 

[LazyBug]

Hello, @Egor Crotoe!

I am attaching the spreadsheet

Where is it?

rows C30:F30 with the total shown in G30, and similar for rows H30:J30

I do not understand what you mean. C and H are the columns, don't you think?

 

[Egor Crotoe]

LazyBug, Thank you for your reply. I have now found the solution to my question.

Egor

 

[LazyBug]

Oh, that's great. Have a nice day!