Storing headers in string array VBA

Question

New to VBA and am trying to essentially lookup column headers in on sheet to another and if match copy data over...

I was told that I can store my headers in a string array then loop through and compare the headers to my array to see if they match.

ie: For each c In
Sheet1.Range("A1:BA1").offset(rownumber -1)

But I'm not sure what that means? How do I store my headers in a string array? Sorry if this is a super basic question. I have googled it and not found anything explaining how to do this or what it means.

My Project: research data on sheet1. If there is an issue I want to click a button that will copy only the matching column data to a new row in a Specified sheet. From there the data will be reviewed and then another button to export the data to an MS SQL table.

ie:

Sheet1
A       B        C      D    E
ID     CUR    Region Amount Y/N
1      USD      NA    $54    Y

Sheet2
   A       B     C     D     E
Region    CUR   Amount   Type  Misc

So if Column E = Y then copy all the relevant data in that row to a new sheet:

Sheet2 (output)
   A       B     C      D     E
Region    CUR   Amount Type  Misc
  NA      USD   $54    Null  Null

Sheet2 has columns not in Sheet1 and vice versa... Also the order of the columns are not the same in each sheet. The real sheets are huge with many columns and the row count will change everytime I refresh my data. I need this to loop until Col A in Sheet1 is null.

So to simplify; you need to check if Column E in Sheet1 is a "Y", if it is then fill in the data from that row to the corresponding columns in Sheet2? Will the columns in both sheets always stay the same, or will they be changing? — D_Zab
– D_Zab, Commented Jan 20, 2015 at 21:07
They have the potential to change. And I corrected one of the columns in sheet2. — user3486773
– user3486773, Commented Jan 21, 2015 at 2:40

sancho.s ReinstateMonicaCellio · Accepted Answer · 2015-01-20 21:39:32Z

2

How do I store my headers in a string array?

A very practical way:

Dim hdlist As String
Dim sep As String
hdlist = "ID|CUR|Region|Amount|Y/N"      ' Change this line
sep = "|"
Dim hdnames() As String
hdnames = Split(hdlist, sep, -1, vbBinaryCompare)

Then you can use a For loop to traverse the array.

answered Jan 20, 2015 at 21:39

sancho.s ReinstateMonicaCellio

15.8k25 gold badges110 silver badges212 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Doug Glancy Over a year ago

You can skip a few steps with something like: HeaderList = Array("header1", "header2")

sancho.s ReinstateMonicaCellio Over a year ago

@DougGlancy - Yes. I have personally found this more practical when you have many elements in the array. It is a matter of taste.

user3486773 Over a year ago

This answered the actual question and cleared up what they were referring to

basodre · Accepted Answer · 2015-01-20 21:24:04Z

1

Here's a piece of code that I've thrown together that I think meets your needs. I think the variable names are self explanatory, but if not, please follow up.

The code searches each cell in the header row of the origin sheet to see if it exists in the destination sheet. If so, it copies over the corresponding information.

Dim wsOrigin As Worksheet
Dim wsDest As Worksheet
Dim nCopyRow As Long
Dim nPasteRow As Long
Dim rngFnd As Range
Dim rngDestSearch As Range
Dim cel As Range

Const ORIGIN_ROW_HEADERS = 1
Const DEST_ROW_HEADERS = 1


Set wsOrigin = Sheets("Sheet1")
Set wsDest = Sheets("Sheet2")

nCopyRow = ActiveCell.Row
nPasteRow = wsDest.Cells(Rows.Count, 1).End(xlUp).Row + 1

Set rngDestSearch = Intersect(wsDest.UsedRange, wsDest.Rows(DEST_ROW_HEADERS))

For Each cel In Intersect(wsOrigin.UsedRange, wsOrigin.Rows(ORIGIN_ROW_HEADERS))
    On Error Resume Next
        Set rngFnd = rngDestSearch.Find(cel.Value)

        If rngFnd Is Nothing Then
            'Do Nothing as Header Does not Exist
        Else
            wsDest.Cells(nPasteRow, rngFnd.Column).Value = wsOrigin.Cells(nCopyRow, cel.Column).Value
        End If
    On Error GoTo 0

    Set rngFnd = Nothing
Next cel

answered Jan 20, 2015 at 21:24

basodre

5,7901 gold badge18 silver badges25 bronze badges

2 Comments

user3486773 Over a year ago

I'm torn. Technically the top answer is the answer to the question I posted. But you knocked it out of the park with this^^^. This is the answer to my project & only requires minor tweaking. Going to study this good & long to understand how it does what I've been trying for days to do.The only thing this doesn't do is search the "Y/N" column to see if = Y before copying and then looping to the bottom of the sheet. Once I understand this more I'll add those tweaks. What's amazing is how scalable this is. You didn't even need to know any of the header names in order to make this work. Bravo!

basodre Over a year ago

Thanks! I figured I'd try to help you solve the underlying problem. With regards to the Y/N column: If I understood your request correctly, a button click will fire this macro. Once clicked, the button will check to see if the Y/N column is indeed a Y? If that's correct, then the easiest solution is to encase the entire code block within an IF / ELSE statement. For example, IF cells(Activecell.row, 5) = "Y" Then 'ENTER CODE ELSE END IF

Collectives™ on Stack Overflow

Storing headers in string array VBA

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related