将标头存储在字符串数组VBA中 [英] Storing headers in string array VBA

问题描述

VBA的新手，我试图从本质上查找工作表中的列标题到另一个标题，如果匹配，则复制数据...

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)

但是我不确定这意味着什么？如何将标头存储在字符串数组中？抱歉，这是一个非常基本的问题。我已经用Google搜索了它，却没有发现任何解释如何执行此操作或其含义的东西。

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.

我的项目：在sheet1上有
个研究数据。如果有问题，我想单击一个按钮，该按钮将仅将匹配的列数据复制到指定工作表中的新行。从那里将检查数据，然后再单击另一个按钮以将数据导出到MS SQL表。

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.

即：

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

因此，如果列E = Y，则将该行中的所有相关数据复制到新工作表中：

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的Sheet1中没有列，反之亦然...每个列中的列顺序也不相同。实际的工作表非常大，有很多列，并且每次刷新数据时行数都会改变。我需要循环直到Sheet1中的Col A为空。

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.

推荐答案

如何将标头存储在字符串中

一种非常实用的方法：

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)

然后您可以使用 For 循环遍历数组。

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

这篇关于将标头存储在字符串数组VBA中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！