5 Replies - 3498 Views - Last Post: 16 September 2009 - 06:34 PM Rate Topic: -----

#1 guyfromri  Icon User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 817
  • Joined: 16-September 09

VBA sorting in excel with variable numbers

Posted 16 September 2009 - 11:10 AM

I am writing a code that inserts a row, adds a name, adds a code for the name and sorts the cells. In my code, I need to figure out how to add one row the ammount of cells to sort. I can't sort the whole page because there are three different lists of names, broken up by state, on one page.

the code i have now works fine but i dont know the variable code to add...heres my code now.

Sub NewMassDriverTest()

	Dim MyInput As String
	Dim mycode As String
	
		 MyInput = InputBox("THIS WILL ADD A MASS DRIVER! MAKE SURE YOU WANT A MASS DRIVER", _
		"DRIVER NAME", "Enter Drivers Name FIRST,LAST here")
	'myinput is the name field for the driver
		If MyInput = "Enter your input text HERE" Or _
		MyInput = "" Then
			 Exit Sub
			End If
	'enter name in field
	'the reason i use e11 is because it's centrally located in the field of names
		If MyInput > "0" Then
		Sheets("dashboard").Select
		Range("E11") = MyInput
	'mycode is the "short name" for the driver
		mycode = InputBox("Enter Driver Code", "Driver Code", "Enter Code Here....Duh. LOL")
		If mycode = "Enter Code Here." Or mycode = "" Then
				Exit Sub
			End If
		
		If mycode > "0" Then
		Sheets("dashboard").Select
		Range("F11") = mycode
		End If
		
			
Range("E7:K17").Select
	Selection.sort Key1:=Range("E7"), Order1:=xlAscending, Header:=xlGuess, _
		OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
		DataOption1:=xlSortNormal
			end if 
end if

	End Sub


Is This A Good Question/Topic? 0
  • +

Replies To: VBA sorting in excel with variable numbers

#2 MajorWalrus  Icon User is offline

  • D.I.C Head

Reputation: 10
  • View blog
  • Posts: 115
  • Joined: 22-April 09

Re: VBA sorting in excel with variable numbers

Posted 16 September 2009 - 03:10 PM

Not quite sure what you mean by "the variable code", but as for sorting, couldn't you doz;

Selection.sort Key1:=Range("E7:K17") 'or whatever range



Attention Mod: Should this thread be moved? He's using VBA, not VB6.
Was This Post Helpful? 0
  • +
  • -

#3 guyfromri  Icon User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 817
  • Joined: 16-September 09

Re: VBA sorting in excel with variable numbers

Posted 16 September 2009 - 03:42 PM

yes, thats the sort code i have...i don't quite understand how the keys work but i figured it out. the problem is this:
i have three sets of info - e7-e17, e20-30 and e32-e42. each is a list of names of employees in different states. when i run one macro it adds a row to the first set and then i have to re-sort. Problem is, now the first set turned into e7-e18...thats what i meant by variable...i might be phrasing it wrong.

thanks in advance for the help though

This post has been edited by guyfromri: 16 September 2009 - 03:44 PM

Was This Post Helpful? 0
  • +
  • -

#4 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: VBA sorting in excel with variable numbers

Posted 16 September 2009 - 06:01 PM

use the select function for the particular area you want sort
now sort it
that's all
Was This Post Helpful? 0
  • +
  • -

#5 guyfromri  Icon User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 817
  • Joined: 16-September 09

Re: VBA sorting in excel with variable numbers

Posted 16 September 2009 - 06:17 PM

Select as in vba or ad in select cells in excel? How would it know to add the new cell to the cells i'm sorting?
Was This Post Helpful? 0
  • +
  • -

#6 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: VBA sorting in excel with variable numbers

Posted 16 September 2009 - 06:34 PM

	ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:A17") _
		, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
	ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C1:C17") _
		, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
	With ActiveWorkbook.Worksheets("Sheet1").Sort
		.SetRange Range("A1:I17")
		.Header = xlGuess
		.MatchCase = False
		.Orientation = xlTopToBottom
		.SortMethod = xlPinYin
		.Apply
	End With



add the employee state first then by name
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1