Adding a range error plz help

[Patriot2879]

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?

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

 

[jproffer]

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

 

[Patriot2879]

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

emptyRow = 38.Range("A:K").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

 

[jproffer]

No, I mean this:

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:

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??

 

[Patriot2879]

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

 

[Joe4]

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:

emptyRow = Cells(Rows.Count,"A").End(xlUp).Row + 1

 

[Patriot2879]

sorry me again how do i get a automatical add a new line please in the code?

 

[Joe4]

sorry me again 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.

 

[Patriot2879]

just seen your response from earlier this works great thank you

 

[Joe4]

You are welcome.
Glad we could help.