VBA Change Event

[smwexcel]

I'm in the process of building a complex workbook that requires me to dive into manual calculations (which is an uncharted territory for me). I need to be able to calculate specific areas of the sheet which is working correctly, however I need to be able to drag a series and calculate each row during this process.

If A1 is Modified B1 Calculates
If A1:A5 (Drag Series) is Modified B1:B5 Calculates = Code Results in Run-Time Error '13': Type Mismatch

VBA (Sheet1)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
     If Target.Column = 1 Then
     ThisRow = Target.Row
               If Target.Value >= 0 Then
               Range("B" & ThisRow).Calculate
               End If
     End If
End Sub

Mod edit: Code tags added

 

[Luke M]

Welcome to the forums, smwexcel!

Before going too far, I would caution that the better route may be to leave calculation mode in automatic. Usually, we switch to manual mode not because XL needs to, but because of bad workbook design. That said, this is how you could improve your code to only update the needed cells.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rngChange As Range
Dim c As Range

Set rngChange = Intersect(Me.Range("A:A"), Target)

'Check if we need to do anything
If rngChange Is Nothing Then Exit Sub

'Update col B
rngChange.Offset(0, 1).Calculate
End Sub
[/code

 

[smwexcel]

Is it possible to extend the range across multiple columns with maintaining a single row, as in I want

Set rngChange = Intersect(Me.Range("C:C"), Target)
rngChange.Offset(0, 1 through 14).Calculate

End If

Set rngChange = Intersect(Me.Range("R:R"), Target)
rngChange.Offset(0, 1 through 14).Calculate

End If

Or, do I need to list each column out?

 

[Luke M]

Sure thing. The method you'll want to use is called Resize. So, let's say we wanted to calculate columns B:O, which is a total of 4 columns. Code line would be:

rngChange.Offset(0,1).Resize(,14).Calculate

So, we start at are range in col A, offset 1 column to get into col B, and then resize to be in B:O.

 

[smwexcel]

Going back to yours initial replay; "Avoiding Manual Mode", I could do this if I use vba calculate the value and place the result into the next cell.

Formula:
{=INDEX(F4PK,MATCH(1,("SCD5_"=F4SCDCode)*(INDIRECT("RC[-1]",0)=F4ProductCode),0))}

How would I go about hard coding this formula into vba:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rngChange As Range
Dim c As Range

Set rngChange = Intersect(Me.Range("A6:A127"), Target)

'Check if we need to do anything
If rngChange Is Nothing Then Exit Sub

'Update col B
rngChange.Offset(0, 1).Formula = " {=INDEX(F4PK,MATCH(1,("SCD5_"=F4SCDCode)*(INDIRECT("RC[-1]",0)=F4ProductCode),0))}"
'Return "Value" to Column B 
End Sub

 

[Luke M]

I'm guessing those are named ranges in your formula? I think you could do something like this:

Dim myVal As Variant
myVal = Range("F4PK").Cells(WorksheetFunction.Match(1, (Range("R4SCDCode").Value = "SCD5_") * _
    (Range(Range("A2").Value).Value = Range("F4ProductCode").Value), 0))

Note that code will error out though if the MATCH can't find anything.