时间戳

首页 > 解决方案 > VBA .Find 和 .Union 方法

vba - VBA .Find 和 .Union 方法

问题描述

我是 VBA 新手。我想找到六列的标题并使用联合方法将它们连接起来。一旦它们作为 BigColumns 加入，我想将字体大小更改为 14。

我已经尝试过循环，但鉴于我是新手，我想尝试一种更简单的方法。我可以在一个范围（例如 TC1）上成功运行此代码，并将字体更改为 14。但是一旦我尝试组合范围，它就会失败。

Sub ASOLDPrintFormatTesting2()   
Dim Table As Range
Dim BigColumns As Range
Dim TC1, TC2, TC3, TC4, TC5, TC6 As Range
'TC stands for Table Columns These individual ranges are joined through 
Union Method

Set ReferenceCell = ActiveCell
Set WS = Sheets(2)

With WS
    Set ReferenceCell = Cells.Find(What:="Source #", LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    'Make Source # the Reference Cell, or the equivalent of A1
If Not ReferenceCell Is Nothing Then
    Set ReferenceCell = Cells.Find(What:="Sample #", LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
End If
    'Make Sample # the Reference Cell

Set Table = ReferenceCell.CurrentRegion

With Table
    Set TC1 = .Find(What:="Source Well", LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

    Set TC2 = .Find(What:="Sample ID", LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

    Set TC3 = .Find(What:="VerboseConc_uM", LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

    Set TC4 = .Find(What:="VerboseConc_ug/ml", LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

    Set TC5 = .Find(What:="Mol Wt.", LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

    Set TC6 = .Find(What:="N/Mole", LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    'Find all headers of BigColumns

    Set BigColumns = Application.Union(Range("TC1"), Range("TC2"), _
    Range("TC3"), Range("TC4"), Range("TC5"), Range("TC6"))
    'Union of all headerrs as BigColumns

    If Not BigColumns Is Nothing Then
    Else
        MsgBox "'BigColumns' not found"
        Exit Sub
    End If
End With

With BigColumns
    .Cells.Font.Size = 14
End With
'Test to see if BigColumns works

End With

End Sub

代码运行时不会产生错误消息。

标签： vba

解决方案

使用数组（将给出一个片段示例），您应该能够循环并节省一些时间和可能出现的问题：

dim arr as variant, i as long
arr = array("Source Well","Sample ID") 'can add more
with table
    for i = lbound(arr) to ubound(arr)
        set f = .find(what:=arr(i))
        if not f is nothing then f.font.size = 14
    next i
end with

-未经测试的代码-

您找到您的项目，如果没有找到它会跳过更改字体大小。

推荐阅读