Perform calculation on cells if they have data

Question

I am a bit new to the macro's in excel and I am trying to find a way to adjust one of the macros I currently have in an excel file. I have a calculation that takes the columns D and E then subtracts D from E and adds it to the value of column B. here is the current code and also the sheet being used.

Sub InvAdj()
'
' InvAdj Macro
'
' Keyboard Shortcut: Ctrl+Shift+I
'
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Quality"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]-RC[2]+RC[3]"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C33")
    Range("C2:C33").Select
    Columns("C:C").Select
    Selection.Copy
    Columns("B:B").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=True, Transpose:=False
    Columns("C:C").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("D2:E33").Select
    Selection.ClearContents
    Range("F1").Select
End Sub

Also the image link that you gave doesn't work. can you provide another link. TIP: [INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Dec 18 '13 at 19:37

score 0 · Answer 1 · answered Dec 18 '13 at 19:52

0

Not sure if this is what you are trying?

Sub InvAdj()
    Dim ws As Worksheet

    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        .Range("B1").Value = "Quality"

        For i = 2 To 33
            '~~> Check if all cells have data
            If Len(Trim(.Range("B" & i).Value)) <> 0 And _
            en(Trim(.Range("D" & i).Value)) <> 0 And _
            en(Trim(.Range("E" & i).Value)) <> 0 Then
                'B = B + (E - D)
                .Range("B" & i).Value = .Range("B" & i).Value + _
                                        (.Range("E" & i).Value - .Range("D" & i).Value)
            End If
        Next i
    End With
End Sub

answered Dec 18 '13 at 19:52

Siddharth Rout

147,039
17
206
250

Thats pretty close, I do not want the limit to be just set for 33 though, I want to be able to say if column D or E have data in them to perform the calculation and add the sum of that to column B, if it does not have a value or is null in anyway to stop the operation. I think what I need is a for loop of some sort that loops through say column D until the cell is empty and then to set that as the value instead of a set number like 33. I hope that makes sense – user3116412 Dec 20 '13 at 00:06
So if I give you a variable which can say hold "D" or "E" would that be good enough? – Siddharth Rout Dec 20 '13 at 00:08
I suppose maybe a do while the value of d:d is not null or "" then perform the function would work to. I just haven't worked with excel much and do not know how to write that function properly and clean – user3116412 Dec 20 '13 at 00:08
please see my last comment – Siddharth Rout Dec 20 '13 at 00:09
Yes I think that would be a great help! – user3116412 Dec 22 '13 at 17:14

Perform calculation on cells if they have data

1 Answers1