non-expanding range, and also a VBA Sort error

K

Keith

I have a macro that appends values from Sheet14 to the end of a list of data
on Sheet13, then sorts the entire group in alpha order. My first problem is
that the range (rng) isn't updated- it refers to Prescreenlist which is a
Steve Bullen auto-expanding range, but I'm guessing this code takes a
reference to that range in the beginning when the rng is set, and doesn't
update it every time a new item is added. That may or may not be related to
the second problem, which is an error with the sort command (select method
of range class failed). Using XL2003. I've noted the two rows below that are
causing problems.

1. What is the appropriate way to get the rng reference to update (use the
auto-expanded range dynamically)?
2. The select statement highlights the appropriate range on the worksheet


Private Sub cmdLoadNames_Click()

Dim rng As Range, TempRng As Range, sVal As String, res As Variant, res2 As
Variant
Set rng = ThisWorkbook.Names("Prescreenlist").RefersToRange 'the names
that have already been transferred
Set rng2 = ThisWorkbook.Names("PositionID").RefersToRange

RawID = Val(Trim(Sheet14.Range("A1").Value))
'MsgBox RawID

For I = 2 To 65000
'Check each line for a new name
RawName = Trim(Sheet14.Range("A" & CStr(I)).Value)
'Check to make sure it is a real name, not just an extra line
If Len(RawName) > 4 Then
'Check to see if the name is already on the list
res = Application.Match(RawName, rng, 0)
'If no match was found
If IsError(res) Then
'add the name to the list
'next line should update rng each loop so that new name is
always appended once cell after previous name
Set TempRange = rng.Offset(rng.Rows.Count, 0).Resize(1, 1).Cells
' = Rawname '<<this overwrites each time a name is added
TempRow = rng.Offset(rng.Rows.Count, 0).Resize(1, 1).Row
Dim MyCell As Range
For Each MyCell In TempRange
MyCell.Value = RawName
Next
'add position info
'1. find which column
res2 = Application.Match(RawID, rng2, 0)
'2. have to ensure a position match before processing
If IsError(res2) Then
MsgBox "The position ID was not found", , "Position ID
mismatch"
ClearOld = False
Exit Sub
Else
'3. check to see if info is already in that cell
UseCol = ConvertCol(res2 + 1)
TempCol = UseCol
TempOld = Sheet13.Range(TempCol & CStr(TempRow)).Value
If TempOld = "" Then
Sheet13.Range(TempCol & CStr(TempRow)).Value = "A"
ElseIf TempOld = "A" Then
'do nothing
Else
'a value already exists! do nothing for now, assume that
we
'don't want to overwrite an existing code
End If
End If

End If
Else
Exit For 'exit loop b/c end of names list
End If
Next

Sheet14.Columns("A:A").Select
Selection.ClearContents
Range("A1").Select

Sheet13.Activate
Sheet13.Range("A11:Z10000").Select
Selection.Sort Key1:=Range("A11"), Order1:=xlAscending, Header:=xlNo, _
'<<this is where the code breaks
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheet13.Range("A11").Select

End Sub
 
K

Keith

Oops, I stopped execution at the wrong place when writing my post- the real
error given on the sort is:

Run time error 1004

The sort reference is not valid. Make sure that it's within the data you
want to sort, and the first sort by box isn't the same or blank

Thanks,
Keith
 
D

Dave Peterson

This looks like code that is behind a commandbutton placed on a worksheet.

If that's the case, then the unqualified Range("A11") still belongs to the sheet
that owns the code--not Sheet13.

One way around it is to qualify the range.

Selection.Sort Key1:=sheet13.Range("A11"), Order1:=xlAscending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Another way is to dump the .select's and .activate's:

Sheet13.Activate
Sheet13.Range("A11:Z10000").Select
Selection.Sort Key1:=Range("A11"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheet13.Range("A11").Select

Becomes:

with Sheet13
with .Range("A11:Z10000")
.cells.Sort Key1:=.columns(1), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
end with
end with

You may find that the code is easier to read/maintain and even works a bit
quicker, too.
Sheet14.Columns("A:A").Select
Selection.ClearContents
Range("A1").Select
Click to expand...

Could become
 
K

Keith

Thanks Dave!
:)
Dave Peterson said:
This looks like code that is behind a commandbutton placed on a worksheet.

