Successive retrieval of defined ranges by splitting thier names in a constant as well as a dynamic part

0 votes

The parking management tool should contain the code below. Essentially, it is only conditional formatting of parking spots in the form of defined ranges in a sheet called "GF" based on an associated status that is defined as a string in a list object in a sheet called "GF List."

I want to address the entire problem using a VBA method rather than the typical conditional formatting because, unlike the example below, the code will be applied later to several hundred parking spaces with eight potential formattings.

Because I can't format the "CurrentLot" as a Range using a dynamically-retrieved value, the code fails.

Sub No_01_to_05a()

Dim gfList As Worksheet
Dim gfPlan As Worksheet
Dim status As String
Dim CurrentLot As Range
Dim i As Integer
Dim No As String

Set gfList = ThisWorkbook.Worksheets("GF List")
Set gfPlan = ThisWorkbook.Worksheets("GF")


'Parking lots that are defined manually here
Dim LotNo1 As Range
Set LotNo1 = gfPlan.Range("B2", "C2")

Dim LotNo2 As Range
Set LotNo2 = gfPlan.Range("D2", "E2")

Dim LotNo3 As Range
Set LotNo3 = gfPlan.Range("F2", "G2")

Dim LotNo4 As Range
Set LotNo4 = gfPlan.Range("H2", "I2")

Dim LotNo5 As Range
Set LotNo5 = gfPlan.Range("J2", "K2")

Dim LotNo5a As Range
Set LotNo5a = gfPlan.Range("M2", "M3")

'ForNext procedure
For i = 4 To gfList.Range("E" & Application.Rows.Count).End(xlUp).Row
    
    status = gfList.Range("E" & i).Value
    No = gfList.Range("B" & i).Value
    CurrentLot = "LotNo" & No 'Line that does not seem to work
    
        If status = "Vacant" Then
                CurrentLot.Interior.Color = RGB(255, 255, 0)
                CurrentLot.Font.Color = RGB(0, 0, 0)
    
        ElseIf status = "Let" Then
                CurrentLot.Interior.Color = RGB(146, 208, 80)
                CurrentLot.Font.Color = RGB(0, 0, 0)

        ElseIf status = "Reserved" Then
                CurrentLot.Interior.Color = RGB(0, 176, 240)
                CurrentLot.Font.Color = RGB(0, 0, 0)
                
        Else
                CurrentLot.Interior.Color = RGB(255, 255, 255)
                CurrentLot.Font.Color = RGB(0, 0, 0)     
        End If
                  
Next i

End Sub
Feb 5, 2023 in Others by Kithuzzz
• 38,010 points
214 views

1 answer to this question.

0 votes

You can use a Select Case statement :

Option Explicit

Sub No_01_to_05a()

    Dim gfList As Worksheet
    Set gfList = ThisWorkbook.Worksheets("GF List")
    Dim status As String
    Dim CurrentLot As Range
    Dim i As Integer
    Dim No As String

    'ForNext procedure
    For i = 4 To gfList.Range("E" & Application.Rows.Count).End(xlUp).Row
    
        status = gfList.Range("E" & i).Value
        No = gfList.Range("B" & i).Value
        'CurrentLot = "LotNo" & No   <= You cannot assign a string to a range
        Set CurrentLot = getLot(No)
    
        If status = "Vacant" Then
            CurrentLot.Interior.Color = RGB(255, 255, 0)
            CurrentLot.Font.Color = RGB(0, 0, 0)
    
        ElseIf status = "Let" Then
            CurrentLot.Interior.Color = RGB(146, 208, 80)
            CurrentLot.Font.Color = RGB(0, 0, 0)

        ElseIf status = "Reserved" Then
            CurrentLot.Interior.Color = RGB(0, 176, 240)
            CurrentLot.Font.Color = RGB(0, 0, 0)
                
        Else
            CurrentLot.Interior.Color = RGB(255, 255, 255)
            CurrentLot.Font.Color = RGB(0, 0, 0)
        End If
                  
    Next i

End Sub

Function getLot(ByVal No As Long) As Range
    
    Dim gfPlan As Worksheet
    Set gfPlan = ThisWorkbook.Worksheets("GF")
    
    'Parking lots that are defined manually here
    Dim LotNo1 As Range
    Set LotNo1 = gfPlan.Range("B2", "C2")

    Dim LotNo2 As Range
    Set LotNo2 = gfPlan.Range("D2", "E2")

    Dim LotNo3 As Range
    Set LotNo3 = gfPlan.Range("F2", "G2")

    Dim LotNo4 As Range
    Set LotNo4 = gfPlan.Range("H2", "I2")

    Dim LotNo5 As Range
    Set LotNo5 = gfPlan.Range("J2", "K2")

    Dim LotNo5a As Range
    Set LotNo5a = gfPlan.Range("M2", "M3")
    Select Case No
        Case 1
            Set getLot = LotNo1
        Case 2
            Set getLot = LotNo2
        Case 3
            Set getLot = LotNo3
        Case 4
            Set getLot = LotNo4
        Case 5
            Set getLot = LotNo5
        Case Else
            '
    End Select
End Function
answered Feb 5, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

Ordering by the order of values in a SQL IN() clause

We can use expressions as well in ...READ MORE

answered May 31, 2022 in Others by Sohail
• 3,040 points
253 views
0 votes
1 answer

Ordering by the order of values in a SQL IN() clause

Use MySQL FIND_IN_SET function: SELECT * ...READ MORE

answered Jun 6, 2022 in Others by nisha
• 2,210 points
449 views
0 votes
1 answer

How to trick an Excel function that wants a column as input to accept a list of values as if these were in a column

Use VSTACK: vstack to make an array: Use it as value ...READ MORE

answered Mar 18, 2023 in Others by narikkadan
• 63,420 points
259 views
0 votes
1 answer

VBA to create a dynamic rolling list of names

If I understand you correctly, a case. ...READ MORE

answered Mar 23, 2023 in Others by narikkadan
• 63,420 points
259 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
864 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,167 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
460 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
719 views
0 votes
1 answer

In a excel formula I need to create a list of names on one sheet based upon criteria/data of another sheet

The final formula is: =IF(ROWS($H$3:H3)<=$I$1,INDEX(Personnel! ...READ MORE

answered Nov 25, 2022 in Others by narikkadan
• 63,420 points
718 views
0 votes
1 answer

Multiplication of all numbers in a cell by a specific number

With ms365, try: The formula in B1: =MAP(A1:A3,LAMBDA(x,TE ...READ MORE

answered Feb 21, 2023 in Others by narikkadan
• 63,420 points
206 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP