disgracept
New member
- Local time
- Today, 08:26
- Joined
- Jan 27, 2020
- Messages
- 21
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:
And this is the code for the setValidation function:
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
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:
- set the validation for the second column to the houses of the particular client
- 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.
Code:
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:
Code:
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