Private Sub Worksheet_Change(ByVal Target As range)
Dim wsClient As Worksheet
Set wsClient = ThisWorkbook.Sheets("Klient-Client")
Dim lookupRange As range
Dim rowNo As long
Dim ws As Worksheet
If Not Intersect(Target, range("M3")) Is Nothing Then
Dim selectedValueM3 As String
selectedValueM3 = Target.Value
Set lookupRange = wsClient.Columns("A")
On Error Resume Next
rowNo = Application.WorksheetFunction.Match(selectedValueM3, lookupRange, 0)
On Error GoTo 0
If rowNo = 0 Then Exit Sub
Set ws = ThisWorkbook.Sheets("Kwotasie")
' Update relevant cells for dropdown in M3
With ws
.range("C15").Value = wsClient.Cells(rowNo, "A").Value
.range("C16").Value = wsClient.Cells(rowNo, "B").Value
.range("C17").Value = wsClient.Cells(rowNo, "C").Value
.range("C18").Value = wsClient.Cells(rowNo, "D").Value
.range("C19").Value = wsClient.Cells(rowNo, "E").Value
.range("H3").Value = wsClient.Cells(rowNo, "F").Value
.range("H4").Value = wsClient.Cells(rowNo, "G").Value
.range("H5").Value = wsClient.Cells(rowNo, "H").Value
.range("H6").Value = wsClient.Cells(rowNo, "I").Value
.range("H7").Value = wsClient.Cells(rowNo, "J").Value
.range("D22").Value = wsClient.Cells(rowNo, "K").Value
.range("E22").Value = wsClient.Cells(rowNo, "L").Value
.range("D23").Value = wsClient.Cells(rowNo, "M").Value
.range("E23").Value = wsClient.Cells(rowNo, "N").Value
.range("D24").Value = wsClient.Cells(rowNo, "O").Value
.range("E24").Value = wsClient.Cells(rowNo, "P").Value
.range("D25").Value = wsClient.Cells(rowNo, "Q").Value
.range("E25").Value = wsClient.Cells(rowNo, "R").Value
' Update the rest of the cells as needed...
End With
ElseIf Not Intersect(Target, range("N3")) Is Nothing Then
Dim selectedValueN3 As Variant
selectedValueN3 = Target.Value
Set lookupRange = wsClient.Columns("G")
' Check if the lookup range is not empty and the lookup value is not an empty string
If WorksheetFunction.CountA(lookupRange) > 0 And Trim(selectedValueN3) <> "" Then
On Error Resume Next
rowNo = Application.WorksheetFunction.Match(selectedValueN3, lookupRange, 0)
On Error GoTo 0
If rowNo = 0 Then Exit Sub
Set ws = ThisWorkbook.Sheets("Kwotasie") 'is this has correct name of sheet
' Update relevant cells for dropdown in N3
With ws
.range("C15").Value = wsClient.Cells(rowNo, "A").Value
.range("C16").Value = wsClient.Cells(rowNo, "B").Value
.range("C17").Value = wsClient.Cells(rowNo, "C").Value
.range("C18").Value = wsClient.Cells(rowNo, "D").Value
.range("C19").Value = wsClient.Cells(rowNo, "E").Value
.range("H3").Value = wsClient.Cells(rowNo, "F").Value
.range("H4").Value = wsClient.Cells(rowNo, "G").Value
.range("H5").Value = wsClient.Cells(rowNo, "H").Value
.range("H6").Value = wsClient.Cells(rowNo, "I").Value
.range("H7").Value = wsClient.Cells(rowNo, "J").Value
.range("D22").Value = wsClient.Cells(rowNo, "K").Value
.range("E22").Value = wsClient.Cells(rowNo, "L").Value
.range("D23").Value = wsClient.Cells(rowNo, "M").Value
.range("E23").Value = wsClient.Cells(rowNo, "N").Value
.range("D24").Value = wsClient.Cells(rowNo, "O").Value
.range("E24").Value = wsClient.Cells(rowNo, "P").Value
.range("D25").Value = wsClient.Cells(rowNo, "Q").Value
.range("E25").Value = wsClient.Cells(rowNo, "R").Value
End With
MsgBox "Selected Value M3: " & selectedValueM3
MsgBox "Lookup Range: " & lookupRange.Address
MsgBox "Row Number: " & rowNo
End If
End If
End Sub
Private Sub Worksheet_Activate()
' Insert formula into cell F22
range("F22").Formula = "=IFERROR(VLOOKUP(D22,'Dienste'!$C:$D,2,FALSE),IFERROR(VLOOKUP(D22,'Quote'!$C:$D,2,FALSE),""""))"
range("F23").Formula = "=IFERROR(VLOOKUP(D23,'Dienste'!$C:$D,2,FALSE),IFERROR(VLOOKUP(D23,'Quote'!$C:$D,2,FALSE),""""))"
range("F24").Formula = "=IFERROR(VLOOKUP(D24,'Dienste'!$C:$D,2,FALSE),IFERROR(VLOOKUP(D24,'Quote'!$C:$D,2,FALSE),""""))"
range("F25").Formula = "=IFERROR(VLOOKUP(D25,'Dienste'!$C:$D,2,FALSE),IFERROR(VLOOKUP(D25,'Quote'!$C:$D,2,FALSE),""""))"
range("F26").Formula = "=IFERROR(VLOOKUP(D26,'Dienste'!$C:$D,2,FALSE),IFERROR(VLOOKUP(D26,'Quote'!$C:$D,2,FALSE),""""))"
range("F27").Formula = "=IFERROR(VLOOKUP(D27,'Dienste'!$C:$D,2,FALSE),IFERROR(VLOOKUP(D27,'Quote'!$C:$D,2,FALSE),""""))"
range("F28").Formula = "=IFERROR(VLOOKUP(D29,'Dienste'!$C:$D,2,FALSE),IFERROR(VLOOKUP(D28,'Quote'!$C:$D,2,FALSE),""""))"
range("F29").Formula = "=IFERROR(VLOOKUP(D29,'Dienste'!$C:$D,2,FALSE),IFERROR(VLOOKUP(D29,'Quote'!$C:$D,2,FALSE),""""))"
End Sub
Bookmarks