0

since my last problem got solved, i came up with another one! Woooo

Method "Range" for Object '_Global' failed.

Here, I am importing data which haven been searched up my a variable (PZ_RNG/strSearch)

Private Sub Search_Click()

Dim PZ_RNG As Range
Dim strSearch As String

strSearch = Packzettelinfo.PZ_ID

Set PZ_RNG = ThisWorkbook.Sheets("Data").Range("B:B").Find(strSearch, , xlValues, xlWhole)
If Not PZ_RNG Is Nothing Then

Packzettelinfo.KD_ID = ThisWorkbook.Sheets("Data").Cells(PZ_RNG.Row, PZ_RNG.Column + 1)
Packzettelinfo.Customer_Combination = ThisWorkbook.Sheets("Data").Cells(PZ_RNG.Row, PZ_RNG.Column + 2)
Packzettelinfo.Ship_ID = ThisWorkbook.Sheets("Data").Cells(PZ_RNG.Row, PZ_RNG.Column + 3)
Packzettelinfo.Author_ID = ThisWorkbook.Sheets("Data").Cells(PZ_RNG.Row, PZ_RNG.Column + 4)
Packzettelinfo.Art_Lager = ThisWorkbook.Sheets("Data").Cells(PZ_RNG.Row, PZ_RNG.Column + 5)
Packzettelinfo.Art_Bestell = ThisWorkbook.Sheets("Data").Cells(PZ_RNG.Row, PZ_RNG.Column + 6)
Packzettelinfo.DTPicker1 = ThisWorkbook.Sheets("Data").Cells(PZ_RNG.Row, PZ_RNG.Column + 7)
Packzettelinfo.Calc_Time = ThisWorkbook.Sheets("Data").Cells(PZ_RNG.Row, PZ_RNG.Column + 8)
Packzettelinfo.Time1 = ThisWorkbook.Sheets("Data").Cells(PZ_RNG.Row, PZ_RNG.Column + 10)
Packzettelinfo.Time2 = ThisWorkbook.Sheets("Data").Cells(PZ_RNG.Row, PZ_RNG.Column + 11)
Packzettelinfo.Time3 = ThisWorkbook.Sheets("Data").Cells(PZ_RNG.Row, PZ_RNG.Column + 12)
Packzettelinfo.Time_Special = ThisWorkbook.Sheets("Data").Cells(PZ_RNG.Row, PZ_RNG.Column + 13)
Packzettelinfo.Time_Total = ThisWorkbook.Sheets("Data").Cells(PZ_RNG.Row, PZ_RNG.Column + 14)
Packzettelinfo.Notes_Buero = ThisWorkbook.Sheets("Data").Cells(PZ_RNG.Row, PZ_RNG.Column + 15)
Packzettelinfo.Notes_Lager = ThisWorkbook.Sheets("Data").Cells(PZ_RNG.Row, PZ_RNG.Column + 16)

ThisWorkbook.Sheets("Data").Range("E1") = PZ_RNG.Address 'save data

Else
    MsgBox "Packzettel Nr. " & strSearch & " konnte nicht gefunden werden (Fehler #001)", vbOKOnly
    Packzettelinfo.PZ_ID.SetFocus
    Exit Sub
End If

Now: I wanna save the contents when a button is clicked: 

With ThisWorkbook.Sheets("Data")
.Cells(Range(Range("E1")).Row, Range(Range("E1")).Column + 1) = Packzettelinfo.KD_ID
.Cells(Range(Range("E1")).Row, Range(Range("E1")).Column + 2) = Packzettelinfo.Customer_Combination
.Cells(Range(Range("E1")).Row, Range(Range("E1")).Column + 3) = Packzettelinfo.Ship_ID
.Cells(Range(Range("E1")).Row, Range(Range("E1")).Column + 4) = Packzettelinfo.Author_ID
.Cells(Range(Range("E1")).Row, Range(Range("E1")).Column + 5) = Packzettelinfo.Art_Lager
.Cells(Range(Range("E1")).Row, Range(Range("E1")).Column + 6) = Packzettelinfo.Art_Bestell
.Cells(Range(Range("E1")).Row, Range(Range("E1")).Column + 7) = Packzettelinfo.DTPicker1
.Cells(Range(Range("E1")).Row, Range(Range("E1")).Column + 8) = Packzettelinfo.Calc_Time
.Cells(Range(Range("E1")).Row, Range(Range("E1")).Column + 10) = Packzettelinfo.Time1
.Cells(Range(Range("E1")).Row, Range(Range("E1")).Column + 11) = Packzettelinfo.Time2
.Cells(Range(Range("E1")).Row, Range(Range("E1")).Column + 12) = Packzettelinfo.Time3
.Cells(Range(Range("E1")).Row, Range(Range("E1")).Column + 13) = Packzettelinfo.Time_Special
.Cells(Range(Range("E1")).Row, Range(Range("E1")).Column + 14) = Packzettelinfo.Time_Total
.Cells(Range(Range("E1")).Row, Range(Range("E1")).Column + 15) = Packzettelinfo.Notes_Buero
.Cells(Range(Range("E1")).Row, Range(Range("E1")).Column + 16) = Packzettelinfo.Notes_Lager
End With
MsgBox "Packzettel " & Packzettelinfo.PZ_ID & " wurde gespeichert!", vbOKOnly
Unload Me
End Sub

