Error trying to select sheet with public variable

KhallP

Board Regular
Joined
Mar 30, 2021
Messages
80
Office Version
  1. 2016
Platform
  1. Windows
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:


Capturar.JPG


Capturar.JPG




The code is that:

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

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You'll get that error if MachineName isn't one of the values you specified.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

KhallP

Board Regular
Joined
Mar 30, 2021
Messages
80
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Capturar.JPG



Capturar.JPG
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
Mar 30, 2021
Messages
80
Office Version
  1. 2016
Platform
  1. Windows
Declaration

machine.JPG


Modules with public variables

module.JPG



Combobox

machine_2.JPG
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Which module is the declaration in, and what is the code that calls the routine you posted?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,187
Messages
5,640,693
Members
417,161
Latest member
Devon150

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top