Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good day Loungers,

    I am having some difficulty with a macro I am writing. The idea is that I can look at a list of IP addresses and compare them to a list of Good IP addresses, Bad IP addresses and Ugly IP addresses.

    I have written a If statement with lots of Ands and Ors to determine the proper location to copy the row to, but when I try to execute I get an Object Required error and can not for the life of me figure out what is missing.

    If anyone has a better method of doing this, I am open to suggestions.

    I have attached a stripped down sample and here is the code I am using
    [codebox]Sub Clock_Log_Cleanup()
    '
    ' Clock_Log_Cleanup Macro
    '

    '
    Dim m As Long
    Dim r As Long
    Dim n As Long
    Dim g As Long
    Dim b As Long
    Dim u As Long
    Dim wshGood As Worksheet
    Dim wshBad As Worksheet
    Dim wshUgly As Worksheet
    Dim wshSource As Worksheet

    ' change labels

    Range("A1").Select
    ActiveCell.FormulaR1C1 = "EMPLID"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Rec Num"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Area"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Task"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Clock Timestamp"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Action Code"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "User EMPLID"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "User Name"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "Action Time"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "Timezone"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "Run Date"
    Cells.Select
    Cells.EntireColumn.AutoFit

    ' delete any tk-user changes and supervisor changes

    r = Range("A" & Rows.Count).End(xlUp).Row

    For n = r To 2 Step -1

    If Cells(n, 9) = "tk-user" Then
    Cells(n, 1).EntireRow.Delete
    Else
    If Cells(n, 1) <> Cells(n, 9) Then
    Cells(n, 1).EntireRow.Delete
    End If
    End If
    Next n

    'Divide the IP to 4 colums to use in matching

    Range("I1").Select
    Selection.EntireColumn.Insert
    Selection.EntireColumn.Insert
    Selection.EntireColumn.Insert
    Selection.EntireColumn.Insert
    Columns("H:H").Select
    Selection.Copy
    Range("I1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :=".", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
    TrailingMinusNumbers:=True
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "IP1"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "IP2"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "IP3"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "IP4"

    ' add tabs for Good, Bad and Ugly

    Set wshGood = Worksheets.Add(before:=Worksheets(Worksheets.Count ))
    wshGood.Name = "RS"
    Set wshBad = Worksheets.Add(before:=Worksheets(Worksheets.Count ))
    wshBad.Name = "Other IU"
    Set wshUgly = Worksheets.Add(before:=Worksheets(Worksheets.Count ))
    wshUgly.Name = "Outside IU"
    Set wshSource = Worksheets("IP Data")


    ' move rows to appropriate tab based on IP address

    m = wshSource.Range("A" & Row.Count).End(xlUp).Row

    g = wshGood.Range("A" & Row.Count).End(xlUp).Row

    b = wshBad.Range("A" & Row.Count).End(xlUp).Row

    u = wshUgly.Range("A" & Row.Count).End(xlUp).Row

    For r = m To 2 Step -1


    If Cells(m, 9) = 129 And Cells(m, 10) = 79 And _
    (Cells(m, 11) = 45 Or Cells(m, 11) = 64 _
    Or Cells(m, 11) = 6 Or Cells(m, 11) = 177) Then
    Cells(m, 1).EntireRow.Copy _
    Destination:=wshGood.Range("A" & g)
    g = g + 1
    Else
    If Cells(m, 9) = 129 And Cells(m, 10) = 79 And _
    (Cells(m, 11) <> 45 Or Cells(m, 11) <> 64 _
    Or Cells(m, 11) <> 6 Or Cells(m, 11) <> 177) Then
    Cells(m, 1).EntireRow.Copy _
    Destination:=wshBad.Range("A" &
    b = b + 1
    Else
    If Cells(m, 9) <> 129 Then
    Cells(m, 1).EntireRow.Copy _
    Destination:=wshUgly.Range("A" & u)
    u = u + 1

    End If
    End If
    End If


    Next r


    End Sub[/codebox]

    Thanks is advance for any assistance you can provide.

    Greg
    Attached Files Attached Files
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    Lounger
    Join Date
    Mar 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This code is clearing your worksheet
    Code:
    ' delete any tk-user changes and supervisor changes
    
    	r = Range("A" & Rows.Count).End(xlUp).Row
    	
    	For n = r To 2 Step -1
    	
    		If Cells(n, 9) = "tk-user" Then
    			Cells(n, 1).EntireRow.Delete
    		Else
    			If Cells(n, 1) <> Cells(n, 9) Then
    			Cells(n, 1).EntireRow.Delete
    			End If
    		End If
    	Next n

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts
    With my trials, that code only cleared about 100 rows of the original 3800 I had in the table. I cut the number of rows for my post to the lounge. The error I get is on the line:
    m = wshSource.Range("A" & Row.Count).End(xlUp).Row

    Greg
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  4. #4
    Lounger
    Join Date
    Mar 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Looking at your sample, references are all out by one column

    also:
    Rows instead of Row
    Code:
    m = wshSource.Range("A" & Rows.Count).End(xlUp).Row
    Activate IP sheet
    Code:
      wshSource.Activate
    	For r = m To 2 Step -1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •