0

I want to be able to add multiple data in 2 sheets. With that I have an error:

Run Time Error '91' : Object Variable or With Block not Set

Referring to this line:

With Sheetclient = ThisWorkbook.Sheets(CMB_Test.Value)

The first page is chosen by a combobox value and it's working well, and the second page will automatically something the page: "testbit".

  Private Sub Save_test_Click()

Dim Sheetclient As Worksheet
Dim testbit1 As Worksheet
Dim nr As Integer, lr As Integer

 With Sheetclient = ThisWorkbook.Sheets(CMB_Test.Value)
 nr = Sheetclient.Cells(Rows.Count, 1).End(xlUp).Row + 1
 Sheetclient.Cells(nr, 5) = Me.TB_dateBit.Value
 Sheetclient.Cells(nr, 6) = Me.serial.Value
 Sheetclient.Cells(nr, 7) = Me.matrice.Value
 Sheetclient.Cells(nr, 8) = Me.CMB_config.Value
 Sheetclient.Cells(nr, 9) = Me.lifetime.Value

 End With

 With testbit1 = ThisWorkbook.Sheets("testbit")
 nr = testbit1.Cells(Rows.Count, 1).End(xlUp).Row + 1
 testbit1.Cells(nr, 1) = Me.TB_dateBit.Value
 testbit1.Cells(nr, 2) = Me.serial.Value
 testbit1.Cells(nr, 3) = Me.matrice.Value
 testbit1.Cells(nr, 4) = Me.CMB_config.Value
 testbit1.Cells(nr, 5) = Me.lifetime.Value
 End with

 End

 End Sub
Mogsdad
  • 44,709
  • 21
  • 151
  • 275
Max078
  • 37
  • 7
  • 1
    Is http://stackoverflow.com/questions/5281759/simple-vba-code-gives-me-run-time-error-91-object-variable-or-with-block-not-set of any help? Usually you need to `set` the object... – Floris Apr 30 '14 at 19:25
  • Check the VBA docs for correct use of With – Tim Williams Apr 30 '14 at 19:28

1 Answers1

2

You need to Set the object - and having Set it, you can use . to reference it. Thus your code might look like this:

Set Sheetclient = ThisWorkbook.Sheets(CMB_Test.Value)
With Sheetclient
  nr = .Cells(Rows.Count, 1).End(xlUp).Row + 1
  .Cells(nr, 5) = Me.TB_dateBit.Value
  .Cells(nr, 6) = Me.serial.Value
  .Cells(nr, 7) = Me.matrice.Value
  .Cells(nr, 8) = Me.CMB_config.Value
  .Cells(nr, 9) = Me.lifetime.Value
End With

Same for the second part of the code

Floris
  • 45,857
  • 6
  • 70
  • 122
  • It'S doesn't work with the second one. I have en error message:Subscript out of range (Error 9) – Max078 Apr 30 '14 at 19:44
  • That error message suggests that you don't have a sheet called "testbit" in `ThisWorkbook`. You could try `Workbooks("name of workbook").Sheets("name of sheet")` - rather than referencing `ThisWorkbook`. Sometimes that works better. – Floris Apr 30 '14 at 19:46