Compile error end of statement to read to end of data set instead of a set number of rows

[fvisions]

Compile error: Expected: end of statement
I am changed my code to read column V to endo or the full worksheet, it was v2:v1000 but sometimes there are more than 1000 rows and I am receiving the compile error. I am not sure who to correct this.
Dim LastRow As Long
LastRow = Cells(Rows.Count, 2).End(xlUp).Row

ActiveWorkbook.Worksheets("Installation wkg").Sort.SortFields.Add2 Key:=range _
        ("V2:V" & LastRow).SortOn:= xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

 

[fvisions]

The word "range" in " "Key:=range _" should be capitalized.
The fact that it is not seems to imply that you may have a variable, procedure, or function named "range".
That is a big no-no. You should never use reserved words (names of existing functions, objects, or properties) as the names of your variables or procedures.
That can cause unpredictable behavior and errors.

The capital R will not stay?? I attempted find replace for range and they all stay as lower case.

 

[Joe4]

The capital R will not stay?? I attempted find replace for range and they all stay as lower case.

Basically, if you have all the arguments set up correctly, Excel will recognize that is a range declaration automatically make the "r" in "range" capital.
If it does not, then one of two things is going on:
1. "range" is used elsewhere, as a variable, procedure, or user defined function
- or -
2. the argument is not set-up properly, so Excel does not recognize that as a range declaration

I thought it was the first case, but Fluff found that it was the second. By using the "." instead of the ",", it was including eveything after the "." as part of that argument, which is not a valid argument that Excel recognizes. So it did not capitalize the "r". Excel literally had no idea what to do with that statement.

 

[fvisions]

Basically, if you have all the arguments set up correctly, Excel will recognize that is a range declaration automatically make the "r" in "range" capital.
If it does not, then one of two things is going on:
1. "range" is used elsewhere, as a variable, procedure, or user defined function
- or -
2. the argument is not set-up properly, so Excel does not recognize that as a range declaration

I thought it was the first case, but Fluff found that it was the second. By using the "." instead of the ",", it was including eveything after the "." as part of that argument, which is not a valid argument that Excel recognizes. So it did not capitalize the "r". Excel literally had no idea what to do with that statement.

Ok so when I run the macro, I don't receive any errors. When I have a co-worker run it she is getting the run time error 438 object doesn't support this property or method. When she selects debug it highlights the code we have been talking about. This is over my head and I don't know what to do. Any advice?

 ActiveWorkbook.Worksheets("Installation wkg").Sort.SortFields.Add2 Key:=range _
        ("v2:v" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

 

[fvisions]

Maybe it would help if I gave you all the code for the sort process. This is supposed to sort the full worksheet by column V.

 ActiveWorkbook.Worksheets("Installation wkg").Sort.SortFields.Add2 Key:=range _
        ("v2:v" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Installation wkg").Sort
        .SetRange range("A1:AD" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    range("v1").Select
End Sub
[QUOTE="fvisions, post: 5563431, member: 99647"]
Ok so when I run the macro, I don't receive any errors. When I have a co-worker run it she is getting the run time error 438 object doesn't support this property or method. When she selects debug it highlights the code we have been talking about. This is over my head and I don't know what to do. Any advice?
[CODE=vba] ActiveWorkbook.Worksheets("Installation wkg").Sort.SortFields.Add2 Key:=range _
        ("v2:v" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

[/QUOTE][/CODE]

 

[Joe4]

Are you both using the same version of Excel?

Another thing to check.
From the VB Editor on your computer, go to the "Tools" drop-down menu, and select "References". Note all the different references that have been selected.
Now, go to the computer where it is NOT working, and do the same thing. Are all the same references selected? If not, select the missing ones.

 

[Fluff]

Try changing Add2 to Add

 

[Joe4]

Try changing Add2 to Add

Out of curiosity, is that a Excel version thing (i.e. old versions don't recognize "Add2")?

 

[fvisions]

Are you both using the same version of Excel?

Another thing to check.
From the VB Editor on your computer, go to the "Tools" drop-down menu, and select "References". Note all the different references that have been selected.
Now, go to the computer where it is NOT working, and do the same thing. Are all the same references selected? If not, select the missing ones.

Thank You I will check with her. If this happens to be the issue is this thing I will have to do for each user?

 

[Fluff]

Out of curiosity, is that a Excel version thing (i.e. old versions don't recognize "Add2")?

Yes it is, I think that Add2 came into being with 2016, it has something to do with subfields whatever they are.

 

[fvisions]

Out of curiosity, is that a Excel version thing (i.e. old versions don't recognize "Add2")?

I was about to ask what the difference was...I am on windows 10 but my co worker is not