+ Reply to Thread
Results 1 to 3 of 3

Dropdown list not show data in F22 in my sheets for Quote

Hybrid View

  1. #1
    Forum Expert
    Join Date
    11-28-2015
    Location
    indo
    MS-Off Ver
    2016 64 bitt
    Posts
    1,433

    Re: Dropdown list not show data in F22 in my sheets for Quote

    You has using double if should be one is elseif, or check for set ws is correct name of sheet
    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
    Last edited by daboho; 04-28-2024 at 01:36 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Show a conditional dropdown list of data validation only if a specific cell equals a value
    By jeriss in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-19-2022, 01:24 PM
  2. [SOLVED] Data Extraction from Other Sheets Based on the Value from the Dropdown List
    By franceslin in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 09-13-2021, 11:55 AM
  3. Show a list of data based from dropdown selection
    By reecerito in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-09-2016, 03:18 AM
  4. Replies: 2
    Last Post: 03-31-2014, 01:32 AM
  5. Retrieve Data from other sheets using dropdown list.
    By lemuel in forum Excel General
    Replies: 8
    Last Post: 09-28-2012, 09:35 AM
  6. Making a data list from a separate quote sheets
    By Benjando in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-08-2010, 12:19 AM
  7. Replies: 6
    Last Post: 07-17-2009, 01:35 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1