Estimating Spreadsheet-Summing

[Egor Crotoe]

I have created an estimating spreadsheet, which works as required, aprt from one area. The spreadsheet is dymanic so rows are added/subtracted during the estimating process. I would like to make two ranges dymanic "Engineered Points" outlined in red, and the "Software Points outlined in green. I have tried several options but none provide the required solution. Can you please provide a solution.

I am attaching a screen shot and the VBA code for guidance.

Regards and thank

Egor.

Sub MCC_01()

Application.ScreenUpdating = False

Dim PointsData As Worksheet 'data scource
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
Dim c As Integer
Dim d As Integer
Dim e As Integer
Dim f As Integer

'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(C13:C" & lrow & ")"
    Range("G" & lrow + 2).Value = WorksheetFunction.Sum(Range("C48:F48"))
   
    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 & ")"
   
    'Range("H4").Value = WorksheetFunction.Sum(Range("D4:G4"))
    
  '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

 

[Peter_SSs]

@Egor Crotoe
We understand that you are new here but please note the following points

• When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.
  
  
• Your code refers to certain ranges on the worksheet but your image does not show column or row headings so hard to match the code to the worksheet. In any case helpers cannot copy from an image to test anything. I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be. It can then also easily be copied. Consider experimenting in the Test Here forum.