VBA Cell Value Update

AoN · Mar 11th 2014

Short story: I need to update a budget template to automatically multiply a cell value by that of another. There are two ranges involved, E62:P75 and E84:P86. Right now I'm just trying to get it working with the first range. In the corresponding D for a cell is the per month rate. For each month E:P, you should be enter in an integer and the script then replaces that value with the value times per month rate. If the field is blank or not an integer, then it defaults to 0.

I keep getting "Run-time error: '-2147417848 (80010108)': Method 'Value' of object 'Range' failed".

Here's my code:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    If Not Intersect(Target, Range("E62:P75")) Is Nothing Then
        For i = 62 To 75
            For j = 5 To 16
                If IsNumeric(Cells(i, j).Value) Then
                    If Cells(i, j).Value < 100 Then
'                        k = Cells(i, j).Value * Cells(i, 4).Value
'                        Cells(i, j).Value = k
                        Cells(i, j).Value = Cells(i, j).Value * Cells(i, 4).Value
                    End If
                Else
                    Cells(i, j).Value = 0
                End If
            Next j
        Next i
    End If
End Sub

Display More

I've also attached the spreadsheet. Any suggestions?

**Kenneth Hobson** · Mar 11th 2014

Re: VBA Cell Value Update

Did you really want to iterate the whole intersected range? The way you have it now, one entry in the range will loop through several cells. If you posted one sample input and the expected result, I could better see just what you need and offer a more simple solution I suspect.

In any case, when working with event code like that, one usually wants to add Application code for speed alone if nothing else.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    
    If Intersect(Target, Range("E62:P75")) Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    
    For i = 62 To 75
        For j = 5 To 16
            If IsNumeric(Cells(i, j).Value) Then
                If Cells(i, j).Value < 100 Then
'                        k = Cells(i, j).Value * Cells(i, 4).Value
'                        Cells(i, j).Value = k
                    Cells(i, j).Value = Cells(i, j).Value * Cells(i, 4).Value
                End If
            Else
                Cells(i, j).Value = 0
            End If
        Next j
    Next i
    
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Display More

AoN · Mar 11th 2014

Re: VBA Cell Value Update

Wow, that does exactly what I was looking for.

I know the code is a bit wasteful in that it forces an update to all of the cells in the range at the same time, but that also ensures that all entries are validated regardless of what is modified.

As for an example of what I would ideally want to see happen, if you enter 3 into E62, it updates to 3*D62, or 17,490. Having it update all the fields at once is cool, but I do understand it takes more effort for the system to do so. So, is it possible to limit it to updating only the modified field if the field was in the specified range, or would that be getting too complex of code that it wouldn't be worth doing?

**Kenneth Hobson** · Mar 12th 2014

Re: VBA Cell Value Update

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range, r As Range
     
    Set r = Intersect(Target, Range("E62:P75,E84:P86"))
    If r Is Nothing Then Exit Sub
     
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
     
    For Each c In r.Cells
      With c
        If IsNumeric(.Value) Then
          If .Value < 100 Then
            .Value = .Value * Range("D" & .Row).Value
          End If
          Else
            .Value = 0
        End If
      End With
    Next c
     
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Display More

AoN · Mar 13th 2014

Re: VBA Cell Value Update

Perfect! Thank you for this, Kenneth Hobson!

VBA Cell Value Update

Participate now!

Share