Excel, make Variable = Cells(RowNum,2) work or equivalent

[renigar]

I'm trying to take info from a list of employees on sheet 2 and fill in various cells on a timecard template on sheet 1. I want to use a SpinButton to scroll through an employee list. I'm trying to make my variables equal the value of a cell in a column but the row number changes with each press of the SpinButton. Making the variables work is my problem. See sample code.

Private Sub SpinButton2_Change()

' Create variables to hold employee info.
Dim Dept As String
Dim ENbr As Integer
Dim EName As String
Dim Acct1 As Integer
Dim Acct2 As Integer
Dim Acct3 As Integer
Dim Acct4 As Integer
Dim Acct5 As Integer
Dim Stdby As Integer

'Change active sheet to Employee List.
Sheets("Employee List").Select

' Select column D (the list of employee names.
ActiveSheet.Range("D1").Select

' Go to the last cell with data in the selected column.
Selection.End(xlDown).Select

' Create variable, make it equal to the selected cell's row number.
LastRow = (ActiveCell.Row)

If SpinButton2 > LastRow Then SpinButton2 = 1

' Create variable for row number of employee info.
' Identifies desired row number.
RowNum = SpinButton2 + 1

'Fill variables with info. I can't figure this part out.
Dept = Cells(RowNum, 2)
ENbr = Cells(RowNum, 3)
EName = Cells(RowNum, 4)
Acct1 = Cells(RowNum, 5)
Acct2 = Cells(RowNum, 6)
Acct3 = Cells(RowNum, 7)
Acct4 = Cells(RowNum, 8)
Acct5 = Cells(RowNum, 9)
Stdby = Cells(RowNum, 10)

'Change active sheet to TimeCard.
Sheets("TimeCard").Select

If SpinButton2 = 1 Then
Range("M1").Select
ActiveCell.FormulaR1C1 = Dept
Range("D3").Select
ActiveCell.FormulaR1C1 = ENbr
Range("M3").Select
ActiveCell.FormulaR1C1 = EName
Range("C5").Select
ActiveCell.FormulaR1C1 = Acct1
Range("I5").Select
ActiveCell.FormulaR1C1 = Acct2
Range("O5").Select
ActiveCell.FormulaR1C1 = Acct3
Range("T5").Select
ActiveCell.FormulaR1C1 = Acct4
Range("Y5").Select
ActiveCell.FormulaR1C1 = Acct5
Range("AD5").Select
ActiveCell.FormulaR1C1 = Stdby

End If
End Sub

This is how I want it to work but not knowing enough maybe I have the wrong approach. Any help will be greatly appreciated.

 

[GVF]

Your correct, it's the wrong approach. I suggest you dump the Spinbutton and insert a regular Commandbutton from the Controls Toolbar.
In design view (click on the draftsman triangle on the toolbar) right click on the commandbutton and select properties. In the properties window change the caption to "Print Time Cards" or something.
Provided the Name of the button is "CommandButton1" (view it in the properties window) the following code will put employee info into your time card and print it, then move to the next employee.

Private Sub CommandButton1_Click()

Dim Dept As String
Dim ENbr As Integer
Dim EName As String
Dim Acct1 As Integer
Dim Acct2 As Integer
Dim Acct3 As Integer
Dim Acct4 As Integer
Dim Acct5 As Integer
Dim Stdby As Integer
Dim Counter As Integer
LastRow as Integer
FirstRow as integer

Sheets("Employee List").Activate

Columns("D").Select
LastRow = ActiveSheet.UsedRange.Rows.Count
'The FirstRow will be the first cell in column D with an employee name in it.
FirstRow = 1
For Counter = FirstRow To LastRow Step 1
Dept = Cells(Counter, 2).Value
ENbr = Cells(Counter, 3).Value
EName = Cells(Counter, 4).Value
Acct1 = Cells(Counter, 5).Value
Acct2 = Cells(Counter, 6).Value
Acct3 = Cells(Counter, 7).Value
Acct4 = Cells(Counter, 8).Value
Acct5 = Cells(Counter, 9).Value
Stdby = Cells(Counter, 10).Value

