Problem with seting cell formula through vba

[disgracept]

good afternoon to all!

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.Clear
        .SortFields.Add Key:=sortcolumn1, SortOn:=xlSortOnValues, Order:=xlAscending
        .SortFields.Add Key:=sortcolumn2, SortOn:=xlSortOnValues, Order:=xlAscending
        .Header = xlYes
        .Apply
    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
        .Delete
        .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?

Thanks

 

[gallen]

sheet.Range("D7").Formula = "=IFNA(IF($B$4=""Work Day"";VLOOKUP([Address];Houses[[Address]:[Weekends]];2;0);VLOOKUP([Address];Houses[[Address]:[Weekends]];3;0));"""")"

this line has semicolons instead of of commas?

 

[disgracept]

Mu Excel uses semicolons in formulas in the worksheets.
In chá should be commas?

 

[disgracept]

"It should..."
Sorry... i'm in the phone and the autocorrect is a pain...

But, anyway, if it should be commas why does it work on the first sheet and not in the others?

 

[disgracept]

this line has semicolons instead of of commas?

Ok...
Just changed the semicolons to commas and... it worked!
Thanks a lot Gallen! Now it's everything working!

 

[gallen]

Ok...
Just changed the semicolons to commas and... it worked!
Thanks a lot Gallen! Now it's everything working!

Happy it's working, not sure why it works with semicolons on just 1 sheet but at least it works.

 

[disgracept]

Mu Excel uses semicolons in formulas in the worksheets.
In chá should be commas?

Happy it's working, not sure why it works with semicolons on just 1 sheet but at least it works.

I don’t know also why that happened... It’s another Excel mistery!
But, as you said, it works so... i’m happy!
Thanks again!
(Is there a way to set the thread as solved?)

 

[MARK858]

VBA is based on US-English settings (see the various issues we have with date formats in multiple posts) and so you use comma for the delimiter when using Formula, FormulaArray or FormulaR1C1.

 

[gallen]

(Is there a way to set the thread as solved?)

No there isn't. It will just fade away....