Searching Data in a Combo Box and Update Underlying Data in another sheet.

jaysteruk

New Member
Joined
Jun 26, 2008
Messages
2
Hello All I currently have a combo box using the following code:
Code:
Sub DropDown4_Change()
    Dim LProject_Ref As Integer
 
    Dim Lstair_Planned_Date_1 As String
    Dim Lstair_Planned_Date_2 As String
    Dim Lstair_PLanned_Date_3 As String
    Dim Lstair_Planned_Date_4 As String
    Dim Lstair_actual_Date_1 As String
    Dim Lstair_actual_Date_2 As String
    Dim Lstair_actual_Date_3 As String
    Dim Lstair_actual_Date_4 As String
    Dim LElderly_Alarms_planned_1 As String
    Dim LElderly_Alarms_planned_2 As String
    Dim LElderly_Alarms_planned_3 As String
    Dim LElderly_Alarms_planned_4 As String
    Dim LElderly_Alarms_actual_1 As String
    Dim LElderly_Alarms_actual_2 As String
    Dim LElderly_Alarms_actual_3 As String
    Dim LElderly_Alarms_actual_4 As String
    Dim LFire_Alarms_planned_1 As String
    Dim LFire_Alarms_Planned_2 As String
    Dim LFire_Alarms_Planned_3 As String
    Dim LFire_Alarms_Planned_4 As String
    Dim LFire_Alarms_actual_1 As String
    Dim LFire_Alarms_actual_2 As String
    Dim LFire_Alarms_actual_3 As String
    Dim LFire_Alarms_actual_4 As String
    Dim LFire_equipment_planned_1 As String
    Dim LFire_equipment_planned_2 As String
    Dim LFire_equipment_planned_3 As String
    Dim LFire_equipment_planned_4 As String
    Dim LFire_equipment_actual_1 As String
    Dim LFire_equipment_actual_2 As String
    Dim LFire_equipment_actual_3 As String
    Dim LFire_equipment_actual_4 As String
    Dim LEmerg_Lighting_planned_1 As String
    Dim LEmerg_Lighting_planned_2 As String
    Dim LEmerg_Lighting_Planned_3 As String
    Dim LEmerg_Lighting_planned_4 As String
    Dim LEmerg_Lighting_actual_1 As String
    Dim LEmerg_Lighting_actual_2 As String
    Dim LEmerg_Lighting_actual_3 As String
    Dim LEmerg_Lighting_actual_4 As String
    Dim LPat_test_planned_1 As String
    Dim LPat_test_planned_2 As String
    Dim LPat_test_planned_3 As String
    Dim LPat_test_planned_4 As String
    Dim LPat_test_actual_1 As String
    Dim LPat_test_actual_2 As String
    Dim LPat_test_actual_3 As String
    Dim LPat_test_actual_4 As String
    Dim Lbed_hoist_planned_1 As String
    Dim Lbed_hoist_planned_2 As String
    Dim Lbed_hoist_planned_3 As String
    Dim Lbed_hoist_planned_4 As String
    Dim Lbed_hoist_actual_1 As String
    Dim Lbed_hoist_actual_2 As String
    Dim Lbed_hoist_actual_3 As String
    Dim Lbed_hoist_actual_4 As String
    Dim Lin_block_lift_planned_1 As String
    Dim Lin_block_lift_planned_2 As String
    Dim Lin_block_lift_planned_3 As String
    Dim Lin_block_lift_planned_4 As String
    Dim Lin_block_lift_actual_1 As String
    Dim Lin_block_lift_actual_2 As String
    Dim Lin_block_lift_actual_3 As String
    Dim Lin_block_lift_actual_4 As String
    Dim LMisc_planned_1 As String
    Dim LMisc_planned_2 As String
    Dim LMisc_actual_1 As String
    Dim LMisc_actual_2 As String
 
    Dim LRow As Long
    Dim LFound As Boolean
 
    'Retrieve Project_Ref number number
    LProject_Ref = Range("A8").Value
 
    'Move to Full List
    Sheets("Full List").Select
 
    LFound = False
 
    LRow = 2
 
    Do While LFound = False
        'Found matching Project_Ref, now update information on Date Entry
        If Range("A" & LRow).Value = LProject_Ref Then
            LFound = True
            Lstair_Planned_Date_1 = Range("J" & LRow).Value
            Lstair_Planned_Date_2 = Range("K" & LRow).Value
            Lstair_PLanned_Date_3 = Range("L" & LRow).Value
            Lstair_Planned_Date_4 = Range("M" & LRow).Value
            Lstair_actual_Date_1 = Range("N" & LRow).Value
            Lstair_actual_Date_2 = Range("O" & LRow).Value
            Lstair_actual_Date_3 = Range("P" & LRow).Value
            Lstair_actual_Date_4 = Range("Q" & LRow).Value
            LElderly_Alarms_planned_1 = Range("Z" & LRow).Value
            LElderly_Alarms_planned_2 = Range("AA" & LRow).Value
            LElderly_Alarms_planned_3 = Range("AB" & LRow).Value
            LElderly_Alarms_planned_4 = Range("AC" & LRow).Value
            LElderly_Alarms_actual_1 = Range("AD" & LRow).Value
            LElderly_Alarms_actual_2 = Range("AE" & LRow).Value
            LElderly_Alarms_actual_3 = Range("AF" & LRow).Value
            LElderly_Alarms_actual_4 = Range("AG" & LRow).Value
            LFire_Alarms_planned_1 = Range("DH" & LRow).Value
            LFire_Alarms_Planned_2 = Range("DI" & LRow).Value
            LFire_Alarms_Planned_3 = Range("DJ" & LRow).Value
            LFire_Alarms_Planned_4 = Range("DK" & LRow).Value
            LFire_Alarms_actual_1 = Range("DL" & LRow).Value
            LFire_Alarms_actual_2 = Range("DM" & LRow).Value
            LFire_Alarms_actual_3 = Range("DN" & LRow).Value
            LFire_Alarms_actual_4 = Range("DO" & LRow).Value
            LFire_equipment_planned_1 = Range("CQ" & LRow).Value
            LFire_equipment_planned_2 = Range("CR" & LRow).Value
            LFire_equipment_planned_3 = Range("CS" & LRow).Value
            LFire_equipment_planned_4 = Range("CT" & LRow).Value
            LFire_equipment_actual_1 = Range("CU" & LRow).Value
            LFire_equipment_actual_2 = Range("CV" & LRow).Value
            LFire_equipment_actual_3 = Range("CW" & LRow).Value
            LFire_equipment_actual_4 = Range("CX" & LRow).Value
            LEmerg_Lighting_planned_1 = Range("EX" & LRow).Value
            LEmerg_Lighting_planned_2 = Range("EY" & LRow).Value
            LEmerg_Lighting_Planned_3 = Range("EZ" & LRow).Value
            LEmerg_Lighting_planned_4 = Range("FA" & LRow).Value
            LEmerg_Lighting_actual_1 = Range("FB" & LRow).Value
            LEmerg_Lighting_actual_2 = Range("FC" & LRow).Value
            LEmerg_Lighting_actual_3 = Range("FD" & LRow).Value
            LEmerg_Lighting_actual_4 = Range("FE" & LRow).Value
            LPat_test_planned_1 = Range("BI" & LRow).Value
            LPat_test_planned_2 = Range("BJ" & LRow).Value
            LPat_test_planned_3 = Range("BK" & LRow).Value
            LPat_test_planned_4 = Range("BL" & LRow).Value
            LPat_test_actual_1 = Range("BM" & LRow).Value
            LPat_test_actual_2 = Range("BN" & LRow).Value
            LPat_test_actual_3 = Range("BO" & LRow).Value
            LPat_test_actual_4 = Range("BP" & LRow).Value
            Lbed_hoist_planned_1 = Range("AS" & LRow).Value
            Lbed_hoist_planned_2 = Range("AT" & LRow).Value
            Lbed_hoist_planned_3 = Range("AU" & LRow).Value
            Lbed_hoist_planned_4 = Range("AV" & LRow).Value
            Lbed_hoist_actual_1 = Range("AW" & LRow).Value
            Lbed_hoist_actual_2 = Range("AX" & LRow).Value
            Lbed_hoist_actual_3 = Range("AY" & LRow).Value
            Lbed_hoist_actual_4 = Range("AZ" & LRow).Value
            Lin_block_lift_planned_1 = Range("BZ" & LRow).Value
            Lin_block_lift_planned_2 = Range("CA" & LRow).Value
            Lin_block_lift_planned_3 = Range("CB" & LRow).Value
            Lin_block_lift_planned_4 = Range("CC" & LRow).Value
            Lin_block_lift_actual_1 = Range("CD" & LRow).Value
            Lin_block_lift_actual_2 = Range("CE" & LRow).Value
            Lin_block_lift_actual_3 = Range("CF" & LRow).Value
            Lin_block_lift_actual_4 = Range("CG" & LRow).Value
            LMisc_planned_1 = Range("EG" & LRow).Value
            LMisc_planned_2 = Range("EG" & LRow).Value
            LMisc_actual_1 = Range("EG" & LRow).Value
            LMisc_actual_2 = Range("EG" & LRow).Value
 
            Sheets("Date Entry").Select
            Range("D14").Value = Lstair_Planned_Date_1
            Range("G14").Value = Lstair_Planned_Date_2
            Range("J14").Value = Lstair_PLanned_Date_3
            Range("M14").Value = Lstair_Planned_Date_4
            Range("D16").Value = Lstair_actual_Date_1
            Range("G16").Value = Lstair_actual_Date_2
            Range("J16").Value = Lstair_actual_Date_3
            Range("M16").Value = Lstair_actual_Date_4
            Range("D50").Value = Lelderly_alarm_planned_1
            Range("G50").Value = Lelderly_alarm_planned_2
            Range("J50").Value = Lelderly_alarm_planned_3
            Range("M50").Value = Lelderly_alarm_planned_4
            Range("D52").Value = Lelderly_alarm_actual_1
            Range("G52").Value = Lelderly_alarm_actual_2
            Range("J52").Value = Lelderly_alarm_actual_3
            Range("M52").Value = Lelderly_alarm_actual_4
            Range("D20").Value = LFire_Alarms_planned_1
            Range("G20").Value = LFire_Alarms_Planned_2
            Range("J20").Value = LFire_Alarms_Planned_3
            Range("M20").Value = LFire_Alarms_Planned_4
            Range("D22").Value = LFire_Alarms_actual_1
            Range("G22").Value = LFire_Alarms_actual_2
            Range("J22").Value = LFire_Alarms_actual_3
            Range("M22").Value = LFire_Alarms_actual_4
            Range("D26").Value = LFire_equipment_planned_1
            Range("G26").Value = LFire_equipment_planned_2
            Range("J26").Value = LFire_equipment_planend_3
            Range("M26").Value = LFire_equipment_planned_4
            Range("D28").Value = LFire_equipment_actual_1
            Range("G28").Value = LFire_equipment_actual_2
            Range("J28").Value = LFire_equipment_actual_3
            Range("M28").Value = LFire_equipment_actual_4
            Range("D32").Value = LEmerg_Lighting_planned_1
            Range("G32").Value = LEmerg_Lighting_planned_2
            Range("J32").Value = LEmerg_Lighting_Planned_3
            Range("M32").Value = LEmerg_Lighting_planned_4
            Range("D34").Value = LEmerg_Lighting_actual_1
            Range("G34").Value = LEmerg_Lighting_actual_2
            Range("J34").Value = LEmerg_Lighting_actual_3
            Range("M34").Value = LEmerg_Lighting_actual_4
            Range("D38").Value = LPat_test_planned_1
            Range("G38").Value = LPat_test_planned_2
            Range("J38").Value = LPat_test_planned_3
            Range("M38").Value = LPat_test_planned_4
            Range("D40").Value = LPat_test_actual_1
            Range("G40").Value = LPat_test_actual_2
            Range("J40").Value = LPat_test_actual_3
            Range("M40").Value = LPat_test_actual_4
            Range("D56").Value = Lbed_hoist_planned_1
            Range("G56").Value = Lbed_hoist_planned_2
            Range("J56").Value = Lbed_hoist_planned_3
            Range("M56").Value = Lbed_hoist_planned_4
            Range("D58").Value = Lbed_hoist_actual_1
            Range("G58").Value = Lbed_hoist_acutal_2
            Range("J58").Value = Lbed_hoist_actual_3
            Range("M58").Value = Lbed_hoist_actual_4
            Range("D44").Value = Lin_block_lift_planned_1
            Range("G44").Value = Lin_block_lift_planned_2
            Range("J44").Value = Lin_block_lift_planned_3
            Range("M44").Value = Lin_block_lift_planned_4
            Range("D46").Value = Lin_block_lift_actual_1
            Range("G46").Value = Lin_block_lift_actual_2
            Range("J46").Value = Lin_block_lift_actual_3
            Range("M46").Value = Lin_block_lift_actual_4
            Range("D62").Value = LMisc_planned_1
            Range("G62").Value = LMisc_planned_2
            Range("D64").Value = LMisc_actual_1
            Range("G64").Value = LMisc_actual_2
 
        'Encountered a blank project_ref number (assuming end of list on full list)
        ElseIf IsEmpty(Range("A" & LRow).Value) = True Then
            MsgBox ("No Match was Found for Combo box Selection.")
            Exit Sub
        End If
 
        LRow = LRow + 1
    Loop
