Transfer data between matching headers

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Transfer data between matching headers

  1. #1
    New Member
    Join Date
    Feb 2016
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Transfer data between matching headers

     
    Can anyone help please, struggling to understand - If I run this code independently it works fine but when I incorporate it into a wider routine it is returning a runtime error 5 on the red highlighted section?

    Dim rawSht As Worksheet
    Dim procSht As Worksheet
    Dim headers As Collection
    Dim c As Long
    Dim v As Variant

    With Application
    CalcMode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False

    Set rawSht = ThisWorkbook.Worksheets("Raw Data")
    Set procSht = ThisWorkbook.Worksheets("Output Sheet")


    Set headers = New Collection
    For c = 1 To rawSht.Cells(1, Columns.Count).End(xlToLeft).Column
    headers.Add c, rawSht.Cells(1, c).Text
    Next

    For c = 1 To 47
    rawCol = headers(procSht.Cells(6, c).Text)
    v = rawSht.Range(rawSht.Cells(2, rawCol), rawSht.Cells(Rows.Count, rawCol).End(xlUp)).Value2
    procSht.Cells(7, c).Resize(UBound(v, 1)).value = v
    Next

    End With

  2. #2
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,032
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Transfer data between matching headers

    What is the value of procSht.Cells(6, c) when the code errors?

    The error indicates that this header wasn't previously found in row 1 of rawSht.

    If the code works now, but errors if incorporated into wider code, I can only guess that the wider code is doing something unintended, e.g. perhaps accidentally overwriting your header area(s) with other values?

  3. #3
    New Member
    Join Date
    Feb 2016
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Transfer data between matching headers

    Thanks @ StephenCrump - can't work it out still. The value of procSht.Cells(6, c) is blank when it errors.

    The routine before that is opening a closed workbook and importing a worksheet into my current workbook. Once this is done, the above routine then starts the process of transferring the data from the imported sheet into the right columns on another worksheet.

  4. #4
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,032
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Transfer data between matching headers

    Quote Originally Posted by trux101 View Post
    The value of procSht.Cells(6, c) is blank when it errors.
    I am guessing that if you have no header value in procSht.Cells(6, c), then there is nothing to be copied from rawSht?
    In which case, perhaps you could modify your code:

    Code:
    For c = 1 To 47
        If procSht.Cells(6, c).Text <> "" Then
            rawcol = headers(procSht.Cells(6, c).Text)
            v = rawSht.Range(rawSht.Cells(2, rawcol), rawSht.Cells(Rows.Count, rawcol).End(xlUp)).Value2
            procSht.Cells(7, c).Resize(UBound(v, 1)).Value = v
        End If
    Next
    Alternatively, if you have a blank header in procSheet, and there is a corresponding blank header in rawSht, and you want want to copy the matching column across to procSheet, then I suggest you check that the headers are in fact both blank. If the Collection key isn't a match, it would suggest that perhaps one header is "" and the other " ", or similar?

  5. #5
    New Member
    Join Date
    Feb 2016
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Transfer data between matching headers

    Hasn't fixed it for some reason, thanks Stephen. Any other ideas on how I can achieve what I am aiming for?

    Raw Data contains 47 columns and I would like to transfer data for all columns that headers are matched in Output Sheet.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    5,011
    Post Thanks / Like
    Mentioned
    109 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Transfer data between matching headers

    What is the value of c when you get the error?
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

    Running Office 2003 & 2013 on Win 7

  7. #7
    New Member
    Join Date
    Feb 2016
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Transfer data between matching headers

      
    Rookie mistakes on my part – very new to VBA. The issue was that I was referring to more columns than containing headers in procSht so have made that dynamic. Thanks all for your help!

    Const HDR1 As Long = 1 'header row on sheet 1
    Const HDR2 As Long = 1 'header row on sheet 2

    Dim ws1 As Worksheet, ur1 As Range, vr1 As Variant, c1 As Long, c2 As Long, r As Long
    Dim ws2 As Worksheet, ur2 As Range, vr2 As Variant, msg As String, t As Double
    Dim rawSht As Worksheet
    Dim procSht As Worksheet
    Dim headers As Collection
    Dim c As Long
    Dim v As Variant


    Set rawSht = ThisWorkbook.Worksheets("Raw Data")
    Set procSht = ThisWorkbook.Worksheets("Output")

    Set headers = New Collection
    For c = 1 To rawSht.Cells(1, Columns.Count).End(xlToLeft).Column
    headers.Add c, rawSht.Cells(1, c).Text
    Next

    For c = 1 To procSht.Cells(1, Columns.Count).End(xlToLeft).Column
    rawCol = headers(procSht.Cells(1, c).Text)
    v = rawSht.Range(rawSht.Cells(1, rawCol), rawSht.Cells(Rows.Count, rawCol).End(xlUp)).Value2
    procSht.Cells(1, c).Resize(UBound(v, 1)).value = v
    Next

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
  •  

 

 
DMCA.com