ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Question regarding returning a variable value to a cell (https://www.excelbanter.com/excel-programming/388874-question-regarding-returning-variable-value-cell.html)

Frank[_27_]
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

PCLIVE
Question regarding returning a variable value to a cell
 


Range("E22").value = OneCount
Range("E23").value = CinCount
Range("E24").value = VerCount
Range("E25").value = SprCount

Regards,
Paul
"Frank" wrote in message
oups.com...
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

Frank[_27_]
Question regarding returning a variable value to a cell
 
Close this topic. I found the answer in an earlier post HE

http://groups.google.com/group/micro...d05c4f71d98654

THANKS ALL!!















On May 7, 1:25 pm, Frank wrote:
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


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

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com