按名称查找列标题并选择列标题下方的所有数据(Excel-VBA) [英] Find Column Header By Name And Select All Data Below Column Header (Excel-VBA)

问题描述

这是我的第一篇文章...

This is my first post...

我正在尝试创建一个宏来执行以下操作:

I'm attempting to create a macro to do the following:

1. 按名称搜索电子表格列标题.
2. 从选定列中选择所有数据，但列标题除外.

3. 将数字存储为文本&转换为数字.

1. Search a spreadsheet column header by name.
2. Select all data from the selected column, except column header.

3. Take Number Stored As Text & Convert to Number.

• 转换为数字以用于VLookup.

例如:

Visual Spreadsheet示例:

Visual Spreadsheet Example:

我在网上发现了以下代码:

I've discovered the following code online:

With ActiveSheet.UsedRange

Set c = .Find("Employee ID", LookIn:=xlValues)

If Not c Is Nothing Then

    ActiveSheet.Range(c.Address).Offset(1, 0).Select
End If

End With

但是，我仍然遇到一些问题，我们将不胜感激.

However, I'm still experiencing some issues, any assistance would be greatly appreciated.

推荐答案

尝试一下.只需将要查找的所有列标题名称添加到集合中.我假设您的列数不超过200，如果您只是将i = 1到200的部分更新为更大的数即可.

Try this out. Simply add all the column header names you want to find into the collection. I'm assuming you don't have more than 200 columns, if you do simply update the for i = 1 to 200 section to a larger number.

Public Sub FindAndConvert()
    Dim i           As Integer
    Dim lastRow     As Long
    Dim myRng       As Range
    Dim mycell      As Range
    Dim MyColl      As Collection
    Dim myIterator  As Variant

    Set MyColl = New Collection

    MyColl.Add "Some Value"
    MyColl.Add "Another Value"

    lastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    For i = 1 To 200
        For Each myIterator In MyColl
            If Cells(1, i) = myIterator Then
                Set myRng = Range(Cells(2, i), Cells(lastRow, i))
                For Each mycell In myRng
                    mycell.Value = Val(mycell.Value)
                Next
            End If
        Next
    Next
End Sub

这篇关于按名称查找列标题并选择列标题下方的所有数据(Excel-VBA)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！