Excel VBA, Loop

Question

I'm pretty new to VBA and cannot get a loop work.

Column J contains certain ID numbers. For each ID number I have several rows displayed in my database. I sorted my database on column J (ascending order). I would like to filter for all the values in columns J and copy all the rows for each ID number into a new sheet what I want to rename as the ID number. Here is the macro but the loop doesn't work (Run-time error 13, Type mismatch, error in the following code row: For i = 45 To LastEmpNo)

Any help would be welcomed. Thanks

Sub Macro3()

Dim MyDataFirstCell
Dim MyDataLastCell
Dim EmpNoStart
Dim EmpNoFinish
Dim i As Integer

Range("A1").Select
MyDataFirstCell = ActiveCell.Address
Selection.End(xlDown).Select
Selection.End(xlToRight).Select
MyDataLastCell = ActiveCell.Address 

Range("J2").Select
EmpNoStart = ActiveCell.Address
Selection.End(xlDown).Select
EmpNoFinish = ActiveCell.Address

Range(EmpNoStart & ":" & EmpNoFinish).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns _
    ("K:K"), Unique:=True

Dim FirstEmpNo
Dim LastEmpNo

Range("K2").Select
FirstEmpNo = ActiveCell.Address
Selection.End(xlDown).Select
LastEmpNo = ActiveCell.Address

For i = FirstEmpNo To LastEmpNo

    Range("J1").Select
    Selection.AutoFilter
    ActiveSheet.Range(MyDataFirstCell & ":" & MyDataLastCell).AutoFilter Field:=10, Criteria1:=i
    Rows("1:1").Select
    Range("C1").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    Columns("B:B").EntireColumn.AutoFit
    Cells.Select
    Cells.EntireColumn.AutoFit
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = i

Next i


End Sub

bpdohall · Accepted Answer · 2015-02-24 14:25:19Z

1

You cannot iterate from one string value to another. The "For" loop is expecting two numeric values.

?activecell.Address
$C$9
?activecell.Row
 9

Therefore:

FirstEmpNo = ActiveCell.Row
Selection.End(xlDown).Select
LastEmpNo = ActiveCell.Row

edited Feb 24, 2015 at 14:25

answered Feb 24, 2015 at 14:16

bpdohall

1,0516 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA, Loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related