时间戳

首页 > 解决方案 > 使用 vba 查找列标题并在该标题下添加新记录

excel - 使用 vba 查找列标题并在该标题下添加新记录

问题描述

我正在尝试创建能够从一个文本框中获取值的东西，搜索一组列标题以找到正确的标题，然后将第二个文本框中的新值放入该列下的最后一行。我修改了我在这里找到的这段代码，https://stackoverflow.com/a/37687346/13073514，但我需要一些帮助。此代码将第二个文本框中的值发布在每个标题下，我希望它只发布在文本框 1 中的标题下。任何人都可以帮助我并解释我如何才能使它工作吗？我是 vba 的新手，所以任何解释都将不胜感激。

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 "Craig"
MyColl.Add "Ed"

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

For i = 1 To 25
    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

标签： excelvba

解决方案

我已经评论了您的代码，以便您更好地理解。这里是。

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 "Craig"
    MyColl.Add "Ed"
    Debug.Print MyColl(1), MyColl(2)        ' see output in the Immediate Window

    ' your code starts in the top left corner of the sheet,
    ' moves backward (xlPrevious) from there by rows (xlByRows) until
    ' it finds the first non-empty cell and returns its row number.
    ' This cell is likely to be in column A.
    lastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    For i = 1 To 25                         ' do the following 25 times
        ' in Cells(1, i), i represents a column number.
        ' 1 is the row. It never changes.
        ' Therefore the code will look at A1, B1, C1 .. until Y1 = cells(1, 25)
        For Each myIterator In MyColl       ' take each item in MyColl in turn
            If Cells(1, i) = myIterator Then
                ' set a range in the column defined by the current value of i
                ' extend it from row 2 to the lastRow
                Set myRng = Range(Cells(2, i), Cells(lastRow, i))
                ' loop through all the cells in myRng
                For Each myCell In myRng
                    ' convert the value found in each cell to a number.
                    ' in this process any non-numeric cells would become zero.
                    myCell.Value = Val(myCell.Value)
                Next myCell
            End If
        Next myIterator
    Next i
End Sub

如您所见，任何地方都没有涉及 TextBox。因此，您的问题不容易理解。但是，我的解释可能使您能够修改它。这完全是通过坐标识别工作表中的单元格并为其分配正确值的问题。

推荐阅读