excel - 从工作簿上的说明表创建工作簿按钮

Question

抱歉，我发布了我的整个代码以获得更好的视觉效果。我创建了 getcol 函数来给它字符串(列名)，它返回该列的范围

Public Function getColRange(colName As String) As String

    'create variables that will be used in this function
    Dim first As String
    Dim last As String
    Dim col As String
    Dim first_row As Integer
    Dim first_str As String
    Dim last_col As String
    Dim last_row As Integer
    Dim last_str As String
     
    'loop to check if colname is equal in range between columns A and X, easy to change below
    For Each i In Range("A1:X1")
        If i = colName Then
            'catches column, first and last rows
            col = Split(i.Address(1, 0), "$")(0)
            last_row = Range("A2").End(xlDown).Row
            first_row = 2
            
            'make first and last addresses as strings
            first_str = "" & col & first_row
            last_str = "" & first_col & last_row
            
            'function ouput in the next line is a combination of above two strings
            getColRange = "" & first_str & ":" & col & last_str
        End If
    Next

End Function

选项显式
子proper_text()
暗淡 name_rng 作为范围
将 name_cell 调暗为范围
暗淡 name_selection 作为字符串
将 city_rng 调暗为范围
将 city_cell 调暗为范围
将 city_selection 调暗为字符串

    Dim col_name As String
    Dim trim_name_row As Long
    Dim trim_name_rng As Range
    Dim trim_name_cell As Range
    Dim col_city As String
    Dim trim_city_row As Long
    Dim trim_city_rng As Range
    Dim trim_city_cell As Range
    
    With Credentialing_Work_History
        ' First Part
        name_selection = getColRange("Company_Name")
        Set name_rng = Range(name_selection)
        For Each name_cell In name_rng
            name_cell.Value = WorksheetFunction.Proper(name_cell.Value)
        Next
        
        city_selection = getColRange("Company_City")
        Set city_rng = Range(city_selection)
        For Each city_cell In city_rng
            city_cell.Value = WorksheetFunction.Proper(city_cell.Value)
        Next
        'Second Part
        col_name = getColRange("Company_Name")
        ' To 'Find the last used cell in Col A
        trim_name_row = Range(col_name).End(xlDown).Row
        
        'Declare the range used by having the coordinates of rows and column till the last cell used.
        Set trim_name_rng = Range(Cells(2, 9), Cells(trim_name_row, 9))
        ' Loop through the range and remove any trailing space
        For Each trim_name_cell In trim_name_rng
            trim_name_cell = RTrim(trim_name_cell)
        'Go to the next Cell
        Next trim_name_cell
        
        col_city = getColRange("Company_Name")
        trim_city_row = Range(col_city).End(xlDown).Row
        
        Set trim_city_rng = Range(Cells(2, 10), Cells(trim_city_row, 10))
        
        For Each trim_city_cell In trim_city_rng
            trim_city_cell = RTrim(trim_city_cell)
        Next trim_city_cell
    End With


End Sub

Answer 1

引用同一张工作表

始终使用 Option Explicit .如果您使用过它，您会注意到变量 city_selection和 city_cell , 和 i未声明。

当有“吨”变量时，让它们靠近“ Action ”以使代码更具可读性(参见 Quick Fix)。使用较短的变量名称，最好(但不一定)具有描述性。

使用 With 时声明，您必须在 . 前面使用句点(点，Worksheets) , Range , Cells , Columns , Rows ...等，例如:

With Credentialing_Work_History
    Set name_rng = .Range(name_selection)
End With

在此示例中，您已确保该范围位于工作表 Credentialing_Work_History 中。 .

您不必遍历范围的单元格，您可以使用 Proper和 Trim在一个范围内(如果您允许 Trim 而不是 RTrim )。

您必须限定您的范围，即确保它们引用正确的工作表。在函数的更正中也可以看到这一点(添加了 ws 参数)。

