Error trying to select sheet with public variable

[KhallP]

I am trying to make a code that makes the program write data in a specific excel spreadsheet according to the value of the variable "MachineName" declared as public


What happens is that the program does not allow me to do it because an error is detected on line 96


The error:

The code is that:

Sub KillEmpty()

    Dim sh As Worksheet
    Dim k As Long

    Range("C4").Select
   
    If MachineName = "A4" Then
   
        Set sh = ThisWorkbook.Sheets("A4")
       
    ElseIf MachineName = "A8.1" Then
   
        Set sh = ThisWorkbook.Sheets("A8_1")
   

    ElseIf MachineName = "A8.2" Then
      
        Set sh = ThisWorkbook.Sheets("A8_2")
   
    ElseIf MachineName = "A8.3" Then
   
        Set sh = ThisWorkbook.Sheets("A8_3")
       
       
    ElseIf MachineName = "A12.1" Then

        Set sh = ThisWorkbook.Sheets("A12_1")
   
   
    ElseIf MachineName = "A12.2" Then

        Set sh = ThisWorkbook.Sheets("A12_2")
     
        
    ElseIf MachineName = "A12.3" Then

        Set sh = ThisWorkbook.Sheets("A12_3")
   
   
    ElseIf MachineName = "A20.1" Then

        Set sh = ThisWorkbook.Sheets("A20_1")
       

    ElseIf MachineName = "A20.2" Then

        Set sh = ThisWorkbook.Sheets("A20_2")
       
    End If
   
    k = sh.Range("C4", sh.Range("C4").End(xlDown)).Rows.Count
   
   
    If ActiveCell.Value = "" Then
        Call FillCells
   
    ElseIf ActiveCell.Value <> "" Then
        Do Until ActiveCell.Value = ""
            ActiveCell.Offset(1, 0).Select
        Loop
        Call FillCells
       
    End If
End Sub

Can anyone help me?

 

[RoryA]

You'll get that error if MachineName isn't one of the values you specified.

 

[Joe4]

Another little tip.
When checking one variable for multiple different values, it is often cleaner to use a Case statement instead of a whole bunch of "ElseIf" statements.
See: MS Excel: How to use the CASE Statement (VBA)

 

[KhallP]

You'll get that error if MachineName isn't one of the values you specified.

I know but i have that values in the variable

 

[RoryA]

Is that the value when the error occurs? Your code doesn't show where MachineName is declared or assigned a value.

 

[KhallP]

Another little tip.
When checking one variable for multiple different values, it is often cleaner to use a Case statement instead of a whole bunch of "ElseIf" statements.
See: MS Excel: How to use the CASE Statement (VBA)

Done

 

[KhallP]

Is that the value when the error occurs? Your code doesn't show where MachineName is declared or assigned a value.

Whatever value the data will always appear in the "Start" spreadsheet and not in the spreadsheet marked by the variable, i don´t know why

 

[RoryA]

That picture really doesn't tell us anything. Where is the code that declares and assigns the variable and then presumably calls the code you posted?

 

[KhallP]

Declaration

Modules with public variables

Combobox

 

[RoryA]

Which module is the declaration in, and what is the code that calls the routine you posted?