Page 1 of 2 12 LastLast
Results 1 to 15 of 21

Thread: Help with Code

  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Help with Code

    Loungers,

    I'm trying to adapt some code from another spreadsheet (that I didn’t create) into the sample attached.

    I need the code to look in the "2.Audit Elements" tab and down column z staring at cell z12 and where there is an "A" put the corresponding number in column AA into "3.Action Summary" tab starting at D12.

    When I run the macro it gets to "myend_addy = ActiveCell.Offset(0, -6).Address" and fails.

    Any help/thoughts would be greatly appreciated. I hope that makes sense

    Cheers

    Forgot to mention that the code is in the attachment file (module) - any help or suggestions would be great. I think that there just needs to be some small adjustments made to the code to reflected the design/columns of the attachment speadsheet
    Attached Files Attached Files
    Last edited by verada; 2012-07-31 at 20:09. Reason: extgra information

  2. #2
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Anyone ?

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    When the active cell is in column D, you can't go 6 columns to the left...

    Try this:
    Code:
    Sub Action_register()    Dim wksAudit         As Worksheet
        Dim wksAction        As Worksheet
        Dim lngLastRow       As Long
        Dim lngRow           As Long
    
    
        Set wksAudit = Sheets("2.audit element")
        Set wksAction = Sheets("3.Action Summary")
        Application.ScreenUpdating = False
        wksAction.Range("d12:d500").ClearContents
        With wksAudit
            .Range("z12:z500").Replace What:="a", Replacement:="A", LookAt:=xlPart, _
                                       SearchOrder:=xlByRows, MatchCase:=False
            lngLastRow = .Cells(.Rows.Count, "Z").End(xlUp).Row
    
    
            For lngRow = 13 To lngLastRow
    
    
                If .Cells(lngRow, "Z").Value = "A" Then
                    wksAction.Cells(Rows.Count, "D").End(xlUp).Offset(1).Value = .Cells(lngRow, "AA").Value
                End If
            Next lngRow
    
    
        End With
        wksAction.Rows("12:500").EntireRow.AutoFit
        Application.ScreenUpdating = True
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    New Lounger
    Join Date
    Dec 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Rory - thanks very much for your help. Had a username problem so have changed this in the short term.

    I pasted the code into the working spreadsheet. When I run it I get an error "wksAction.Rows("12:500").EntireRow.AutoFit"

    Also is there away to unprotect the sheet when the code runs as i notice that when the sheet is protected the code does nothing

    Any thoughts/solutions would be very much appreciated?
    Last edited by 1awb520; 2012-08-02 at 00:29.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Sure:
    Code:
    Sub Action_register()    Dim wksAudit         As Worksheet    Dim wksAction        As Worksheet    Dim lngLastRow       As Long    Dim lngRow           As Long    Set wksAudit = Sheets("2.audit element")    Set wksAction = Sheets("3.Action Summary")    Application.ScreenUpdating = FalseWksaction.unprotect password:="password"    wksAction.Range("d12:d500").ClearContents    With wksAudit        .Range("z12:z500").Replace What:="a", Replacement:="A", LookAt:=xlPart, _                                   SearchOrder:=xlByRows, MatchCase:=False        lngLastRow = .Cells(.Rows.Count, "Z").End(xlUp).Row        For lngRow = 13 To lngLastRow            If .Cells(lngRow, "Z").Value = "A" Then                wksAction.Cells(Rows.Count, "D").End(xlUp).Offset(1).Value = .Cells(lngRow, "AA").Value            End If        Next lngRow    End With    wksAction.Range("12:500").EntireRow.AutoFitWksaction.protect password:="password"    Application.ScreenUpdating = TrueEnd Sub
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    New Lounger
    Join Date
    Dec 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the quick response, Rory - Does this code replace or supplement the original code?

    Regards

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Replace it. It seems to have got garbled while posting - can you make sense of it (I'm on a phone at the moment)
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    New Lounger
    Join Date
    Dec 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Rory - I think I have un-garbled it. I replaced the word "password" with the actual passord set for the sheet (I assume that is correct), put is does not appear to be re-protecting after running the code, also I'm still getting an error at "wksAction.Range("12:500").EntireRow.AutoFit"

    Any other thoughs - I appreciate your assistance
    Last edited by 1awb520; 2012-08-02 at 02:31.

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You mean the sheet is being left unprotected? (of course if the code errors and stops before the Protect line, that's not surprising)
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    New Lounger
    Join Date
    Dec 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Rory - sorry, you are corrct is it leaving the sheet unprotected and as you say it must be as a result of the code error before the protect line , any idea what is causing the code error and the fix for it?

    Regards

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    What is the actual error message? The syntax itself is fine.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    New Lounger
    Join Date
    Dec 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is the line that fails
    wksAction.Range("12:500").EntireRow.AutoFit

    A message box appears with a Run-time error '1004' with an End or Debug option

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    And the Action Summary sheet is definitely unprotected at that point?
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    New Lounger
    Join Date
    Dec 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes - definitely unprotected. I did a retest just to double check - protected the sheet with the allocated password - selected cells all protected (locked and hidden - in the format cell - Protection). Run the code, values go in as they should in column D commencing at cell D12. Sheet is now unprotected.

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Can you post the workbook? (don't need any data in it)
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

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