请注意，如果该函数返回范围而不是范围地址，则该函数会更有用，因此您可以使用例如Set name_rng = getColRange(Credentialing_Work_History, "Company_Name") .这可能是您接下来的任务之一。

代码

Option Explicit

Sub proper_text()
        
    ' Name
    Dim name_selection As String
    Dim name_rng As Range
    name_selection = getColRange(Credentialing_Work_History, "Company_Name")
    If name_selection <> "" Then
        Set name_rng = Credentialing_Work_History.Range(name_selection)
        name_rng.Value = Application.Trim(Application.Proper(name_rng.Value))
    End If
    
    ' City
    Dim city_rng As Range
    Dim city_selection As String
    city_selection = getColRange(Credentialing_Work_History, "Company_City")
    If name_selection <> "" Then
        Set city_rng = Credentialing_Work_History.Range(city_selection)
        city_rng.Value = Application.Trim(Application.Proper(city_rng.Value))
    End If
 
End Sub

Function getColRange(ws As Worksheet, colName As String) As String

    'create variables that will be used in this function
    Dim first As String
    Dim last As String
    Dim col As String
    Dim first_col As String
    Dim first_row As Long
    Dim first_str As String
    Dim last_col As String
    Dim last_row As Long
    Dim last_str As String
    Dim rg As Range
     
    'loop to check if colname is equal in range between columns A and X, easy to change below
    For Each rg In ws.Range("A1:X1")
        If rg = colName Then
            'catches column, first and last rows
            col = Split(rg.Address(1, 0), "$")(0)
            last_row = ws.Range("A2").End(xlDown).Row
            first_row = 2
            
            'make first and last addresses as strings
            first_str = "" & col & first_row
            last_str = "" & first_col & last_row
            
            'function ouput in the next line is a combination of above two strings
            getColRange = "" & first_str & ":" & col & last_str
        End If
    Next rg

End Function

Sub proper_text_QuickFix()
    
    Dim ws As Worksheet: Set ws = Credentialing_Work_History
        
    ' Name
    Dim name_selection As String
    Dim name_rng As Range
    Dim name_cell As Range
    name_selection = getColRange(ws, "Company_Name")
    Set name_rng = ws.Range(name_selection)
    Debug.Print name_rng.Address
    For Each name_cell In name_rng
        name_cell.Value = WorksheetFunction.Proper(name_cell.Value)
    Next
    
    ' City
    Dim city_name_selection As String
    Dim city_rng As Range
    Dim city_name_cell As Range
    city_name_selection = getColRange(ws, "Company_City")
    Set city_rng = ws.Range(city_name_selection)
    Debug.Print city_rng.Address
    For Each city_name_cell In city_rng
        city_name_cell.Value = WorksheetFunction.Proper(city_name_cell.Value)
    Next
    
    ' Trim Name
    Dim col_name As String
    Dim trim_name_row As Integer
    Dim trim_name_rng As Range
    Dim trim_name_cell As Range
    
    col_name = getColRange(ws, "Company_Name")
    trim_name_row = ws.Range(col_name).End(xlDown).Row
    Set trim_name_rng = ws.Range(ws.Cells(2, 9), ws.Cells(trim_name_row, 9))
    Debug.Print name_rng.Address
    For Each trim_name_cell In trim_name_rng
        trim_name_cell = RTrim(trim_name_cell)
    Next trim_name_cell
    
    ' Trim City
    Dim col_city As String
    Dim trim_city_row As Integer
    Dim trim_city_rng As Range
    Dim trim_city_cell As Range
    
    col_city = getColRange(ws, "Company_City")
    trim_city_row = ws.Range(col_city).End(xlDown).Row
    Set trim_city_rng = ws.Range(ws.Cells(2, 10), ws.Cells(trim_city_row, 10))
    Debug.Print trim_city_rng.Address
    For Each trim_city_cell In trim_city_rng
        trim_city_cell = RTrim(trim_city_cell)
    Next trim_city_cell
 
End Sub