Adding a range error plz help

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,259
Office Version
  1. 2010
Platform
  1. Windows
hi, i got the code below where i want to add the data from A38 - K38 but i keep getting error 'Run-time error 91 - object variable or with block variable not set' please can you help me with this?

Code:
Private Sub OKButton_Click()


Dim emptyRow As Long

With ThisWorkbook.Sheets("Northants")
   emptyRow = .Range("A37:K37").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

   'Transfer Information
   .Cells(emptyRow, 1).Value = DateBox.Value
   .Cells(emptyRow, 2).Value = TimeBox.Value
   .Cells(emptyRow, 3).Value = MPANBox.Value
   .Cells(emptyRow, 4).Value = JobBox.Value
   .Cells(emptyRow, 5).Value = MTBox.Value
   .Cells(emptyRow, 6).Value = CustomerBox.Value
   .Cells(emptyRow, 7).Value = ContactBox.Value
   .Cells(emptyRow, 8).Value = AddressBox.Value
   .Cells(emptyRow, 9).Value = PostCodeBox.Value
   .Cells(emptyRow, 10).Value = NotesBox.Value
   .Cells(emptyRow, 11).Value = CARRBox.Value
End With

End Sub
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Well since you're always going to get Row 38 unless row 37 is completely empty between A and K (and if it is, that would bug out anyway, I think), why not just hard code emptyRow?

emptyRow = 38
 
Upvote 0
hi thank you for the advise how do i put the emptyRow = 38 into the code please i have teried the below code and i ge an error
Code:
emptyRow = 38.Range("A:K").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
 
Upvote 0
No, I mean this:

Code:
emptyRow = [B][COLOR=#ff0000].Range("A37:K37").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1[/COLOR][/B]

will always return 38. You're only looking in one row, 37...so if anything is found in row 37, then you add one row, it will alwyas be 38.

So why not just do this:

Code:
Private Sub OKButton_Click()


Dim emptyRow As Long

With ThisWorkbook.Sheets("Northants")
   emptyRow = 38

   'Transfer Information
   .Cells(emptyRow, 1).Value = DateBox.Value
   .Cells(emptyRow, 2).Value = TimeBox.Value
   .Cells(emptyRow, 3).Value = MPANBox.Value
   .Cells(emptyRow, 4).Value = JobBox.Value
   .Cells(emptyRow, 5).Value = MTBox.Value
   .Cells(emptyRow, 6).Value = CustomerBox.Value
   .Cells(emptyRow, 7).Value = ContactBox.Value
   .Cells(emptyRow, 8).Value = AddressBox.Value
   .Cells(emptyRow, 9).Value = PostCodeBox.Value
   .Cells(emptyRow, 10).Value = NotesBox.Value
   .Cells(emptyRow, 11).Value = CARRBox.Value
End With

End Sub

So, now in reality, this leads me to believe you do not want to only look in row 37, so what are you trying to do?? :)
 
Last edited:
Upvote 0
thank you :) that works great, i am still learning and i am very greatful with all the help i get from this forum it is amazing :)
 
Upvote 0
If you are just trying to dynamically find the first blank row of data (by finding the last row of data, then moving down one row), then assuming that column A will always be populated for your data rows, here is another method to find the first empty row:
Code:
emptyRow = Cells(Rows.Count,"A").End(xlUp).Row + 1
 
Last edited:
Upvote 0
sorry me again :) how do i get a automatical add a new line please in the code?
 
Upvote 0
sorry me again :smile: how do i get a automatical add a new line please in the code?
Not sure what you mean by that. Please explain in greater detail.
 
Upvote 0
You are welcome.
Glad we could help.
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

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
Back
Top