根据特定的文本字符串选择列 [英] Selecting columns based on specific text strings

问题描述

我正在尝试组合一个宏，该宏将选择某些列并将其粘贴到新的工作表中.问题是，随着人们认为合适，往往会添加和删除列，这会偏离绝对引用.

I'm trying to put together a macro which will select certain columns and paste them into a new sheet. The problem is that columns tend to be added and deleted as people see fit which throws off the absolute referencing.

我一直在尝试修改使用宏记录器生成的基本宏，但是我没有运气根据它们的内容选择列.我有一个每天从我们的数据库中生成的工作表，其中包含不断变化的字段.例如，我只想选择零件号"，成本"和联系方式"字段，但是今天添加的IDN使得我的旧宏失效了.

I have been trying to tinker with the basic macro produced using the macro recorder but I haven't had any luck selecting columns based on their contents. I have a sheet that is generated from our database daily with changing fields. For example, I would like to select just the Part #, Cost, and Contact fields but the addition of the IDN today threw my old macro off.

到目前为止，我已经尝试使用基本的excel查找功能，例如vlookup，对常量列表的索引以及下面的查找功能，但似乎都无法正常工作.在这里我缺少什么来选择包含所需文本的列吗?

So far, I've tried to use basic excel find functions like vlookup, index against a list of constants and the find function below but none seem to work. Is there something I am missing here to select the column with my desired text?

Columns(find("Part#")).Select

Columns(find("Part #")).Select

推荐答案

首先，

Firstly, be careful with .Select and it looks like you're not fully qualifying your references to the worksheet so take note of the full Workbook.Worksheet.Range type referencing below.

这是您的快速解决方法:

Public Sub Test()
    'the Range approach
    ThisWorkbook.Worksheets("Sheet1").Rows(1).Find(What:="Part #", LookAt:=xlWhole).EntireColumn.Copy
    ThisWorkbook.Worksheets("Sheet2").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats 'or xlPasteAll to include formulas
End Sub

使用整个列范围通常不是最好的主意.它将起作用，但前提是您每次必须记住将复制的列粘贴到目标工作表的第一行中.

Using entire column ranges isn't usually the best idea. It will work, but only as long as you remember to paste your copied column into the first row of the destination worksheet every time.

这是更好的选择(IMO):
通过单击功能区主选项卡上的格式为表格" ，将数据转换为表格(在VBA中为ListObject).现在可以更轻松地引用您的列-只需按名称进行操作即可，无需使用 Find .

Here's the better option (IMO):
Convert your data to a Table (know as a ListObject in VBA) by clicking "format as table" on the home tab of the ribbon. It's now much easier to reference your column - just do it by name, no need to use Find.

Public Sub Test()
    'the ListObject approach
    ThisWorkbook.Worksheets("Sheet1").ListObjects("MyTable").ListColumns("Part #").Copy
    ThisWorkbook.Worksheets("Sheet2").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats 'or xlPasteAll to include formulas
End Sub

您需要知道表的名称(在这里我称其为 MyTable )，只需在选择表时单击功能区上的表"即可做到这一点

You need to know the name of your table (I called it MyTable here), which you can do by clicking on "Table" on the ribbon when your table is selected

这篇关于根据特定的文本字符串选择列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！