Problem with seting cell formula through vba (1 Viewer)


New member
Local time
Today, 09:10
Jan 27, 2020
Hi there!

I'm getting crazy with a code i wrote to set a formula on a cell through vba.
I have a workbook with as many sheets as the days in the month. in each sheet i have a table with 3 columns: client, address and value.
When i change a value in the first column (selecting from a validation dropdown list) my code should do 2 things:
  1. set the validation for the second column to the houses of the particular client
  2. set a formula of the cell in the third column that will lookup for a specific value depending on the type of the day that sheet represent. The type of the day is in the cell B4 and the values are in another sheet on a table that lists the houses.
Here is my code of the change event of the sheets that represent the month days:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        setValidation Target.Value, Target.row, ActiveWorkbook.ActiveSheet
    End If
End Sub

And this is the code for the setValidation function:

Public Sub setValidation(ByVal client As String, row As Integer, sheet As Worksheet)
    Dim tbl As ListObject
    Dim sortcolumn1 As Range
    Dim sortcolumn2 As Range
    Dim validationRange As Range
    Dim cellRange As Range
    Set tbl = Sheet35.ListObjects("Houses")
    Set sortcolumn1 = Range("Houses[Client]")
    Set sortcolumn2 = Range("Houses[Address]")
    With tbl.Sort
        .SortFields.Add Key:=sortcolumn1, SortOn:=xlSortOnValues, Order:=xlAscending
        .SortFields.Add Key:=sortcolumn2, SortOn:=xlSortOnValues, Order:=xlAscending
        .Header = xlYes
    End With

    Set validationRange = Range("C" & Application.WorksheetFunction.Match(client, tbl.ListColumns(1).DataBodyRange, 0) + 3 & _
                          ":C" & Application.WorksheetFunction.Match(client, tbl.ListColumns(1).DataBodyRange, 1) + 3)

    Set cellRange = sheet.Range("C" & row)
    With cellRange.Validation
        .Add Type:=xlValidateList, Formula1:="=" & "Houses!" & validationRange.Address
    End With
    sheet.Range("D7").Formula = "=IFNA(IF($B$4=""Work Day"";VLOOKUP([Address];Houses[[Address]:[Weekends]];2;0);VLOOKUP([Address];Houses[[Address]:[Weekends]];3;0));"""")"
End Sub

Now, the validation rule part works everywhere, but the cell formula only works for the table of the first day, but fails in all other days...
I can't understand why... Searched a lot in the web but couldn't find anything...
Can somebody help please?


Users who are viewing this thread

Top Bottom