Results 1 to 3 of 3
  1. #1
    laterdater is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    4

    Array in Loop possibly not resetting

    Hey All,



    Part of my code is taking info from a sheet and pasting it into the header of a corresponding template sheet in the same workbook. I'm trying to paste only values that end in a 2 to one cell and only values that end in a 1 to another cell.

    For some reason, my first header will paste correctly, but the following loops through the workbook will always defer to the "else" part of my loop.

    Code:
    Sub Header()Dim Names As Range, name As Range, sh As Worksheet, shdest As Worksheet, rn As Range, k As Long
    Dim x As Long, wkshtcount As Long
    
    
    wkshtcount = (ActiveWorkbook.Worksheets.Count - 1) / 2
    
    
    For x = 1 To wkshtcount
    
    
        Set sh = ThisWorkbook.Sheets(x + 1)
        Set shdest = ThisWorkbook.Sheets(sh.name & (x + 1))
        Set rn = sh.UsedRange
        Set Names = sh.Range("A2:A" & Range("A1").End(xlDown).Row)
                 n = 0
            
                For Each name In Names
                    If name.Offset(1, 0) <> name And name Like "*2" Then
                              
                                shdest.Range("D7") = name
                                Else: shdest.Range("D8") = name
    
    
                    End If
                Next name
                
            shdest.Range("D9") = sh.Range("E2")
            shdest.Range("D5") = sh.Range("G2")
            shdest.Range("G5") = sh.Range("D2")
            shdest.Range("G6") = sh.Range("F2")
        
     Set name = Nothing
        
    Next x
    
    
    
    
    End Sub

  2. #2
    JamesDeckert is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Jul 2015
    Location
    Salina, KS
    Posts
    262
    This looks like Excel code to me. You posted this in the Access forum.
    I can respond as if this were an Access problem.
    Is 'name' a number or a string?
    if it's a number you can't test for it like a string.
    you can mod() the number by 2 to see if there is a remainder of 0. if so, the number is even as you're testing for.
    If this stuff doesn't apply, then ignore my feedback and post your question in the Excel section http://www.msofficeforums.com/

  3. #3
    laterdater is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    4
    Oops, you are totally right, I posted this in the wrong section.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA loop array of dictionary
    By Ruegen in forum Programming
    Replies: 2
    Last Post: 07-15-2015, 11:02 PM
  2. Replies: 2
    Last Post: 05-14-2015, 12:24 PM
  3. Replies: 6
    Last Post: 02-10-2014, 07:43 AM
  4. Loop or Array not recognized
    By dvgef2 in forum Forms
    Replies: 6
    Last Post: 05-31-2013, 08:37 AM
  5. Replies: 30
    Last Post: 08-30-2012, 05:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums