Simplifying Code

[chrislowe2006]

Hi, First of all thanks in advanced for your help. I'm fairly new to coding VBA in Excel. so I have managed to create the following code (which works as intended) and I am curious to know if there is anyway to simplify it as I need to add more lines to it.

The intended result is that when a person clicks a button the seat is booked and reflected on another sheet.

Sub BookSeat()

Dim D1 As String
Dim D2 As String
Dim D3 As String
Dim Seat As String

D1 = Sheets("Booking").Range("E8").Value
D2 = Sheets("Booking").Range("F8").Value
D3 = Sheets("Booking").Range("J8").Value
Seat = Sheets("Booking").Range("I8").Value

If D1 = "Manchester" And D2 = "Amsterdam" And Seat = "1A" And D3 = "Available" Then
    Sheets("JB114").Range("S1A").Value = "Occupied"
End If
        
If D1 = "Manchester" And D2 = "Amsterdam" And Seat = "1B" And D3 = "Available" Then
   Sheets("JB114").Range("S1B").Value = "Occupied"
End If

If D1 = "Manchester" And D2 = "Amsterdam" And Seat = "1D" And D3 = "Available" Then
   Sheets("JB114").Range("S1D").Value = "Occupied"
End If
        
If D1 = "Manchester" And D2 = "Amsterdam" And Seat = "1F" And D3 = "Available" Then
   Sheets("JB114").Range("S1F").Value = "Occupied"
End If

If D1 = "Manchester" And D2 = "Amsterdam" And Seat = "2A" And D3 = "Available" Then
    Sheets("JB114").Range("S2A").Value = "Occupied"
End If
        
If D1 = "Manchester" And D2 = "Amsterdam" And Seat = "2B" And D3 = "Available" Then
   Sheets("JB114").Range("S2B").Value = "Occupied"
End If

If D1 = "Manchester" And D2 = "Amsterdam" And Seat = "2D" And D3 = "Available" Then
   Sheets("JB114").Range("S2D").Value = "Occupied"
End If
        
If D1 = "Manchester" And D2 = "Amsterdam" And Seat = "2F" And D3 = "Available" Then
   Sheets("JB114").Range("S2F").Value = "Occupied"
End If

If D1 = "Manchester" And D2 = "Amsterdam" And Seat = "3A" And D3 = "Available" Then
    Sheets("JB114").Range("S3A").Value = "Occupied"
End If
        
If D1 = "Manchester" And D2 = "Amsterdam" And Seat = "3B" And D3 = "Available" Then
   Sheets("JB114").Range("S3B").Value = "Occupied"
End If

If D1 = "Manchester" And D2 = "Amsterdam" And Seat = "3D" And D3 = "Available" Then
   Sheets("JB114").Range("S3D").Value = "Occupied"
End If
        
If D1 = "Manchester" And D2 = "Amsterdam" And Seat = "3F" And D3 = "Available" Then
   Sheets("JB114").Range("S3F").Value = "Occupied"
End If

If D1 = "Manchester" And D2 = "Amsterdam" And Seat = "4A" And D3 = "Available" Then
    Sheets("JB114").Range("S4A").Value = "Occupied"
End If
        
If D1 = "Manchester" And D2 = "Amsterdam" And Seat = "4B" And D3 = "Available" Then
   Sheets("JB114").Range("S4B").Value = "Occupied"
End If

If D1 = "Manchester" And D2 = "Amsterdam" And Seat = "4D" And D3 = "Available" Then
   Sheets("JB114").Range("S4D").Value = "Occupied"
End If
        
If D1 = "Manchester" And D2 = "Amsterdam" And Seat = "4F" And D3 = "Available" Then
   Sheets("JB114").Range("S4F").Value = "Occupied"
End If

As you can see it is a long peice of code...

 

[mumps]

I am assuming that "S1A", "S1B", "S1D", etc. are named ranges.
Try this macro:

Sub BookSeat()
    Dim rng As Range
    Dim rgn1 As Range
    Dim myArray As Variant
    Dim i As Long
    myArray = Array("A", "B", "D", "F")
    For i = LBound(myArray) To UBound(myArray)
        Select Case Left(Range("I8").Value, 1)
        Case Is = 1, 2, 3, 4
            If Range("E8") = "Manchester" And Range("F8") = "Amsterdam" And Range("I8") = Left(Range("I8").Value, 1) & myArray(i) And Range("J8") = "Available" Then
                Sheets("JB114").Range("S" & Left(Range("I8").Value, 1) & myArray(i)).Value = "Occupied"
            End If
        End Select
    Next i
End Sub

 

[Rick Rothstein]

You omitted the "End Sub" statement... I assumed it belonged after the last If..Then block you posted. With that said, and assuming like 'mumps' did that "S1A", "S1B", "S1D", etc. are named ranges, everything you posted above should be able to be replaced by the following...

[table="width: 500"]
[tr]
	[td]Sub BookSeat()
  With Sheets("Booking")
    If .Range("E8").Value = "Manchester" And .Range("F8").Value = "Amsterdam" And .Range("J8").Value = "Available" Then
      Sheets("JB114").Range("S" & .Range("I8").Value).Value = "Occupied"
    End If
  End With
End Sub[/td]
[/tr]
[/table]

 

[mumps]

@Rick: Very efficient as usual.

 

[chrislowe2006]

Thanks both, yes S1A etc are named ranges (just 1 cell).

 

[mumps]

Glad to help.