With Sheets("TimeCard")
    .Range("M1").Value = Dept
    .Range("D3").Value = ENbr
    .Range("M3").Value = EName
    .Range("C5").Value = Acct1
    .Range("I5").Value = Acct2
    .Range("O5").Value = Acct3
    .Range("T5").Value = Acct4
    .Range("Y5").Value = Acct5
    .Range("AD5").Value = Stdby
End With
'you will want to print the timecard since as the code runs it will
'overwrite the cells with the next employee.
ActiveSheet.PrintOut
Next Counter

'Lastly clear the TimeCard sheet.
With Sheets("TimeCard")
    .Range("M1").Value = ""
    .Range("D3").Value = ""
    .Range("M3").Value = ""
    .Range("C5").Value = ""
    .Range("I5").Value = ""
    .Range("O5").Value = ""
    .Range("T5").Value = ""
    .Range("Y5").Value = ""
    .Range("AD5").Value = ""
End With

End Sub

Cells, buttons, sheets, etc. are objects. Objects have properties (.Value, .Min, .ForeColor etc.). Check the help file for properties of the various objects you use.

 

[renigar]

Thank you for your reply GVF. It still has a couple of problems. I've found some answers before finishing this reply.

Columns("D").Select Application-defined or object-defined error
Fixed with ActiveSheet.Columns("D").Select

LastRow = ActiveSheet.UsedRange.Rows.Count I have 92 rows this returns 101. I had used this line originally then switched to the method I posted because it returned 92. Don't know what's going here. I guess I'll revert to my original method.

The variable section is still not picking up the values.
Dept = Cells(Counter, 2).Value. When I step through the values are "".
Fixed with Dept = Worksheets("Employee List").Cells(Counter, 2).Value

I don't write macros often enough to keep this stuff in my head, but I'm glad there are resources like Tek-Tips and the people posting that I can refer to. Thank you so much.

 

[SkipVought]

Used Range can return undesired results. Try this (not knowing exactly the nature of your UsedRange...

LastRow = ActiveSheet.UsedRange.CurrentRegion.Rows.Count

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Private Sub SpinButton2_Change()
    Dim LastRow As Long, RowNum As Long
    
    'Change active sheet to Employee List.
'[b]SELECT is neither necessary nor desirable[/b]
    With Sheets("Employee List")
    
        ' Create variable, make it equal to the selected cell's row number.
        LastRow = Range(.Range("D1"), Range("D1").End(xlDown)).Row
        
        If SpinButton2 > LastRow Then SpinButton2 = 1
        
        RowNum = SpinButton2 + 1
        
        If SpinButton2 = 1 Then
            Sheets("TimeCard").Range("M1").Value = .Cells(RowNum, 2)
            Sheets("TimeCard").Range("D3").Value = .Cells(RowNum, 3)
            Sheets("TimeCard").Range("M3").Value = .Cells(RowNum, 4)
            Sheets("TimeCard").Range("C5").Value = .Cells(RowNum, 5)
            Sheets("TimeCard").Range("I5").Value = .Cells(RowNum, 6)
            Sheets("TimeCard").Range("O5").Value = .Cells(RowNum, 7)
            Sheets("TimeCard").Range("T5").Value = .Cells(RowNum, 8)
            Sheets("TimeCard").Range("Y5").Value = .Cells(RowNum, 9)
            Sheets("TimeCard").Range("AD5").Value = .Cells(RowNum, 10)
        End If
     End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[renigar]

Thanks Skip,
I'll give this a try.

 

[SkipVought]

I spotted a problem, sorry

                                  [red][b]v[/b][/red]
    LastRow = Range(.Range("D1"), [red][b].[/b][/red]Range("D1").End(xlDown)).Row
                                  [red][b]^[/b][/red]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}