lundi 3 décembre 2018

Update sheet1 when a cell in sheet2 meets criteria - Excel and VBA

With VBA, I'm trying to get an update in a cell-value when it meets the criteria given to another sheets cell. So far it gives me an "Error 5 - Invalid procedure call or argument".

Without this "IF - Then" it worked fine. Now I'd like to refine my procedure, by only updating cell1 from table "t_Magazijnvoorraad" when in table "t-Bestellijst" the cell in column "VERZONDEN" has a value "J".

Dim rng1, rng2, cell1, cell2 As Range
Dim lastRow1 As Long
lastRow1 = Range("t_Magazijnvoorraad[PRODUCTCODE]").End(xlUp).Row
Set rng1 = Range("t_Magazijnvoorraad[PRODUCTCODE]")

Dim lastRow2 As Long
lastRow2 = Range("t_Bestellijst[PRODUCTCODEKOPIE]").End(xlUp).Row
Set rng2 = Range("t_Bestellijst[PRODUCTCODEKOPIE]")

If Cells("t_Bestellijst[VERZONDEN]") = "j" Then
  For Each cell1 In rng1
  If IsEmpty(cell1.Value) Then Exit For

  For Each cell2 In rng2
  If IsEmpty(cell2.Value) Then Exit For

  If cell1 = cell2 Then
    cell1.Offset(0, 5) = cell1.Offset(0, 5) - cell2.Offset(0, 1)
    End If
  Next cell2
  Next cell1
Else: End If

The error occurs at:

If Cells("t_Bestellijst[VERZONDEN]") = "j" Then

Aucun commentaire:

Enregistrer un commentaire