Validate chilean RUT using Target.Value on 2 columns

I'm trying to validate chilean RUT (12345678-9) using the math algorithm wich works perfectly but the procedure is applied only when the RUT it's stored in one cell. So when the user inputs a RUT it will display a MsgBox letting the user know the result.


Now, our database stores RUT in two parts, the RUT itself 01234567 and the check digit -the number after the score symbol- 8. I was thinking of a way to store and validate the RUT when the check digit column is changed but I can't figure how to store both values at the same time using Target.Value. Our excel "template" for the DB

RUT          DV

My code validating one cell:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("C")) Is Nothing Then
    On Error Resume Next

    rute = Target.Value
    arut = UCase(Target.Value)

    Rut = Replace("0000" & Rut, ".", "", 1)
    If InStr(1, Rut, "-") > 0 Then Rut = Left(rute, InStr(1, rute, "-") - 1)
    Rut = Right(rute, 8)
    suma = 0
    For i = 1 To 8
    suma = suma + Val(Mid(rute, i, 1)) * Val(Mid("32765432", i, 1))
    Next i
    dv = 11 - (suma Mod 11)
    If dv = 10 Then dv = "K"
    If dv = 11 Then dv = 0

    'If Right(arut, 1) = CStr(dv) Then MsgBox ("Rut " & rute & " Correcto")
    If Right(arut, 1) <> CStr(dv) Then MsgBox ("Rut: " & rute & " Incorrecto.")
    If rute = "" Then MsgBox ("Campo(s) modificado(s).")
End If
End Sub

Any hints on what should I do or other approach it's appreciated.

NOTE: In cell formula isn't possible due to requirements.

1 answer

  • answered 2019-03-13 20:24 Tim Williams

    Here's an example (omitting your checking code)

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rng As Range, rute, chk, c As Range
        Set rng = Intersect(Target, Me.Range("C:D"))
        If Not rng Is Nothing Then
            For Each c in rng.cells
                rute = c.entirerow.cells(3).value
                chk = c.entirerow.cells(4).value
                'validate rute and chk here and
                '  show msgbox if needed
            Next c   
        End If
    End Sub