LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8
Default Question regarding returning a variable value to a cell

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
GetOpenFilename not returning a usable variable Lilivati Excel Discussion (Misc queries) 2 June 26th 06 09:02 PM
[Newbie question] Returning an array from a function dorutzu Excel Programming 4 December 8th 04 10:45 PM
Returning a variable from a sub melisfreed[_7_] Excel Programming 0 November 17th 04 08:08 PM
Returning a variable from a sub melisfreed[_6_] Excel Programming 1 November 17th 04 07:39 PM
Newbie Question: Returning the value from a cell on another sheet SmilingPolitely[_2_] Excel Programming 4 July 14th 04 01:23 PM


All times are GMT +1. The time now is 02:21 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"