Please help. I beg!!

S

Sethaholic

Here's the part of the code giving me trouble:

For Each rngItem In rngData
Set rngOut = rngAccounts.Find(What:=rngItem)

If rngOut Is Nothing Then
rngItem.Offset(0, 2).Value = "N/A"

Else
Set rngOut = rngOut.Offset(0, 1)
Range(rngOut, _
rngOut.End(xlDown).End(xlToRight)).Copy _
Destination:=rngItem.Offset(0, 2)
End If
Next rngItem
Next mysht


What code do I add to it so that when it copies to the destination, it
will shift the entire rows down? I've been trying so hard but i can't
seem to work it out.
I tried Destination:=rngItem.Offset(0, 2).shift(x1down)
but it doesn't work. What should I do? Please please please help.
Thanks!!
 
S

Sethaholic

thanks tom

i did as you said and i have this

Set rngOut = rngOut.Offset(0, 1)
Range(rngOut, _
rngOut.End(xlDown).End(xlToRight)).Copy

rngItem.Offset(0, 2).Insert xlShiftDown


it works, but it only shifts the columns down. I want to shift the
entirerow down. I tried: rngItem.Offset(0, 2).entirerow.Insert
xlShiftDown
but it crashed on me. any ideas??
 
T

Tom Ogilvy

Sub BBB()
Dim rng As Range
Dim rngItem As Range
Dim rngout As Range
Dim sAdd As String
Set rngItem = Range("A5")
Set rngout = Range("H3")
Set rngout = rngout.Offset(0, 1)
Set rng = Range(rngout, _
rngout.End(xlDown).End(xlToRight))
sAdd = rngItem.Address
rngItem.EntireRow.Resize(rng.Rows.Count).Insert xlShiftDown
rng.Copy Destination:=Range(sAdd).Offset(0, 2)
End Sub

Make sure the data you are copying will not be affected by inserting
entirerows.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top