Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Another newbie question if you please:
In the script below, I wish to return the value of a variable to a specific cell on a specific worksheet. I've been unable to find the correct function. Please let me know how to make this (likely quite simple) paste for variables: OneCount SprCount VerCount CinCount These are simple integer values (currently) here is the entire vbscript: Sub Vendor_Sort() ' ' Vendor_Sort Macro ' ' Keyboard Shortcut: Ctrl+Shift+V ' THIS SECTION UPDATES TOTALS IN EACH CATEGORY ' Purpose of this section is to delete old data from resultant worksheets Sheets("Personal").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Corporate").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Disconnect").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp ' Purpose of this section is copying of data to appropriate resultant worksheets Sheets("Master").Select For Each Cell In Range("F1", Cells(Cells(Rows.Count, "F").End(xlUp).Row, "F")) Select Case Cell Case "P" Cell.EntireRow.Copy Sheets("Personal").Cells(Rows.Count, 1).End(xlUp)(2) Case "C" Cell.EntireRow.Copy Sheets("Corporate").Cells(Rows.Count, 1).End(xlUp)(2) Case "D" Cell.EntireRow.Copy Sheets("Disconnect").Cells(Rows.Count, 1).End(xlUp)(2) End Select Next ' THIS SECTION IS THE SORTS by VENDOR Sheets("CellOne").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Cingular").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Sprint").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp Sheets("Verizon").Select Rows("2:65535").Select Selection.Delete Shift:=xlUp ' Purpose of this section is copying of data to appropriate resultant worksheets Sheets("Corporate").Select For Each Cell In Range("N1", Cells(Cells(Rows.Count, "N").End(xlUp).Row, "N")) Select Case Cell Case "Cellularone" Cell.EntireRow.Copy Sheets("CellOne").Cells(Rows.Count, 1).End(xlUp)(2) Case "Verizon" Cell.EntireRow.Copy Sheets("Verizon").Cells(Rows.Count, 1).End(xlUp)(2) Case "Cingular" Cell.EntireRow.Copy Sheets("Cingular").Cells(Rows.Count, 1).End(xlUp)(2) Case "Sprint" Cell.EntireRow.Copy Sheets("Sprint").Cells(Rows.Count, 1).End(xlUp)(2) End Select Next ' VENDOR SORT BY CONTRACT DATE Dim OneCount As Integer Dim VerCount As Integer Dim CinCount As Integer Dim SprCount As Integer ' Cell One Sort OneCount = 0 Sheets("CellOne").Select Cells.Select Selection.Sort Key1:=Range("AC2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom For Each Cell In Range("AC1", Cells(Cells(Rows.Count, "AC").End(xlUp).Row, "AC")) Select Case Cell Case "No" Case "??" Case Is < Now Cell.EntireRow.Copy Sheets("CellOne Totals").Cells(Rows.Count, 1).End(xlUp)(2) End Select OneCount = OneCount + 1 Next Range("A2").Select ' Verizon Sort VerCount = 0 Sheets("Verizon").Select Cells.Select Selection.Sort Key1:=Range("AC2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom For Each Cell In Range("AC1", Cells(Cells(Rows.Count, "AC").End(xlUp).Row, "AC")) Select Case Cell Case "No" Case "??" Case Is < Now Cell.EntireRow.Copy Sheets("Verizon Totals").Cells(Rows.Count, 1).End(xlUp)(2) End Select VerCount = VerCount + 1 Next Range("A2").Select ' Cingular Sort CinCount = 0 Sheets("Cingular").Select Cells.Select Selection.Sort Key1:=Range("AC2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom For Each Cell In Range("AC1", Cells(Cells(Rows.Count, "AC").End(xlUp).Row, "AC")) Select Case Cell Case "No" Case "??" Case Is < Now Cell.EntireRow.Copy Sheets("Cingular Totals").Cells(Rows.Count, 1).End(xlUp)(2) End Select CinCount = CinCount + 1 Next Range("A2").Select ' Sprint Sort SprCount = 0 Sheets("Sprint").Select Cells.Select Selection.Sort Key1:=Range("AC2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom For Each Cell In Range("AC1", Cells(Cells(Rows.Count, "AC").End(xlUp).Row, "AC")) Select Case Cell Case "No" Case "??" Case Is < Now Cell.EntireRow.Copy Sheets("Sprint Totals").Cells(Rows.Count, 1).End(xlUp)(2) End Select SprCount = SprCount + 1 Next Range("A2").Select ' Purpose of this section is to return to the main page, insert totals and save changes ActiveWorkbook.Save Sheets("READ ME").Select Range("A2").Select ' HERE I WOULD LIKE TO ASSIGN THE VALUES AS FOLLOWS: ' Sheets("READ ME").Select ' THE FINAL OUTPUT SHEET ' E22 = OneCount ' E23 = CinCount ' E24 = VerCount ' E25 = SprCount ' I'll be carrying many other values and performing other calculations later; but a little at a time for me! End Sub THANKS FOR ANY AND ALL ASSISTANCE. I KNOW MY PROGRAMMING IS STILL QUITE CLUMSY AND I CAN LIKELY CONSOLIDATE |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
GetOpenFilename not returning a usable variable | Excel Discussion (Misc queries) | |||
[Newbie question] Returning an array from a function | Excel Programming | |||
Returning a variable from a sub | Excel Programming | |||
Returning a variable from a sub | Excel Programming | |||
Newbie Question: Returning the value from a cell on another sheet | Excel Programming |