Hi,
I am new to VBA and have a question on union
Ive two ranges that Ive combined using union and then applied sort to union, I get the above error
Code:
Worksheets("Conso").Select
'create lookup table for competency to get competency description
lstcmprw = Worksheets("Conso").Cells(Rows.Count, 1).End(xlUp).Row
'initialize range
Set Cmptbl = Worksheets("Conso").Range("A1")
Set comp = Worksheets("Conso").Range("A2 : A" & lstcmprw)
Set compdesc = Worksheets("Conso").Range("U2:U9")
Set cp = Worksheets("Conso").Range("L2:L9")
Set Cmptbl = Application.Union(cp, compdesc)
Cmptbl.Sort
all are either defined as variables or ranges
Any help is appreciated.
I have a column with a value resulting from a fairly complex formula:
I will not include the formula, but basically what it does is a number of calculations including subtracting dates, that is
TODAY'S DATE - A GIVEN DATE (provided in a different cell)
That means that, either manually or automatically, those values are constantly recalculating (with every second that goes by, that value changes).
And the number of values/cells to update is not few: about 1100.
Here's the problem.
When I press SORT, Excel seems to be absolutely incapable of sorting that list correctly. The values are completely and randomly out of order.
I wonder if it could be due to the fact that given that those values would be constantly changing, there would be a conflict with the commands
sort
update
Could this be it?
I am desperate because I have worked for hours to come up with that formula to be able to have that column and all my work was dependent on it being sorted. If I can't sort it, I'm dead.
Please help!!
(Excel 2000) I have 2 "header rows" that I don't want included when I sort
the page. It worked fine at first, just clicking the sort ascending or sort
descending icon, then something changed. Even if I go to "data sort" and
check "header row" it only recognizes the top row, not the second. I've had
this happen before but can't remember the cure.
i have a task of producing a spreadsheet that asks questions and takes figures then displays them as a report.
e.g how much is your energy bill? the user types a figure, its calculated in a formula, and the results displayed in a report.
I need it set out so it looks professional and user friendly. Has anyone got any examples of well laid out user friendly colourful spreadsheets?
Would appriciate it if some one could help me.
Thanks a great deal
Hi,
I am looking for a good website specially a video tutorial that shows you how to create dashboards or charts. I am looking for similar website like http://www.datapigtechnologies.com/
this is very good website for those of you who need to do some fancy staff with excel. Any help will be appriciated. Thanks.
hi, i would like to know the site or resource for mastering Loops like DO loops and other loops with examples.if any body knows some sites do list here.
Hello everybody,
Can anybody upload the
2500 Excel VBA Examples, if you have.
I am trying to set up a sort macro that the user can implement. The user receives the spreadsheet with data in column 1, 2, and 191 of a variable number of rows starting with 5. There are also columns in between that have data, but most are empty for the user to fill. I want the user to be able to sort on column 1 or column 2. All rows past the last row with data in column 1,2,191 are hidden and locked. All columns past 191 are hidden and locked. I keep getting a "Sort method ...failed" message when I try to run this code:
Private Sub cmdSort_Click()
Dim intRows As Integer
Me.Unprotect (mstrPssWrd)
intRows = Cells.SpecialCells(xlCellTypeLastCell).Row
If Me.cmdSort.Caption = "PGY Sort" Then
Range(Cells(mint1stDataRow, mintNmCol), Cells(intRows, mintLastDataCol)).Sort _
key1:=Cells(mint1stDataRow, mintPGYCol), _
key2:=Cells(mint1stDataRow, mintNmCol), dataoption1:=xlSortTextAsNumbers
Me.cmdSort.Caption = "Alpha Sort"
Else 'sort alpha
Range(Cells(mint1stDataRow, mintNmCol), Cells(intRows, mintLastDataCol)).Sort _
key1:=Cells(mint1stDataRow, mintNmCol)
Me.cmdSort.Caption = "PGY Sort"
End If
Me.Protect (mstrPssWrd)
End Sub
I read something about data needing to exist in the range, but does that mean all cells in the range?
Any help or suggestions are deeply appreciated.
Peg
Hi,
I keep getting this error message when I try to sort this dates with the following code. It used to work with no problems, but I have since protected certain areas of the sheet, I wouldn't think this would affect it but can anyone help.
Sef
Code:
Private Sub Date_sort()
Range("DataPC").Sort Key1:=Range("A6:A105"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Cells(6, 1).Select
End Sub
This last line just selects a preferred cell, so please ignore.