If that's the case, then the unqualified Range("A11") still belongs to the
sheet
that owns the code--not Sheet13.

One way around it is to qualify the range.

Selection.Sort Key1:=sheet13.Range("A11"), Order1:=xlAscending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Another way is to dump the .select's and .activate's:

Sheet13.Activate
Sheet13.Range("A11:Z10000").Select
Selection.Sort Key1:=Range("A11"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Sheet13.Range("A11").Select

Becomes:

with Sheet13
with .Range("A11:Z10000")
.cells.Sort Key1:=.columns(1), Order1:=xlAscending, Header:=xlNo,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
end with
end with

You may find that the code is easier to read/maintain and even works a bit
quicker, too.


Could become
Click to expand...
 
K

Keith

Thanks to Dave for fixing problem #2. I'm still struggling with how to get
the named auto-expanding range to update after each loop. Interestingly,
when I added in another Set statement right before the line that I don't
believe was updating, it updated on the first loop through but not the
second. The first name was added on the first available cell, the second
name was added in the next available cell, then all subsequent names was
copied over the second name, never incrementing to the next cell.

I'm thinking maybe the code that selects the cell right after the range
isn't doing what I think it should do, but then I don't know why the first
and second names actually go where they are supposed to go...

Many thanks,
Keith

Named range:
=OFFSET(AllApplicants!$A$11,0,0,COUNTA(AllApplicants!$A$11:$A$14),1)

Relevant code (called from a worksheet button):

Private Sub cmdLoadNames_Click()

Dim rng As Range, TempRng As Range, sVal As String, res As Variant, res2
As Variant
Set rng = ThisWorkbook.Names("Prescreenlist").RefersToRange 'the names
that have already been transferred to sheet13

RawID = Val(Trim(Sheet14.Range("A1").Value))

For I = 2 To 65000
'Check each line for a new name
RawName = Trim(Sheet14.Range("A" & CStr(I)).Value)
'Check to make sure it is a real name, not just an extra line
If Len(RawName) > 4 Then
'Check to see if the name is already on the list
res = Application.Match(RawName, rng, 0)
'If no match was found
If IsError(res) Then
'add the name to the list

'updates from named range to try to get correct rows.count
Set rng = ThisWorkbook.Names("Prescreenlist").RefersToRange
'tries to get the next (empty) cell after the current range to
paste in the new name
Set TempRange = rng.Offset(rng.Rows.Count, 0).Resize(1, 1).Cells
' = Rawname
TempRow = rng.Offset(rng.Rows.Count, 0).Resize(1, 1).Row
'paste the value in the open cell
Dim MyCell As Range
For Each MyCell In TempRange
MyCell.Value = RawName
Next
End If
Else
Exit For 'exit loop b/c end of names list
End If
Next

End Sub
 
D

Dave Peterson

I don't quite understand what you're doing with that last loop.
Dim MyCell As Range
For Each MyCell In TempRange
MyCell.Value = RawName
Next
Click to expand...

But after you've added more to your list, you'll want to have the code refresh
that range object.

I think...

rng.Offset(rng.Rows.Count, 0).Resize(1, 1).value = Rawname
'update rng
Set rng = ThisWorkbook.Names("Prescreenlist").RefersToRange
 
K

Keith

I was trying to figure out how to place the value of RawName into a single
cell at the end of the 'old' range; I managed to get TempRange referring to
the right range, but not (by default) a single cell [I'm used to notation
like range("A1").value = z]- so the code you indicate was my attempt just to
get the correct value into the cell. It might also work to say
TempRange.value = Rawname, but I kind of came at this sideways, and didn't
want to mess with pieces of code that were working unless I knew what I was
doing... <vbg>
Thanks,
Keith
 

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

Similar Threads

Expanding a range 2
VBA Pivot Table Error 1
Please Help! Need to add a Variable Range in my Excel Macro withPivot Tables 3
Named range not expanding with insertions after sort?? 2
Filter, Delete and Error handling - need help 4
Use a VBA function to return a factor 2
Set XL range into Wrod bookmark as a table? 2
Clear if "#N/A" and Find End of Range, Fill Blanks 4

Top