Object Required Error

[Minitman]

Greetings,

I getting this error message:

_________________________
| |
| Run-time error '424': |
| Object required |
|________________________|

From this code:
_______________________________________________

With ws1.Cells("65536", "A").End(xlUp).Row + 1
Set rng1 = .EntireRow
Set rng2 = .Offset(1, 0)
End With
_______________________________________________

I'm trying to locate the row just below the last row without much
luck!!!

How can I change this code to work?

Anyone help on this problem will be appreciated.

-Minitman

 

[Gary''s Student]

It needs a range:

Sub qwerty()
Set ws1 = Sheets("Sheet1")
n = ws1.Cells("65536", "A").End(xlUp).Row + 1
Set r = Cells(n, "A")
With r
Set rng1 = .EntireRow
Set rng2 = .Offset(1, 0)
End With
End Sub

 

[Dave Peterson]

..Row returns a number -- not a range:

With ws1.Cells("65536", "A").End(xlUp).offset(1,0) 'maybe????
Set rng1 = .EntireRow
Set rng2 = .Offset(1, 0) 'do you still need this
'or
Set rng2 = .cells 'single cell under last used cell in column A
End With

But I'm not sure what you want rng1 and rng2 to be.

 

[Minitman]

Hey Gary's Student,

Thanks for the reply.

However, I get the same error from

"n = ws1.Cells("65536", "A").End(xlUp).Row + 1"
which is where I got from in my code.

Though great minds think alike, I don't think it helped in this
instance <G>

Thanks for trying.

-Minitman

 

[Minitman]

Hey Dave,

Thanks for replying.

I am getting the strong impression that my approach is all wrong.

Perhaps a note on what I need accomplished might be in order here <G>.

This is the routine that lays down the contents of either the UserForm
TextBoxes or the worksheet named ranges to the worksheet called
CustInfo. Maybe a bit too ambitious.

This data is either going to modify a chosen row or create a new
record after the last record. I am trying to acquire the row numb and
pass it to the actual paste sub (which is working).

The paste down sub will simply paste the data, from whichever source,
overwriting the row number that is passed to it as an argument, it
does not care. The passed though row number is what I am trying to
come up with. Modify is done. It is that last row I'm having trouble
with. Note: The last row is a formatting and formula template. I
need to copy this entire row (column A to CD) to the row below it and
then paste over columns E to CD with TextBoxes 5 to 82. Keeping the
formatting for the entire row and the formulas in the first 4 columns.

The modify option simply captures the row number of the record that is
chosen and passing the number though as an argument to pasting sub.

-Minitman

 

[Dave Peterson]

I use something like this pseudo code (I didn't build the worksheet and
userform):

Dim DestCell as Range
dim res as variant 'could return an error

with worksheets("Custinfo")
'if the values are text in column A
res = application.match(me.textbox1.value,.range("A:A"),0)
'if the values are integers in column A
res = application.match(clng(me.textbox1.value),.range("A:A"),0)

if iserror(res) then
'didn't find a match, add a row
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
else
'found a match in row # res
'matched returned the number of the row that matched
set destcell = .range("a:a")(res)
end if
End with

Then I can use DestCell as the range to paste.

 

[Minitman]

Hey Dave,

The key to what I was missing is in this line:

set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)

Changed to:

With ws1.Cells(65536, "A").End(xlUp)
Set rng1 = .EntireRow
Set rng2 = .Offset(1, 0)
End With
rng1.Copy Destination:=rng2
l = rng2.Row - 1
PasteDown1 l

With that and a bit of tweaking. My paste was off by 1 column. It
took a while to figure out which offset needed to be tweaked. But it
is working now.

Thank you for you help. It is really appreciated. <VBG>

-Minitman

 

[Dave Peterson]

Glad you got it working.

Hey Dave,

The key to what I was missing is in this line:

set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)

Changed to:

With ws1.Cells(65536, "A").End(xlUp)
Set rng1 = .EntireRow
Set rng2 = .Offset(1, 0)
End With
rng1.Copy Destination:=rng2
l = rng2.Row - 1
PasteDown1 l

With that and a bit of tweaking. My paste was off by 1 column. It
took a while to figure out which offset needed to be tweaked. But it
is working now.

Thank you for you help. It is really appreciated. <VBG>

-Minitman