How to go row wise and do calculation in an excel

Question

Private Sub CalculateCharges()
Worksheets("Sheet1").Activate
Dim i As Long
Dim temp As String
RowCount = ActiveSheet.UsedRange.Rows.Count
ActiveSheet.Range("G1").Select
ActiveCell.Offset(1, 0).Select
i = 0
While i < RowsCount
        If ActiveCell.Value Like "*W-M*" Then
        ActiveCell.Offset(0, 8).Value = ActiveCell.Offset(0, 5).Value + ActiveCell.Offset(0, 6).Value
        ActiveCell.Offset(1, 0).Select
        End If
        i = i + 1
Wend
End Sub

The above is my piece of code. Below is my excel look alike. How I can add up 2 values if a cell contain specific text value in it

What is the error that's happening? The code looks like it should run (although I would recommend not using select...see here: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). ActiveCell is finicky. — OpiesDad, Jul 11 '16 at 17:38

score 0 · Answer 1 · answered Jul 11 '16 at 17:43

You can simplify your code, without selecting each cell, which slows you down on large ranges.

You can also use InStr function as well

Private Sub CalculateCharges()

Dim sht1 As Excel.Worksheet

Set sht1 = ActiveWorkbook.Worksheets("Sheet1")

Dim i As Long
Dim temp As String

RowCount = sht1.UsedRange.Rows.Count

For i = 1 To RowCount
    If sht1.Cells(i, 6).Value Like "*W-M*" Then
    ' can use the InStr function instead
    'If InStr(1, sht1.Cells(i, 6).Value, "W-M", vbTextCompare) > 0 Then
        sht1.Cells(i, 8).Value = sht1.Cells(i, 5).Value + sht1.Cells(i, 6).Value
    End If
Next i

End Sub

How to go row wise and do calculation in an excel

1 Answers1