End Sub
and a save button with this code
Code:
Sub Button3_Click()
'Update data on Full List based on changes made to data on Invoice Control
 
    Dim LProject_Ref As Integer
 
    Dim Lstair_lift_invoice_1 As String
    Dim Lstair_lift_invoice_2 As String
    Dim Lstair_lift_invoice_3 As String
    Dim Lstair_lift_invoice_4 As String
    Dim Lstair_lift_planned_date_1 As String
    Dim Lstair_lift_planned_date_2 As String
    Dim Lstair_lift_planned_date_3 As String
    Dim Lstair_lift_planned_date_4 As String
    Dim Lstair_lift_actual_date_1 As String
    Dim Lstair_lift_actual_date_2 As String
    Dim Lstair_lift_actual_date_3 As String
    Dim Lstair_lift_actual_date_4 As String
    Dim Lstair_Additional_Information As String
    Dim LElderly_Alarms_invoice_1 As String
    Dim LElderly_Alarms_invoice_2 As String
    Dim LElderly_Alarms_invoice_3 As String
    Dim LElderly_Alarms_invoice_4 As String
    Dim Leam_actual_date_1 As String
    Dim Leam_actual_date_2 As String
    Dim Leam_actual_date_3 As String
    Dim Leam_actual_date_4 As String
    Dim Leam_planned1 As String
    Dim Leam_planned2 As String
    Dim Leam_planned3 As String
    Dim Leam_planned4 As String
    Dim Leam_notes As String
    Dim LFire_Alarms_1 As String
    Dim LFire_Alarms_2 As String
    Dim LFire_Alarms_3 As String
    Dim LFire_Alarms_4 As String
    Dim LFire_Alarms_actual1 As String
    Dim LFire_Alarms_actual2 As String
    Dim LFire_Alarms_actual3 As String
    Dim LFire_Alarms_actual4 As String
    Dim LFire_Alarms_planned1 As String
    Dim LFire_Alarms_planned2 As String
    Dim LFire_Alarms_planned3 As String
    Dim LFire_Alarms_planned4 As String
    Dim LFire_Alarms_Notes As String
    Dim LFire_equipment_1 As String
    Dim LFire_equipment_2 As String
    Dim LFire_equipment_3 As String
    Dim LFire_equipment_4 As String
    Dim LFire_equip_planned1 As String
    Dim LFire_equip_actual1 As String
    Dim LFire_equip_notes As String
    Dim LEmerg_Lighting_1 As String
    Dim LEmerg_Lighting_2 As String
    Dim LEmerg_Lighting_3 As String
    Dim LEmerg_Lighting_4 As String
    Dim LEmerg_lights_actual1 As String
    Dim LEmerg_lights_actual2 As String
    Dim LEmerg_lights_actual3 As String
    Dim LEmerg_lights_actual4 As String
    Dim Lemerg_lights_plan1 As String
    Dim Lemerg_lights_plan2 As String
    Dim Lemerg_lights_plan3 As String
    Dim Lemerg_lights_plan4 As String
    Dim Lemerg_lights_notes As String
    Dim LPat_test_1 As String
    Dim LPat_test_2 As String
    Dim LPat_test_3 As String
    Dim LPat_test_4 As String
    Dim LPat_test_actual1 As String
    Dim LPat_test_plan1 As String
    Dim LPat_test_notes As String
    Dim Lbed_hoist_1 As String
    Dim Lbed_hoist_2 As String
    Dim Lbed_hoist_3 As String
    Dim Lbed_hoist_4 As String
    Dim Lbhb_actual1 As String
    Dim Lbhb_actual2 As String
    Dim Lbhb_actual3 As String
    Dim Lbhb_actual4 As String
    Dim Lbhb_plan1 As String
    Dim Lbhb_plan2 As String
    Dim Lbhb_plan3 As String
    Dim Lbhb_plan4 As String
    Dim Lbhb_notes As String
    Dim Lin_block_lift_1 As String
    Dim Lin_block_lift_2 As String
    Dim Lin_block_lift_3 As String
    Dim Lin_block_lift_4 As String
    Dim Lin_block_actual1 As String
    Dim Lin_block_actual2 As String
    Dim Lin_block_actual3 As String
    Dim Lin_block_actual4 As String
    Dim Lin_block_actual5 As String
    Dim Lin_block_actual6 As String
    Dim Lin_block_plan1 As String
    Dim Lin_block_plan2 As String
    Dim Lin_block_plan3 As String
    Dim Lin_block_plan4 As String
    Dim Lin_block_plan5 As String
    Dim Lin_block_plan6 As String
    Dim Lin_block_notes As String
    Dim LChim1 As String
    Dim LChim2 As String
    Dim LChim_actual1 As String
    Dim LChim_actual2 As String
    Dim LChim_plan1 As String
    Dim LChim_plan2 As String
    Dim LChim_notes As String
    Dim LCOMBO1 As String
    Dim LCOMBO2 As String
    Dim LCOMBO_actual1 As String
    Dim LCOMBO_actual2 As String
    Dim LCOMBO_plan1 As String
    Dim LCOMBO_plan2 As String
    Dim LCOMBO_notes As String
    Dim LGecko1 As String
    Dim LGecko2 As String
    Dim LGecko_actual1 As String
    Dim LGecko_actual2 As String
    Dim LGecko_plan1 As String
    Dim LGecko_plan2 As String
    Dim LGecko_notes As String
    Dim LNU As String
    Dim LNU2 As String
    Dim LNu_actual1 As String
    Dim LNu_actual2 As String
    Dim LNu_plan1 As String
    Dim LNu_plan2 As String
    Dim LNu_notes As String
    Dim LPump1 As String
    Dim LPump2 As String
    Dim LPump3 As String
    Dim LPump4 As String
    Dim LPump_actual1 As String
    Dim LPump_actual2 As String
    Dim LPump_actual3 As String
    Dim LPump_actual4 As String
    Dim LPump_plan1 As String
    Dim LPump_plan2 As String
    Dim LPump_plan3 As String
    Dim LPump_plan4 As String
    Dim LPump_Notes As String
    Dim LSmoke1 As String
    Dim LSmoke2 As String
    Dim LSmoke3 As String
    Dim LSmoke4 As String
    Dim LSmoke_actual1 As String
    Dim LSmoke_actual2 As String
    Dim LSmoke_actual3 As String
    Dim LSmoke_actual4 As String
    Dim LSmoke_Plan1 As String
    Dim LSmoke_Plan2 As String
    Dim LSmoke_Plan3 As String
    Dim LSmoke_Plan4 As String
    Dim LSmoke_notes As String
 
    Dim LRow As Long
    Dim LFound As Boolean
 
    'Retrieve Project_ref number number
    LProject_Ref = Range("a8").Value
 
    'Retrieve new information
    Lstair_lift_invoice_1 = Range("D14").Value
    Lstair_lift_invoice_2 = Range("G14").Value
    Lstair_lift_invoice_3 = Range("J14").Value
    Lstair_lift_invoice_4 = Range("M14").Value
    Lstair_lift_planned_date_1 = Range("D18").Value
    Lstair_lift_planned_date_2 = Range("G18").Value
    Lstair_lift_planned_date_3 = Range("J18").Value
    Lstair_lift_planned_date_4 = Range("M18").Value
    Lstair_lift_actual_date_1 = Range("D19").Value
    Lstair_lift_actual_date_2 = Range("G19").Value
    Lstair_lift_actual_date_3 = Range("J19").Value
    Lstair_lift_actual_date_4 = Range("M19").Value
    Lstair_Additional_Information = Range("D21").Value
    Lelderly_alarm_invoice_1 = Range("D81").Value
    Lelderly_alarm_invoice_2 = Range("G81").Value
    Lelderly_alarm_invoice_3 = Range("J81").Value
    Lelderly_alarm_invoice_4 = Range("M81").Value
    Leam_actual_date_1 = Range("D86").Value
    Leam_actual_date_2 = Range("G86").Value
    Leam_actual_date_3 = Range("J86").Value
    Leam_actual_date_4 = Range("M86").Value
    Leam_planned1 = Range("D85").Value
    Leam_planned2 = Range("G85").Value
    Leam_planned3 = Range("J85").Value
    Leam_planned4 = Range("M85").Value
    Leam_notes = Range("D88").Value
    LFire_Alarms_1 = Range("D25").Value
    LFire_Alarms_2 = Range("G25").Value
    LFire_Alarms_3 = Range("J25").Value
    LFire_Alarms_4 = Range("M25").Value
    LFire_Alarms_actual1 = Range("D30").Value
    LFire_Alarms_actual2 = Range("G30").Value
    LFire_Alarms_actual3 = Range("J30").Value
    LFire_Alarms_actual4 = Range("M30").Value
    LFire_Alarms_planned1 = Range("D29").Value
    LFire_Alarms_planned2 = Range("G29").Value
    LFire_Alarms_planned3 = Range("J29").Value
    LFire_Alarms_planned4 = Range("M29").Value
    LFire_Alarms_Notes = Range("D32").Value
    LFire_equipment_1 = Range("D36").Value
    LFire_equipment_2 = Range("G36").Value
    LFire_equipment_3 = Range("J36").Value
    LFire_equipment_4 = Range("M36").Value
    LFire_equip_planned1 = Range("D40").Value
    LFire_equip_actual1 = Range("D42").Value
    LFire_equip_notes = Range("D44").Value
    LEmerg_Lighting_1 = Range("D48").Value
    LEmerg_Lighting_2 = Range("G48").Value
    LEmerg_Lighting_3 = Range("J48").Value
    LEmerg_Lighting_4 = Range("M48").Value
    LEmerg_lights_actual1 = Range("D53").Value
    LEmerg_lights_actual2 = Range("G53").Value
    Lemerg_lights_plan1 = Range("D52").Value
    Lemerg_lights_plan2 = Range("G52").Value
    Lemerg_lights_notes = Range("D55").Value
    LPat_test_1 = Range("D59").Value
    LPat_test_2 = Range("G59").Value
    LPat_test_3 = Range("J59").Value
    LPat_test_4 = Range("M59").Value
    LPat_test_actual1 = Range("D64").Value
    LPat_test_plan1 = Range("D63").Value
    LPat_test_notes = Range("D66").Value
    Lbed_hoist_1 = Range("D92").Value
    Lbed_hoist_2 = Range("G92").Value
    Lbed_hoist_3 = Range("J92").Value
    Lbed_hoist_4 = Range("M92").Value
    Lbhb_actual1 = Range("D97").Value
    Lbhb_actual2 = Range("G97").Value
    Lbhb_actual3 = Range("J97").Value
    Lbhb_actual4 = Range("M97").Value
    Lbhb_plan1 = Range("D96").Value
    Lbhb_plan2 = Range("G96").Value
    Lbhb_plan3 = Range("J96").Value
    Lbhb_plan4 = Range("M96").Value
    Lbhb_notes = Range("D99").Value
    Lin_block_lift_1 = Range("D70").Value
    Lin_block_lift_2 = Range("G70").Value
    Lin_block_lift_3 = Range("J70").Value
    Lin_block_lift_4 = Range("M70").Value
    Lin_block_actual1 = Range("D75").Value
    Lin_block_actual2 = Range("F75").Value
    Lin_block_actual3 = Range("H75").Value
    Lin_block_actual4 = Range("J75").Value
    Lin_block_actual5 = Range("L75").Value
    Lin_block_actual6 = Range("N75").Value
    Lin_block_plan1 = Range("D74").Value
    Lin_block_plan2 = Range("F74").Value
    Lin_block_plan3 = Range("H74").Value
    Lin_block_plan4 = Range("J74").Value
    Lin_block_plan5 = Range("L74").Value
    Lin_block_plan6 = Range("N74").Value
    Lin_block_notes = Range("D77").Value
    LChim1 = Range("D103").Value
    LChim2 = Range("G103").Value
    LChim_actual1 = Range("D108").Value
    LChim_actual2 = Range("G108").Value
    LChim_plan1 = Range("D107").Value
    LChim_plan2 = Range("G107").Value
    LChim_notes = Range("D110").Value
    LCOMBO1 = Range("D114").Value
    LCOMBO2 = Range("G114").Value
    LCOMBO_actual1 = Range("D119").Value
    LCOMBO_plan1 = Range("D118").Value
    LCOMBO_notes = Range("D121").Value
    LGecko1 = Range("D125").Value
    LGecko2 = Range("G125").Value
    LGecko_actual1 = Range("D130").Value
    LGecko_plan1 = Range("D129").Value
    LGecko_notes = Range("D132").Value
    LNU = Range("D136").Value
    LNU2 = Range("G136").Value
    LNu_actual1 = Range("D141").Value
    LNu_plan1 = Range("D140").Value
    LNu_notes = Range("D143").Value
    LPump1 = Range("D147").Value
    LPump2 = Range("G147").Value
    LPump3 = Range("J147").Value
    LPump4 = Range("M147").Value
    LPump_actual1 = Range("D152").Value
    LPump_actual2 = Range("G152").Value
    LPump_actual3 = Range("J152").Value
    LPump_actual4 = Range("M152").Value
    LPump_plan1 = Range("D151").Value
    LPump_plan2 = Range("G151").Value
    LPump_plan3 = Range("J151").Value
    LPump_plan4 = Range("M151").Value
    LPump_Notes = Range("D154").Value
    LSmoke1 = Range("D158").Value
    LSmoke2 = Range("G158").Value
    LSmoke3 = Range("J158").Value
    LSmoke4 = Range("M158").Value
    LSmoke_actual1 = Range("D163").Value
    LSmoke_actual2 = Range("G163").Value
    LSmoke_actual3 = Range("J163").Value
    LSmoke_actual4 = Range("M163").Value
    LSmoke_Plan1 = Range("D162").Value
    LSmoke_Plan2 = Range("G162").Value
    LSmoke_Plan3 = Range("J162").Value
    LSmoke_Plan4 = Range("M162").Value
    LSmoke_notes = Range("D165").Value
 
 
    'Move to Full List to save changes
    Sheets("Full List").Select
 
    LFound = False
 
    LRow = 2
 
    Do While LFound = False
        'Found matching Project_Ref, now update address and phone number information
        If Range("A" & LRow).Value = LProject_Ref Then
            LFound = True
            Range("S" & LRow).Value = Lstair_lift_invoice_1
            Range("T" & LRow).Value = Lstair_lift_invoice_2
            Range("U" & LRow).Value = Lstair_lift_invoice_3
            Range("V" & LRow).Value = Lstair_lift_invoice_4
            Range("J" & LRow).Value = Lstair_lift_planned_date_1
            Range("K" & LRow).Value = Lstair_lift_planned_date_2
            Range("L" & LRow).Value = Lstair_lift_planned_date_3
            Range("M" & LRow).Value = Lstair_lift_planned_date_4
            Range("N" & LRow).Value = Lstair_lift_actual_date_1
            Range("O" & LRow).Value = Lstair_lift_actual_date_2
            Range("P" & LRow).Value = Lstair_lift_actual_date_3
            Range("Q" & LRow).Value = Lstair_lift_actual_date_4
            Range("I" & LRow).Value = Lstair_Additional_Information
            Range("AG" & LRow).Value = Lelderly_alarm_invoice_1
            Range("AH" & LRow).Value = Lelderly_alarm_invoice_2
            Range("AI" & LRow).Value = Lelderly_alarm_invoice_3
            Range("AJ" & LRow).Value = Lelderly_alarm_invoice_4
            Range("AB" & LRow).Value = Leam_actual_date_1
            Range("AC" & LRow).Value = Leam_actual_date_2
            Range("AD" & LRow).Value = Leam_actual_date_3
            Range("AE" & LRow).Value = Leam_actual_date_4
            Range("X" & LRow).Value = Leam_planned1
            Range("Y" & LRow).Value = Leam_planned2
            Range("Z" & LRow).Value = Leam_planned3
            Range("AA" & LRow).Value = Leam_planned4
            Range("AK" & LRow).Value = Leam_notes
            Range("CW" & LRow).Value = LFire_Alarms_1
            Range("CX" & LRow).Value = LFire_Alarms_2
            Range("CY" & LRow).Value = LFire_Alarms_3
            Range("CZ" & LRow).Value = LFire_Alarms_4
            Range("CR" & LRow).Value = LFire_Alarms_actual1
            Range("CS" & LRow).Value = LFire_Alarms_actual2
            Range("CT" & LRow).Value = LFire_Alarms_actual3
            Range("CU" & LRow).Value = LFire_Alarms_actual4
            Range("CN" & LRow).Value = LFire_Alarms_planned1
            Range("CO" & LRow).Value = LFire_Alarms_planned2
            Range("CP" & LRow).Value = LFire_Alarms_planned3
            Range("CQ" & LRow).Value = LFire_Alarms_planned4
            Range("CM" & LRow).Value = LFire_Alarms_Notes
            Range("CH" & LRow).Value = LFire_equipment_1
            Range("CI" & LRow).Value = LFire_equipment_2
            Range("CJ" & LRow).Value = LFire_equipment_3
            Range("CK" & LRow).Value = LFire_equipment_4
            Range("CE" & LRow).Value = LFire_equip_planned1
            Range("CF" & LRow).Value = LFire_equip_actual1
            Range("CD" & LRow).Value = LFire_equip_notes
            Range("EI" & LRow).Value = LEmerg_Lighting_1
            Range("EJ" & LRow).Value = LEmerg_Lighting_2
            Range("EK" & LRow).Value = LEmerg_Lighting_3
            Range("EL" & LRow).Value = LEmerg_Lighting_4
            Range("ED" & LRow).Value = LEmerg_lights_actual1
            Range("EE" & LRow).Value = LEmerg_lights_actual2
            Range("DZ" & LRow).Value = Lemerg_lights_plan1
            Range("EA" & LRow).Value = Lemerg_lights_plan2
            Range("DY" & LRow).Value = Lemerg_lights_notes
            Range("BE" & LRow).Value = LPat_test_1
            Range("BF" & LRow).Value = LPat_test_2
            Range("BG" & LRow).Value = LPat_test_3
            Range("BH" & LRow).Value = LPat_test_4
            Range("BC" & LRow).Value = LPat_test_actual1
            Range("BB" & LRow).Value = LPat_test_plan1
            Range("BI" & LRow).Value = LPat_test_notes
            Range("AW" & LRow).Value = Lbed_hoist_1
            Range("AX" & LRow).Value = Lbed_hoist_2
            Range("AY" & LRow).Value = Lbed_hoist_3
            Range("AZ" & LRow).Value = Lbed_hoist_4
            Range("AR" & LRow).Value = Lbhb_actual1
            Range("AS" & LRow).Value = Lbhb_actual2
            Range("AT" & LRow).Value = Lbhb_actual3
            Range("AU" & LRow).Value = Lbhb_actual4
            Range("AN" & LRow).Value = Lbhb_plan1
            Range("AO" & LRow).Value = Lbhb_plan2
            Range("AP" & LRow).Value = Lbhb_plan3
            Range("AQ" & LRow).Value = Lbhb_plan4
            Range("AM" & LRow).Value = Lbhb_notes
            Range("BY" & LRow).Value = Lin_block_lift_1
            Range("BZ" & LRow).Value = Lin_block_lift_2
            Range("CA" & LRow).Value = Lin_block_lift_3
            Range("CB" & LRow).Value = Lin_block_lift_4
            Range("BR" & LRow).Value = Lin_block_actual1
            Range("BS" & LRow).Value = Lin_block_actual2
            Range("BT" & LRow).Value = Lin_block_actual3
            Range("BU" & LRow).Value = Lin_block_actual4
            Range("BV" & LRow).Value = Lin_block_actual5
            Range("BW" & LRow).Value = Lin_block_actual6
            Range("BL" & LRow).Value = Lin_block_plan1
            Range("BM" & LRow).Value = Lin_block_plan2
            Range("BN" & LRow).Value = Lin_block_plan3
            Range("BO" & LRow).Value = Lin_block_plan4
            Range("BP" & LRow).Value = Lin_block_plan5
            Range("BQ" & LRow).Value = Lin_block_plan6
            Range("BK" & LRow).Value = Lin_block_notes
            Range("DI" & LRow).Value = LChim1
            Range("DJ" & LRow).Value = LChim2
            Range("DF" & LRow).Value = LChim_actual1
            Range("DG" & LRow).Value = LChim_actual2
            Range("DD" & LRow).Value = LChim_plan1
            Range("DE" & LRow).Value = LChim_plan2
            Range("DK" & LRow).Value = LChim_notes
            Range("DS" & LRow).Value = LCOMBO1
            Range("DT" & LRow).Value = LCOMBO2
            Range("DP" & LRow).Value = LCOMBO_actual1
            Range("DN" & LRow).Value = LCOMBO_plan1
            Range("DW" & LRow).Value = LCOMBO_notes
            Range("ET" & LRow).Value = LGecko1
            Range("EU" & LRow).Value = LGecko2
            Range("EQ" & LRow).Value = LGecko_actual1
            Range("EO" & LRow).Value = LGecko_plan1
            Range("EX" & LRow).Value = LGecko_notes
            Range("FF" & LRow).Value = LNU
            Range("FG" & LRow).Value = LNU2
            Range("FC" & LRow).Value = LNu_actual1
            Range("FA" & LRow).Value = LNu_plan1
            Range("FJ" & LRow).Value = LNu_notes
            Range("FV" & LRow).Value = LPump1
            Range("FW" & LRow).Value = LPump2
            Range("FX" & LRow).Value = LPump3
            Range("FY" & LRow).Value = LPump4
            Range("FQ" & LRow).Value = LPump_actual1
            Range("FR" & LRow).Value = LPump_actual2
            Range("FS" & LRow).Value = LPump_actual3
            Range("FT" & LRow).Value = LPump_actual4
            Range("FM" & LRow).Value = LPump_plan1
            Range("FN" & LRow).Value = LPump_plan2
            Range("FO" & LRow).Value = LPump_plan3
            Range("FP" & LRow).Value = LPump_plan4
            Range("FZ" & LRow).Value = LPump_Notes
            Range("GL" & LRow).Value = LSmoke1
            Range("GM" & LRow).Value = LSmoke2
            Range("GN" & LRow).Value = LSmoke3
            Range("GO" & LRow).Value = LSmoke4
            Range("GG" & LRow).Value = LSmoke_actual1
            Range("GH" & LRow).Value = LSmoke_actual2
            Range("GI" & LRow).Value = LSmoke_actual3
            Range("GJ" & LRow).Value = LSmoke_actual4
            Range("GC" & LRow).Value = LSmoke_Plan1
            Range("GD" & LRow).Value = LSmoke_Plan2
            Range("GE" & LRow).Value = LSmoke_Plan3
            Range("GF" & LRow).Value = LSmoke_Plan4
            Range("GP" & LRow).Value = LSmoke_notes
 
            'Encountered a blank Project_Ref number (assuming end of list on full_list)
             ElseIf IsEmpty(Range("A" & LRow).Value) = True Then
                MsgBox ("No Match was found.Changes were not made.")
                Exit Sub
            End If
 
            LRow = LRow + 1
        Loop
 
        'Reposition back on Invoice Control
        Sheets("Invoice Control").Select
        Range("A10").Select
 
        MsgBox ("Yay Changes Complete :)")
End Sub
The combo box uses a Location Ref from the sheet 'Full List' as the info provided in the drop down, but this has now become to large to manage and I need a way of filtering down the data.I have a a number of fields that I can filter with, I just Have no Idea where to start with this one. My preference would be for either radio button filters or multiple Combo boxes. I need to keep the read and write to functions as is if possible. Any help greatfully recieved.

Thanks

Jaysteruk
 

Forum statistics

Threads
1,081,439
Messages
5,358,681
Members
400,508
Latest member
fish31

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top