problems with .EntireRow function

[DavidAtCaspian]

Anyone come across this before. This code has worked fine in the past, but I
have one particular sheet which behaves very strangely.
I have

Dim ThisRow as Range

and am working down one sheet, collecting data, and moving some parts to
another. (src - source, dest - destination)

The 'business' line is:

Set ThisRow = wsSrc.Range("A" & nDRow).EntireRow '{nDrow is my data row}

then

wsDest.Range("AG" & nDRow) = ThisRow.Range("AB" & nDRow).Value

should take the value from wssrc cell AB5 and put it into wsDest cell AG5.

What it actually does is take the data from cell AB9
When nDrow is 6, my data should come from AB6, but it actually comes from
AB11, and when ndrow is 7, my destination data comes from row 13. - In each
case from the correct column.

Any idea what is going on here? I'd have though this ought to be impossible.
The only reason I've found it was that my data integrity check reported a
problem, and I could find nothing wrong with the orginal row (6339). I
spotted that the name wasn't right, and finally found the duff source data in
row 12667.

As far as I can tell, this only happens with one of my source sheets, but my
confidence is a bit shaken!!!

Any thoughts appreciated.

[Bob Phillips]

It is because, once you have calculated the entirerow you then offset it
again using nDRow. You only want to offset ThisRow to AB, not by more rows
as in nDRow as well.

You should use

wsDest.Range("AG" & nDRow) = ThisRow.Range("AB1").Value

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DavidAtCaspian" <DavidAtCaspian@discussions.microsoft.com> wrote in message
news:DAE93214-5AD8-4188-A025-739DD0E1F8E6@microsoft.com...
> Anyone come across this before. This code has worked fine in the past, but
I
> have one particular sheet which behaves very strangely.
> I have
>
> Dim ThisRow as Range
>
> and am working down one sheet, collecting data, and moving some parts to
> another. (src - source, dest - destination)
>
> The 'business' line is:
>
> Set ThisRow = wsSrc.Range("A" & nDRow).EntireRow '{nDrow is my data
row}
>
> then
>
> wsDest.Range("AG" & nDRow) = ThisRow.Range("AB" & nDRow).Value
>
> should take the value from wssrc cell AB5 and put it into wsDest cell AG5.
>
> What it actually does is take the data from cell AB9
> When nDrow is 6, my data should come from AB6, but it actually comes from
> AB11, and when ndrow is 7, my destination data comes from row 13. - In
each
> case from the correct column.
>
> Any idea what is going on here? I'd have though this ought to be
impossible.
> The only reason I've found it was that my data integrity check reported a
> problem, and I could find nothing wrong with the orginal row (6339). I
> spotted that the name wasn't right, and finally found the duff source data
in
> row 12667.
>
> As far as I can tell, this only happens with one of my source sheets, but
my
> confidence is a bit shaken!!!
>
> Any thoughts appreciated.
>
>
>
>
>
>

[DavidAtCaspian]

Thanks Bob.

I re-read the explanations of range, and think I have it now.
The reason all the other sheets are OK, is that this is the only one that
needs transposition.

So all the others have the value of the entire row copied in, and in the
problem sheet the work lines now look like this:

wsDest.Range("AG" & nDRow) = ThisRow.Range("AB1").Value


Thanks for the help.

David





If nDrow is 7----
I get exactly the same problem. ThisRow.range(AB7) is actually populated
from the original sheet row 9.

In any case, since ThisRow should just be a single row. and certainly
Rows(nDrow) should be a single row, then either both the
Rows and EntireRow function are taking the wrong row, or are taking more
than one row, and then working from the back!)





"Bob Phillips" wrote:




> It is because, once you have calculated the entirerow you then offset it
> again using nDRow. You only want to offset ThisRow to AB, not by more rows
> as in nDRow as well.
>
> You should use
>
> wsDest.Range("AG" & nDRow) = ThisRow.Range("AB1").Value
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "DavidAtCaspian" <DavidAtCaspian@discussions.microsoft.com> wrote in message
> news:DAE93214-5AD8-4188-A025-739DD0E1F8E6@microsoft.com...
> > Anyone come across this before. This code has worked fine in the past, but
> I
> > have one particular sheet which behaves very strangely.
> > I have
> >
> > Dim ThisRow as Range
> >
> > and am working down one sheet, collecting data, and moving some parts to
> > another. (src - source, dest - destination)
> >
> > The 'business' line is:
> >
> > Set ThisRow = wsSrc.Range("A" & nDRow).EntireRow '{nDrow is my data
> row}
> >
> > then
> >
> > wsDest.Range("AG" & nDRow) = ThisRow.Range("AB" & nDRow).Value
> >
> > should take the value from wssrc cell AB5 and put it into wsDest cell AG5.
> >
> > What it actually does is take the data from cell AB9
> > When nDrow is 6, my data should come from AB6, but it actually comes from
> > AB11, and when ndrow is 7, my destination data comes from row 13. - In
> each
> > case from the correct column.
> >
> > Any idea what is going on here? I'd have though this ought to be
> impossible.
> > The only reason I've found it was that my data integrity check reported a
> > problem, and I could find nothing wrong with the orginal row (6339). I
> > spotted that the name wasn't right, and finally found the duff source data
> in
> > row 12667.
> >
> > As far as I can tell, this only happens with one of my source sheets, but
> my
> > confidence is a bit shaken!!!
> >
> > Any thoughts appreciated.
> >
> >
> >
> >
> >
> >
>
>
>