Bottom Up search for multiple entries

  • Thread starter Thread starter Hans Hamm
  • Start date Start date
Ben

I set a breakpoint so I could see the data coming from Sheet 2 Column C and being pasted into the new sheet. Each variation is copying and pasting the blank cell.

How would I do something like this; If rpaste.Offset(y,0).Value <>"" thenrPaste.Offset(y, 0).Value = rCopy2.Cells(x, 1)

I tried this and did not get it to work... to me it seems like it would.
Click to expand...

Another idea I had was to sort the data in the new worksheet right after the paste... used part of your code to come up with this
With wsNew
wsNew.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("G" & lFirstRow & ":G" & lLastRow) _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SetRange ("G" & lFirstRow & ":G" & lLastRow)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
But it gives me a compile error: invalid use of property on the .Sort (justafter the wsNew
 
Hans,

I think with the first option that you may be checking the wrong range for a "" value. Try setting a breakpoint on that line and looking at the cell references to ensure that it is correct. I think you may need to rewrite it to:

If rCopy2.Cells(x, 1) <>"" then rPaste.Offset(y, 0).Value = rCopy2.Cells(x, 1)

As for the Sort option, I think that the first two lines are a little off. Try this (untested) option instead:

With wsNew.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("G" & lFirstRow & ":G" & lLastRow) _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SetRange ("G" & lFirstRow & ":G" & lLastRow)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
 
Hans,



I think with the first option that you may be checking the wrong range for a "" value. Try setting a breakpoint on that line and looking at the cell references to ensure that it is correct. I think you may need to rewriteit to:



If rCopy2.Cells(x, 1) <>"" then rPaste.Offset(y, 0).Value = rCopy2.Cells(x, 1)



As for the Sort option, I think that the first two lines are a little off.. Try this (untested) option instead:



With wsNew.Sort

.SortFields.Clear

.SortFields.Add Key:=Range("G" & lFirstRow & ":G" & lLastRow) _

, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

.SetRange ("G" & lFirstRow & ":G" & lLastRow)

.Header = xlNo

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With
Click to expand...

Ben,
I have tried your suggestions and "other ideas" to no avail. When I create a breakpoint to look at the new sheet "wsNew" the blank cell is still there.. What I have been doing is just deleting the row out manually after the report has been run. Now, having said that... been running the report now fortwo weeks and most everything is really good! But, came across another "tweek" that would be nice.
After each section has been completed; copied and pasted and sorted, I thenhave the section formatted and charts run on the data.
What I would like to do is, after the copy/paste/sort has been completed then go back to sheet2 and whatever range it is using and give me the column name
So for instance
If
GetDataDetails rData
is = Sheet2.Range("R1:R20000")
Then Sheet2.Range("R1")

I can't get any of the ideas to work, you have a good idea for this and theblank cell issue?
Need to send you a Christmas gift for your help!
 
Hans,

I think that my version of the code might differ from yours after all of these iterations. Would you mind posting your code so that I can see about recreating the error? In fact, it might be even more helpful to post (or email me) a copy of the file (with "dummy" data, of course).

As for the column name, if the range of data is called "rData" and the first cell contains the column name, you could try:

rData.Range("A1").Value

Ben
 
Hans,



I think that my version of the code might differ from yours after all of these iterations. Would you mind posting your code so that I can see aboutrecreating the error? In fact, it might be even more helpful to post (or email me) a copy of the file (with "dummy" data, of course).



As for the column name, if the range of data is called "rData" and the first cell contains the column name, you could try:



rData.Range("A1").Value



Ben
Click to expand...

I can email you the file... I have a "trash" email at (e-mail address removed)
shoot me a quick email there and I will respond
 
Back
Top