Visual Basic why is there an Object error?

Question

There is some kind of error(424). That occurs when i try to select a specific sheet.

I tried to initialize inside the if statement and/or inside the Do-Until loop.

Dim wbMnTbl As Workbook

cin = InputBox("input Data in form of Excel sheet names", Number)

Pfad1 = "PATH&" & cin & "\" & cin & ".xlsx"
Set wbMnTbl = Workbooks.Open(Pfad1)

Do
    If IsEmpty(Cells(ZelleA1, ZelleA2)) = False Then
        SelA = (Cells(ZelleA1, ZelleA2).Select)
        Selection.Copy

        'MsgBox wbMnTb1.Sheet("Sheet-Name").Range("B3")

        'Error is in this Line 
        -> wbMnTb1.Sheets("Sheet-Name").Range("B" & VarA).Select <-
        ActiveSheet.Paste
        wbMnTbl.Close    
        ZelleA1 = ZelleA1 + 1
        VarA = VarA + 1
    Else
        H = 1   
    End If
Loop Until H = 1

What is the initial value of `VarA`? If it is `0` then keep in mind that there is no cell `B0` - and you should define that variable and use `Option Explicit` as well to avoid problems like this. — braX, Aug 30 '19 at 07:24
The problem is that it isnt even accepting wbMnTb1. It says Objekt needed. — SplashyMelon, Aug 30 '19 at 07:38
Yes you will have to work with objects. Avoid the use of `.Select/.ActiveSheet` etc. You may want to see [This](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Siddharth Rout, Aug 30 '19 at 07:47

Tim Stack · Answer 1 · 2019-08-30T07:38:04.030

The variable VarA in wbMnTb1.Sheets("Sheet-Name").Range("B" & VarA).Select is not populated when that line is executed. That results in a nonsense range string.

I advise you to declare all of your variables. That way you're also confined to only using integers for the VarA variable, thereby preventing you from passing strings to it. After all, VarA should be a row number. The best practice for this is to use Option Explicit

Furthermore, other variables are also empty when you refer to them. Not sure what you mean to do but in order to work with variables it would help actually populating them before them.

For example
This prints nothing

Debug.Print myVar

This prints 0 because Long is an integer and it's not populated beforehand, so it prints the standard value of an integer variable

Dim myVar As Long
Debug.Print myVar

This prints 1, because you set it to that value

Dim myVar As Long
myVar = 1
Debug.Print myVar

This results in an error. You cannot pass a string to a number type variable

Dim myVar As Long
myVar = "test" 'ERROR
Debug.Print myVar

Your code

Option Explicit

Sub MySub()
Dim wbMnTbl As Workbook
Dim cin As String, Pfad1 As String
Dim VarA As Long, ZelleA1 As Long, ZellaA2 As Long, H As Long, Number As Long
Dim SelA As Range

'Variable Number is 0
cin = InputBox("input Data in form of Excel sheet names", Number)

Pfad1 = "PATH&" & cin & "\" & cin & ".xlsx"
Set wbMnTbl = Workbooks.Open(Pfad1)

Do
    'Variable ZelleA1, ZelleA2, VarA, H are 0 on the first loop
    If IsEmpty(wbMnTbl.Cells(ZelleA1, ZelleA2)) = False Then
        Set SelA = wbMnTbl.Cells(ZelleA1, ZelleA2)
        SelA.Copy wbMnTbl.Sheets("Sheet-Name").Range("B" & VarA)

        wbMnTbl.Close    
        ZelleA1 = ZelleA1 + 1
        VarA = VarA + 1
    Else
        H = 1   
    End If
Loop Until H = 1
End Sub

You also copied the typo across :D – Siddharth Rout Aug 30 '19 at 07:36 — Siddharth Rout, Aug 30 '19 at 07:36
Can the `1` and `l` be any more similar? @SiddharthRout – Tim Stack Aug 30 '19 at 07:39 — Tim Stack, Aug 30 '19 at 07:39

Visual Basic why is there an Object error?

1 Answers1