Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Code doesnt work after being imported into a new excel
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2019
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Code doesnt work after being imported into a new excel

    This are the original codes.

    Module 1
    Code:
    'Sub Test()''
    '' Test Macro
    ''
    '' Keyboard Shortcut: Ctrl+f
    ''
    '    Sheets("Engagement Log").Range("Table1[#All]").AdvancedFilter Action:= _
    '        xlFilterCopy, CriteriaRange:=Range("Sheet1!Criteria"), CopyToRange:=Range( _
    '        "A6:H7"), Unique:=False
    'End Sub

    Module 2
    Code:
    Sub Macro2()'
    ' Macro2 Macro
    '
    
    
    '
        Range("Table1[[#Headers],[SURVEY 1 DATE]]").Select
        Windows("Book1.xlsx").Activate
        Application.WindowState = xlNormal
        Application.WindowState = xlNormal
        Windows("20190322.xlsm").Activate
        Columns("B:B").Select
        Selection.Copy
        Range("Table1[[#Headers],[COMPANY NAME]]").Select
        Sheets.Add After:=ActiveSheet
        Columns("A:A").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Columns("A:A").EntireColumn.AutoFit
        Range("A2").Select
        Range(Selection, Selection.End(xlDown)).Select
        Application.CutCopyMode = False
        ActiveSheet.Range("$A$2:$A$1602").RemoveDuplicates Columns:=1, Header:=xlNo
        Sheets("Sheet2").Select
        ActiveWindow.SelectedSheets.Delete
        Sheets("Engagement Log").Select
        Range("AE2").Select
        Sheets("Sheet1").Select
    End Sub

    Module 3
    Code:
    Sub dateCheck()    Dim sht, sht2 As Worksheet
        Dim xStartDate As Date
        Dim xEndDate As Date
        Dim xDate As Date
        
        Set sht = ThisWorkbook.Worksheets("Engagement Log")
        Set sht2 = ThisWorkbook.Worksheets("Result")
        
        
        a = sht.Cells(Rows.Count, 2).End(xlUp).Row
        b = sht.Cells(1, Columns.Count).End(xlToLeft).Column
        xcol = Replace(ActiveSheet.Cells(1, b).Address(True, False), "$1", "")
        Rng = sht.Range("A1:" & xcol & 1)
         
        
        
        a2 = sht2.Cells(Rows.Count, 2).End(xlUp).Row
        If a2 > 5 Then sht2.Range("A6:A" & a2).EntireRow.Delete
        a2 = sht2.Cells(Rows.Count, 2).End(xlUp).Row
        j = a2
        b2 = sht2.Cells(5, Columns.Count).End(xlToLeft).Column
        xcol2 = Replace(ActiveSheet.Cells(1, b2).Address(True, False), "$1", "")
        Rng2 = sht2.Range("A5:" & xcol2 & 5)
        
        
        
        xSurveyCount = sht2.Range("H1").Value
        xStartDate = sht2.Range("B1").Value
        xEndDate = sht2.Range("B2").Value
        
        Set RowRange = sht.Range("B2:B" & a)
        
        For Each rowvalue In RowRange
            xrow = rowvalue.Row
            
            xCert = sht.Cells(xrow, 1).Value
            xUEN = sht.Cells(xrow, 2).Value
            xCName = sht.Cells(xrow, 3).Value
    '        xSProject = sht.Cells(xrow, 4).Value
    '        xSector = sht.Cells(xrow, 8).Value
            Z = 0
            For i = 2 To xSurveyCount
                d = Application.WorksheetFunction.Match("SURVEY " & i & " DATE", Rng, 0)
                xDate = sht.Cells(xrow, d).Value
                d2 = Application.WorksheetFunction.Match("SURVEY " & i & " DATE", Rng2, 0)
                If xDate >= xStartDate And xDate <= xEndDate Then
    '                d2 = Application.WorksheetFunction.Match("SURVEY " & i & " DATE", Rng2, 0)
                    If xCert <> sht2.Cells(j, 1).Value And xUEN <> xUEN2 And xCName <> sht.Cells(j, 3).Value Then
                      z2 = d2
                      Z = Z + 1
                      j = j + 1
                      sht2.Cells(j, 1).Value = sht.Cells(xrow, 1).Value
                      sht2.Cells(j, 2).Value = sht.Cells(xrow, 3).Value
                      sht2.Cells(j, 3).Value = sht.Cells(xrow, 4).Value
                      sht2.Cells(j, 4).Value = sht.Cells(xrow, 8).Value
                      
                      sht2.Cells(j, d2).Value = sht.Cells(xrow, d).Value
                    Else
                     z2 = d2
                     Z = Z + 1
                     sht2.Cells(j, d2).Value = sht.Cells(xrow, d).Value
                    End If
                End If
            Next
    '        If Z >= 2 Then xZdate = sht2.Cells(j, z2).Value
    '        If Z >= 2 Then xZdate1 = sht2.Cells(j, z2 - 1).Value
    '        If Z >= 2 And xZdate > xZdate1 Then sht2.Cells(j, d2 + 1).Value = sht2.Cells(j, z2).Value - sht2.Cells(j, z2 - 1).Value
            If Z >= 1 Then sht2.Cells(j, d2 + 1).Value = sht2.Cells(j, z2).Value
    
    
            xUEN2 = xUEN
        Next
        MsgBox "Task Completed"
    End Sub
    
    
    Sub ClearResult()
        Dim sht2
        Set sht2 = ThisWorkbook.Worksheets("Result")
       
        
        a2 = sht2.Cells(Rows.Count, 2).End(xlUp).Row
        b2 = sht2.Cells(5, Columns.Count).End(xlToLeft).Column
        'Set RowRange = sht2.Range("A6:A" & a2)
        If a2 > 5 Then sht2.Range("A6:A" & a2).EntireRow.Delete
        
    End Sub
    i copied and pasted the exact same thing into a new excel file (only the excel file name is different, the rest of the tabs name are the same). but it doesnt work and i am brought into the debugger mode and shown this.



    how do i make the codes work in the new excel file too?

  2. #2
    Board Regular Tim_Excel_'s Avatar
    Join Date
    Jul 2016
    Posts
    512
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Code doesnt work after being imported into a new excel

    Think about rewriting the entire code. You are selecting and activating ranges which should be avoided and in your code seems to be without any use.

    Give this page a read, it's very informative!

    For example

    Code:
    'This code below...
    Range("A2:A100").Select
    Selection.Value = 3
    
    
    '...should instead be like
    Range("A2:A100").Value = 3
    Always make a backup of your data before trying out any code

    Before you ask why my code is not working: have you checked if the references are correct? Are you referring to the correct Workbook/-sheet?

    Use [CODE] tags when pasting code in your posts

  3. #3
    New Member
    Join Date
    Mar 2019
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code doesnt work after being imported into a new excel

    Quote Originally Posted by Tim_Excel_ View Post
    Think about rewriting the entire code. You are selecting and activating ranges which should be avoided and in your code seems to be without any use.

    Give this page a read, it's very informative!

    For example

    Code:
    'This code below...
    Range("A2:A100").Select
    Selection.Value = 3
    
    
    '...should instead be like
    Range("A2:A100").Value = 3
    the code was actually written by a freelancer on a duplicate file with dummy data. i am trying to insert the codes into my actual file. so rewriting is not really an option.

    so from the link you shared, i understand that .select is mainly due to marco recorder, and many other articles/ page mentioned that the sheet is not active and this is not a good way to code. but this is way too advance for me. is there any quick and easy fix for me?

  4. #4
    Board Regular Tim_Excel_'s Avatar
    Join Date
    Jul 2016
    Posts
    512
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Code doesnt work after being imported into a new excel

    I should have mentioned it's only module 2 that needs rewriting. Shivers down my spine and that. The code in module 1 will never be executed as it is all commented out.

    What is the error you are getting? I have the suspicion it's a Subscript out of Range error, which would mean you are referring to an object Excel cannot find.
    Always make a backup of your data before trying out any code

    Before you ask why my code is not working: have you checked if the references are correct? Are you referring to the correct Workbook/-sheet?

    Use [CODE] tags when pasting code in your posts

  5. #5
    New Member
    Join Date
    Mar 2019
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code doesnt work after being imported into a new excel

    Quote Originally Posted by Tim_Excel_ View Post
    I should have mentioned it's only module 2 that needs rewriting. Shivers down my spine and that. The code in module 1 will never be executed as it is all commented out.

    What is the error you are getting? I have the suspicion it's a Subscript out of Range error, which would mean you are referring to an object Excel cannot find.
    do you think u can help me to amend module 2 code?

    the error i am getting is "Run-time error 1004. Select method of Range class failed"

  6. #6
    Board Regular Tim_Excel_'s Avatar
    Join Date
    Jul 2016
    Posts
    512
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Code doesnt work after being imported into a new excel

    How about you just delete the whole line selected in yellow, it does nothing.
    Always make a backup of your data before trying out any code

    Before you ask why my code is not working: have you checked if the references are correct? Are you referring to the correct Workbook/-sheet?

    Use [CODE] tags when pasting code in your posts

  7. #7
    New Member
    Join Date
    Mar 2019
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code doesnt work after being imported into a new excel

    Quote Originally Posted by Tim_Excel_ View Post
    How about you just delete the whole line selected in yellow, it does nothing.
    then i will get the next line highlighted in yellow. if i ignore that error, i get another error "Run-time error 9. Subscript out of range".

    If i delete the next line as well, then i get more and more lines highlighted. lol. i dont know what i am doing.

  8. #8
    Board Regular Tim_Excel_'s Avatar
    Join Date
    Jul 2016
    Posts
    512
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Code doesnt work after being imported into a new excel

    With
    Code:
    Workbooks("Book1").Activate
    'or
    Windows("Book1.xlsx").Activate
    or anything of such sort, you are referring to a very specific object, in this example a workbook named Book1. If no such object exists, VBA will return an error. Since your code is not dynamic at all, it will keep running into these errors until all references are correct.

    Best to do is to figure out what you want to achieve and go from that, and then ask us to help you with that. I am unable to rewrite the module as it's a mess specifically designed for a very rigid workbook.
    Always make a backup of your data before trying out any code

    Before you ask why my code is not working: have you checked if the references are correct? Are you referring to the correct Workbook/-sheet?

    Use [CODE] tags when pasting code in your posts

  9. #9
    New Member
    Join Date
    Mar 2019
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code doesnt work after being imported into a new excel

    Quote Originally Posted by Tim_Excel_ View Post
    With
    Code:
    Workbooks("Book1").Activate
    'or
    Windows("Book1.xlsx").Activate
    or anything of such sort, you are referring to a very specific object, in this example a workbook named Book1. If no such object exists, VBA will return an error. Since your code is not dynamic at all, it will keep running into these errors until all references are correct.

    Best to do is to figure out what you want to achieve and go from that, and then ask us to help you with that. I am unable to rewrite the module as it's a mess specifically designed for a very rigid workbook.
    i have a table with all the companies details, including the different dates that the companies do their surveys. the surveys are done on ad hoc basis, so a company may be doing the X time survey, while some are on their first.

    the vba codes above are supposed to extract all the companies who have done at least two surveys on a specific dates. if the company has done survey 2 and 3 within the specified date, then it should appear in the same row.

    this is the file that the freelancer worked on with dummy data. i believe it's easier for you to understand once you see the file.
    Last edited by stanco; Mar 28th, 2019 at 04:36 AM.

  10. #10
    Board Regular Tim_Excel_'s Avatar
    Join Date
    Jul 2016
    Posts
    512
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Code doesnt work after being imported into a new excel

    I don't have too much time on my hands right now so unfortunately I can't help you further.
    I can tell that module 2 copies a bunch of data and creates/deletes sheets. Perhaps it's an idea if you start another thread with what you want to achieve with module 2 so someone else can help you out. Good luck!
    Always make a backup of your data before trying out any code

    Before you ask why my code is not working: have you checked if the references are correct? Are you referring to the correct Workbook/-sheet?

    Use [CODE] tags when pasting code in your posts

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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