Close Window
Free Ebook
Get Your Free Excel
ebook!
Top 15 Excel Tutorials from TeachExcel.com
Instant Access!
E-mail:

Trying To Sort Strings In Two-dimensional Array Vba

Hi Guys,

I have been trying to sort a two-dimensional array in excel using VBA.
the values of the array are all strings not numbers, which has been causing my head to explode.

I have an excel sheet that contains data 4 rows by 4 columns.
I then want to put that data in to an array so I can use it.

This is the first time I have been playing with 2D Arrays in VBA. From what I gathered this is how it should look:

Code:

 Dim MyArray(1 To 4, 1 To 4)
 MyArray(1, 1) = "A col1 row1"
 MyArray(1, 2) = "C col1 row2"
 MyArray(1, 3) = "D col1 row3"
 MyArray(1, 4) = "B col1 row4"
 MyArray(2, 1) = "D col2 row1 - sort this col"
 MyArray(2, 2) = "B col2 row2 - sort this col"
 MyArray(2, 3) = "A col2 row3 - sort this col"
 MyArray(2, 4) = "C col2 row4 - sort this col"
 MyArray(3, 1) = "A col3 row1"
 MyArray(3, 2) = "D col3 row2"
 MyArray(3, 3) = "B col3 row3"
 MyArray(3, 4) = "C col3 row4"
 MyArray(4, 1) = "D col4 row1"
 MyArray(4, 2) = "C col4 row2"
 MyArray(4, 3) = "B col4 row3"
 MyArray(4, 4) = "A col4 row4"


so going by this principal i tried to sort the array by the 2nd column. I tried various examples from other sites but they all say that "subscript out of range"

can anyone please let me know if I am going about this completely the wrong way. Any help greatly appreciated. Thanks

Code:

 
 Dim MyArray(1 To 4, 1 To 4)
 Dim i As Integer, j As Integer
 MyArray(1, 1) = "A col1 row1"
 MyArray(1, 2) = "C col1 row2"
 MyArray(1, 3) = "D col1 row3"
 MyArray(1, 4) = "B col1 row4"
 MyArray(2, 1) = "D col2 row1 - sort this col"
 MyArray(2, 2) = "B col2 row2 - sort this col"
 MyArray(2, 3) = "A col2 row3 - sort this col"
 MyArray(2, 4) = "C col2 row4 - sort this col"
 MyArray(3, 1) = "A col3 row1"
 MyArray(3, 2) = "D col3 row2"
 MyArray(3, 3) = "B col3 row3"
 MyArray(3, 4) = "C col3 row4"
 MyArray(4, 1) = "D col4 row1"
 MyArray(4, 2) = "C col4 row2"
 MyArray(4, 3) = "B col4 row3"
 MyArray(4, 4) = "A col4 row4"
 
 SortColumm1 = 1
 SortColumn2 = 3
 For i = LBound(MyArray, 1) To UBound(MyArray, 1) - 1
 For j = LBound(MyArray, 1) To UBound(MyArray, 1) - 1
 Condition1 = MyArray(j, SortColumn1) > MyArray(j + 1, SortColumn1)
 Condition2 = MyArray(j, SortColumn1) = MyArray(j + 1, SortColumn1) And _
 MyArray(j, SortColumn2) > MyArray(j + 1, SortColumn2)
 If Condition1 Or Condition2 Then
 For y = LBound(MyArray, 2) To UBound(MyArray, 2)
 t = MyArray(j, y)
 MyArray(j, y) = MyArray(j + 1, y)
 MyArray(j + 1, y) = t
 Next y
 End If
 Next
 Next





Free Excel Courses



Similar Excel Tutorials


I'll show you how to loop through an array in VBA and macros in Excel.  This is a fairly simple concept but it can ...

How to perform case sensitive sorting in Excel. Select a cell within the data that you want to sort and then go to ...

This Excel tip shows you how to Sort Data Alphabetically and Numerically in Excel 2007. This is a great feature to ...

How to sort columns of data in Excel.  This is the same as sorting left to right. This will change the position of ...


Helpful Excel Macros


- This macro will perform a bubble sort in excel. You use it simply by selecting one column to sort and then running the

- Sort data that doesn't have headers in ascending order in Excel with this macro. This is a simple sort macro that assum

- This macro will sort all of the worksheets in the current workbook. It can sort in ascending or descending order. This