It errors at the first line ".Cells" already. Dunno whats wrong.

Improve this question
9
  • 2
    Why not just hard-code: .Cells(1, 6).... Range("E1").Row will always be 1, Range("E1").Column will always be 5. Commented May 28, 2020 at 14:26
  • 3
    change all the Range(Range("E1")) to just .Range("E1") Commented May 28, 2020 at 14:26
  • 1
    @ScottCraner This won't work bc follwing: I am saving a Range in that cell, so i save a range in a range basically. Commented May 28, 2020 at 14:48
  • 1
    If you want PZ_RNG to persist between "Load to form" and "Save to sheet" then it needs to be a global.... Commented May 28, 2020 at 15:50
  • 2
    Also, you dont have to refer PZ_RNG every time with workbook and sheet names. Packzettelinfo.KD_ID = PZ_RNG.Offset(0,1) is enough Commented May 28, 2020 at 15:52

1 Answer 1

Reset to default
1

If I understand you correctly, you write the address of a search into cell "E1" and later you want to write something to this cell. All this happens while a user form is active.

I don't like the attempt, as long as the form is active, it is sufficient to define the range variable PZ_RNG as a global variable within the form. In that case you could use something like

With ThisWorkbook.Sheets("Data")
    PZ_RNG.Offset(0, 1) = Packzettelinfo.KD_ID
    PZ_RNG.Offset(0, 2) = Packzettelinfo.Customer_Combination
    (...)
End With

btw: Reading the data from the sheet into the form could also be done much easier:

With ThisWorkbook.Sheets("Data")
    Packzettelinfo.KD_ID = PZ_RNG.Offset(0, 1)
    (...)
End With

explanation is easy, PZ_RNG is already a Range and Offset returns a Range that's a specific number of rows and columns apart (in this case 0 rows below and 1 resp. 2 columns to the right).

That said, coming back to your question and let's pretend you want to stick to your current solution. First thing is to split those monster-statements into smaller pieces, that helps you to debug. First step is to read the cell that holds your address and create a Range of it:

With ThisWorkbook.Sheets("Data")
    Dim adr as String, PZ_RNG as Range
    adr = .range(E1)
    set PZ_RNG = .range(adr)

If this gives a runtime error, you likely have not the correct address - which could by easily checked by checking the content of the variable adr. If no error occurs, you could continue with the syntax suggested above, but even if for any reason you want to stick with the attempt of using the .Cells-syntax and therefore need the row and column, declare 2 variables and use those:

    dim destRow as Long, destCol as Long
    destRow = PZ_RNG.row
    destCol = PZ_RNG.column
    .Cells(destRow, destCol+1) = Packzettelinfo.KD_ID
    (...)

Now if something fails, it is much easier to find the problem (learn to use the debugger!!!). And no, declaring and using some extra variables doesn't slow down execution.

And now, at the end of this rather long answer, the thing that (likely) caused your runtime error. SO is full of cases like this because it is not so obvious: You are using a With-statement (good), but even if you do, so called unqualified terms like Range refer to the active sheet, not the sheet of the With-clause. And also not of the sheet that you are addressing with the Cells-statement. If you want to read the range from the Worksheet that you specify in the With-clause, you have to put a . in front of Range. Every. Single. Time.:

 With ThisWorkbook.Sheets("Data")
    .Cells(.Range(.Range("E1")).Row, .Range(.Range("E1")).Column + 1)

So, likely the Sheet "Data" was not the active sheet, so Range("E1") was not reading the address you stored but something random (which was not an adress), therefore Range(.Range("E1")) had to fail.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you - I edited all these Packzettelinfo.KD_ID = PZ_RNG.Offset(0, 1)for the reading part and PZ_RNG.Offset(0, 1) = Packzettelinfo.KD_ID for the saving part. worked like a charm, and is way easier than With ThisWorkbook.Sheets("Data") .Cells(.Range(.Range("E1")).Row, .Range(.Range("E1")).Column + 1) since i already have that variable global now (Extra Module: Public PZ_RNG As Range ), why not use it! Cheers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.