Results 1 to 6 of 6

Thread: [RESOLVED] Something weird is happening in excel (VBA)

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jul 2012
    Posts
    27

    Resolved [RESOLVED] Something weird is happening in excel (VBA)

    Hi guys,

    Got this code below where ws1/2/3 etc makes reference to my worksheet. All stuff is defined.

    Code:
        Dim i As Long
            
        Dim RwCount1 As Long
        
        RwCount1 = ws5.Range("F" & Rows.Count).End(xlUp).Row
        
        For i = 2 To RwCount1
        
        If ws5.Range("CG" & i).Value = "Unknown" Or "0" Then
        ws5.Activate
        ws5.Range("E" & i).Select
        Selection.Copy
        ws6.Activate
        Range("J" & Rows.Count).End(xlUp).Offset(1, 0).Select
        ActiveSheet.Paste
        
        End If
        
        Next i
        
        Application.ScreenUpdating = True
        
        End Sub
    This macro looks through column CG and determines which have an "unknown" or "0" value assigned as the result of a formula. It then pastes the corresponding value of column E into a new sheet.

    Now its doing this fine, but for some reason it is not taking in duplicate values, which is fantastic as it has saved me from writing the code to get rid of duplicates; but does anyone know why it is acting like this?


    Thanks
    Last edited by Siddharth Rout; Jul 24th, 2012 at 10:02 AM. Reason: Code Tags Added

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Something weird is happening in excel (VBA)

    Which duplicates is it not "taking?" Values from column E?

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jul 2012
    Posts
    27

    Re: Something weird is happening in excel (VBA)

    yeh, there are duplicate values in column E, but it seems to be only taking one instance.
    This is fine, I was just wondering why it was doing this

  4. #4
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Something weird is happening in excel (VBA)

    I've attached my version of this...can you step through and tell me if you see anything different than your version? I DO get dupes in mine...
    Attached Files Attached Files

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Something weird is happening in excel (VBA)

    If ws5.Range("CG" & i).Value = "Unknown" Or "0" Then
    this is incorrect, should be
    vb Code:
    1. If ws5.Range("CG" & i).Value = "Unknown" Or ws5.Range("CG" & i).Value = "0" Then
    i can not be sure what the difference in result should be
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Jul 2012
    Posts
    27

    Re: Something weird is happening in excel (VBA)

    vbfbryce - no problem on this one, dno whats going on then. Thanks for your help again!

    westconn1 - thanks aswell, duly noted re the 'or' point

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width