- Sort data in Excel that doesn't have headers using a macro. This Excel macro assumes that your data set does not have a

- Macro that sorts data that has headers in ascending order in Excel. This macro assumes that you data has headers on it.

Similar Topics







How do I sort but have the other data move with the column I'm sorting? Whenever I sort, for example, by name, the address and phone numbers columns stay the same and are therefore incorrect with the newly sorted columns. I've looked everywhere.


Good mornng - I am new to the forum - my name is Jena

My question is this - I have a large spreadsheet with multiple columns of information. I want to sort it by a certain column. I know how to do this. My question is, once I'm done with my work I want to sort it back to the original "sort" but I'm not sure how they have it sorted. I've tried to figure out how they have it sorted but can't. Is there some way to go back to the original sort?

Thank you for your anticipated help.

Jena

Hi,

Can someone please help? I've got quite a lot of data that I want to sort
by the persons date of birth, but because I have cells with formula in it
(this works out the persons age) the sort function is changing the formulas
so the formulas no longer work becuase it changes the cell where it is
getting the data from. Does anyone have any ideas how this could be fixed??

Thanks!



Hi all,

I'm looking for help in building a formula which will sort numbers into different "buckets". My spreadsheet has a range of values in column B. These values can range anywhere from -100,000,000 to +10,000,000. I'd like to be able to sort them into the following buckets:-


I have a workbook with 50 sheets (1 for each employee).

How can I get Excel to sort these into alphabetical order ?

(From )




Good Morning Folk

Is there a way to sort a list of names alphabetically and put any blank cells to the bottom rather than the top

Cheers Jim


I am having trouble with a sort. I would like a list sorted descending, so that the highest dollar amount is at the top. However, if there are blanks or zeros in the list, it is putting those first. How do I get it to ignore them or put them at the bottom?

Thank you,
Liz


i know how to use the data filters vertically but have been wondering if its possible to filter data horizontally so i could put a filter on, say column c and sort the data across the sheet rather than down. if its not possible i will find another way to do what i want but this seems to be just what i would need. does anyone know if it can be done?


I have attached a schedule for shift allowance calculation. The conditions for payment are as follows:

3 & 4 shifts = 15% Basic
2 shifts = 10% Basic
If on leave for 21 working days or more, no shift allowance
If on leave for less than 21 working days and on 3 or 4 shifts, shift allowance prorated as (Basic*15%)/21*(diff. btw 21 and no of days leave observed)

If on leave for less than 21 working days and on 2 shifts, shift allowance prorated as (Basic*10%)/21*(diff. btw 21 and no of days leave observed)


The formular I used in the attached worked but the only problem is that I had to sort by no. of shifts and adjust the formular first. I need A formular that does not require sorting. I tried to combine IF, AND, OR formulae but I got error. Please assist me sort this out. Thanks a bunch.


Good afternoon!

I'm working with Excel 2003 sp 3 on Windows xp.

I have been banging my head trying to jar loose the formula to calculate man days where 1 equals 8 hours. I have one that was given to me by a coworker:

=INT(Q3/8)+(((Q3/8)-(INT(Q3/8)))*0.8)

That calculates man days from total hours. I need a formula that will total man days and return the man day answer. Example:

weekending 12/25 = 16.2 which is 16 man days and 2 hours
weekending 1/1 = 24.7 which is 24 man days and 7 hours

Total = 41 man days and 1 hour

I have also been asked if there was a way for a number to be entered into a spreadsheet and automatically put in order without a macro or "pushing a button".

Any advice is greatly appreciated!

Happy New Year!

~ Brenda ~


Hi
I am using Excel 2007 and suddenly I can no longer filter by color. The option is greyed out and so is the sort by Color option. Does anyone have any idea why this would happen?

Thanks in advance.


Hi All,

I am trying to make excel automatically add a leading zero to values which are 5 digits long;

i.e. number input is 15185, then excel automatically changes it to 015185.

If I put a Customer Number Format of 0##### it works, however, a user could put any length of number into these cells, and if the number is less than 5 digits I don't want a leading zero.

Is there any way of writing a small macro to sort this out.

The numbers would be input into range B16:223.

Many thanks,


Andy


I feel like this should be really easy and I am just out of it today but I cannot figure this out. I want to lock a row and filter (with filter,not sort, tool) the other rows. I attached an example

The row in RED I want to never be changed or added in the filter, but I want row 2 and 1 to filter together. So I want (in this case) the flavor to filter with the same number. SO i guess the filter process would be dependent upon the total number of votes. Any ideas? Thanks!

I had to create the information in tables and went from there. Got it. Thanks!


is it possible to put two formulasinto one cell?

On the attached timesheet there are columns IN, OUT, IN,OUT
The lunch time is worked out by deducting 'C8 from C9' and this works fine when all 'IN, OUT' cells are filled in but for some reason when just the first two cells are filled in it gives a -12 hour answer.

looking at cell 'C11 ' in the attached worksheet, I currently have the formula
=IF(C13=0,","SUM(C9-C8)) The reason for this is to make it look tidy by having blank cells until a calculation is needed,, My problem is, If someone just comes in for the morning one day for instance 8am to 12 noon then they won't have lunch, but C11 will show -12:00
Can I put another formula into C11 to tell it not to deduct C8 from C9 until a time is put into C9.
The following is the sort of thing (many variations) I have been trying but because I'm hopeless at formulas I'm not having any success
Thanks

=IF(C13=0,","SUM(C9-C8))OR,IF(C9,ISBLANK,"",SUM(C9-C8)


First time in this forum. Hi all. I have a challenging question, is there a way either by VBA or manually (preferably both, if possible) to actually unite the X amount of numbers that are in a cell given the contents is alphanumeric? I'll give you the following examples to see if you can understand what I' referring to?

DATA output should be
asd67,h876 --------> 67876
2,3,ujdj5&34 -------> 23534
909k86m34 --------> 9098634

Hope this makes sense?


I am looking for the code to select the visible data after applying a data filter. Actually I know how to select the data after applying the data filter but the issue is I am not able to exclude the header row and give the target range as used (non-blank) rows only!!

I am using below code to Select the Visible rows in the target range:

Code:

Range("A:p").SpecialCells(xlCellTypeVisible).Select


Problems in this code a

1) after applying the filter, while selecting the data it is selecting all the rows in given range till last row on the workbook. I need this to select the the data only till the last used row in the given range.

2) It is not possible to provide the address of the first row after we apply the filter since the first row address may change depending on the values in the table.

E.g. 1st time when I am running the macro the first row in the visible filtered data is starting at Cell address A4 and next time when I will run the macro it may be A6

3) The Code is also selecting the 1st row which is a header row. How can we exclude it from selection.

Some one please revert with the solution.
Thanks in advance.


Hi,

This is a very basic question, but i have been struggling for hours, so I must ask.

I I trying to simply fill a combobox on a userform from a named range on a sheet, I want to populate the combobox as soon as the userform starts. (I launch the userform from a button on the sheet) Where must I place the code? and will this code work?
Code:

Sub Button1_Click()
Set UserForm1.combobox1.List = Sheets("Sheet1").Range("MyRange")
UserForm1.Show
End Sub


I am very new to vba, so please excuse my ignorance,
any help would be greatly appreciated

regards
jim


Hi-

I am brand new to Mr. Excel and would love some advice.

I searched the boards pretty extensively but could not find what I am looking for...I apologize if this is a duplicate.

I am using Excel 2007

How do you automatically add rows and update values for cells to a linked worksheet in which rows have been added? For example: Sheet 1, columns A & B are linked to Sheet 2, columns A & B. Sheet 2 has values in A1:A5 & B1:B5 and Sheet 1, since it is linked, has the same info. I want to add a row in between 3 & 4 on Sheet 2 and want Sheet 1 to automatically add the same row and update the value of the cell in column A & B.

Any help is greatly appreciated!

John



Hi all,

I have written some code that when a button is selected will Refresh a Data Query Table and all of it's information. However, I seem to be getting issues, can some one point me in the right direction with the code?

Code:

 
.Sheets("Sheet 1").ListObject.QueryTable.Refresh BackgroundQuery:=False


When I recorded myslef doing this process it looked like this:

Code:

 
Sheets("Sheet 1").Select
Range("D70872").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False


Many thanks!


I have a requirement to change the panes in excel.
My excel file will contain more than one tab.
I have to free the first two lines in all the tabs except the first one.
I tried the following code.

Code:

xlsobj.Worksheets(1).Rows("1:2").Select
ActiveWindow.FreezePanes = True


But in this I have to change the Active Worksheets in VBA, Which I want to avoid.
Is there any method to achieve this without selecting the cells.
Something like
Code:

xlsobj.Worksheets(1).Rows("1:2").FreezePanes = True





I have a very simple pivot table that I am trying to use as a data source for a VLOOKUP request. Unfortunately, every time I do it I get the result #NA. I have tried recreating the results from the pivot table in the worksheet next to it, and am able to use VLOOKUP on them successfully, but as soon as I point the VLOOKUP array to the pivot table, I get the #NA result

Can VLOOKUP be used on pivot tables? If so, is there any special syntax I need to use?

Thanks


I would like the contents of a selected active cell to be displayed in a certain other cell, say R4. When another cell is selected and active, that cell's contents should be displayed.

I have an array of 9 columns and 20 rows with equity symbols and conditionally formatted to show the severity of price movement in either direction.

Instead of typing in the value of whatever cell a trader is looking at I would like for them to just be able to click on the cell and have that symbol copied into R4 so some detailed information can be displayed for them.

Can't find a thing about this on any forum or msdn.

Hope I'm explaining this at least somewhat clearly.

Thanks.


I'm trying to open a pdf file from within excel vba. I have tried using the followhyperlink method but adobe acrobat opens very briefly then immediately closes Code:

Sub OpenPDF()
'Dim pdf As String
 On Error Resume Next
 'pdf file to open
 pdf = "K:\PDF\mypdf.pdf"
 'open the pdf file
 ActiveWorkbook.FollowHyperlink pdf
End Sub


So then I tried to create an instance of acrobat by setting a reference to the acrobat object but I can't get this to work either!

The code I'm using is Code:

Sub OpenPDF()
 Dim pdf As AcroPDDoc
 Dim strPDF As String
 Set pdf = CreateObject("AcroExch.PDDoc")
 
 'pdf file to open
 strPDF = "K:\PDF\mypdf.pdf"
 'open the pdf file
 pdf.Open strPDF
End Sub


Any ideas what could be wrong with either approach?

Thank-you


I want to Sum Column "H" starting form "H2" all the way down (rows may vary)
Then Paste My answer in "AM1"
This is what I have so far and for some reason is not working.
Code:

myRange = ActiveSheet.Range("H2", Range("H2").End(xlDown))
Range("AM1") = WorksheetFunction.Sum(myRange)


Any Ideas?


Hi,

I'd like help in creating a macro that deletes an entire row that has emtpy cells in col B, C & D in the same row.
So for example if I have empty cells in b3,c3 & d3 I'd like the row deleted.

I've used the code below for just column B but I need to include column C & D as well. I tried putting Columns("B:D") but it deletes everything.

Code:

Sub Step4()
 On Error Resume Next
 Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub


I'm sure it's something simple... like me !

Any help much appreciated

Wrightie



Related pages


unhiding columns in excelreplace function in vbaexcel lookup and replacestacked bar chart excel 2010task assignment template excelprofit margin formula in excelucl calculatornested functions in excelclearcontents vbacompute payback periodpaypal fee calulatorfifo formulahow to create pareto chart in excelexcel macro open workbookone lakh twenty thousand rupeesdevelopers tab in exceldeveloper mode excel 2010access vba error 91how to find frequency distribution in excelexcel embed functionthousand to million converterexcel countif greater than 0how to change number format to millions in excellock formula in excelexcel replace shortcutpc miler online freesecond derivative calculatorgini index formulainterior color vbaexcel average shortcuthow do i unhide a column in excelexcel calculating time differenceformula of irregular polygonhow to generate random numbers in excel 2010cumulative addition in excelextrapolate formulahyperlink macro excelexcel vba copy worksheethow to calculate repeatabilityworking days excel formulastem and leaf plot templateunhide a row in excelexcel vba msgbox inputrefresh pivot tablessigma symbol in excelvba application waitapplication.cutcopymodeinsert radio button in excelexcel millisecond1004 error in vbavb subscript out of rangehot key to pasteexcel password protect cellsunconcatenate excelcalculate my timecardexcel color cell based on valuearrow keys not working properlysumif using date rangeexcel remove letters from celladd leading zero excelformula for meters to feethow to group sheets in excelfarkle dice game score sheetsexcel vba udfexcel formula does not equalweb scraping excelexcel formula roundusing vlookup to compare two listsattrition formula ytdhow do you calculate percentages in excelexcel opcexcel cells.findhow to put names in alphabetical order in excelxcelswimming