Simplifying Code

chrislowe2006

New Member
Joined
Feb 13, 2014
Messages
8
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.

Code:
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,617
I am assuming that "S1A", "S1B", "S1D", etc. are named ranges.
Try this macro:
Code:
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
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,572
Office Version
2010
Platform
Windows
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...
Code:
[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]
 
Last edited:

Forum statistics

Threads
1,085,859
Messages
5,386,386
Members
401,997
Latest member
cizwiz

Some videos you may like

This Week's Hot Topics

Top