Create buttons for work book from instruction sheet on the workbook

Referring to the Same Worksheet

• Always use Option Explicit. If you would have used it, you would have noticed that the variables city_selection and city_cell, and i are not declared.

• When having a 'ton' of variables, keep them close to the 'action' to make the code more readable (see in Quick Fix). Use shorter variable names, always preferably (but not necessarily) descriptive.

• When using the With statement, you have to use the period (dot, .) in front of Worksheets, Range, Cells, Columns, Rows...etc., e.g.:

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

  In this example, you have made sure that the range is in the worksheet Credentialing_Work_History.

• You don't have to loop through the cells of the range, you can use Proper and Trim on a range (if you will allow Trim instead of RTrim).

• You have to qualify your ranges i.e. make sure they refer to the correct worksheet. See this also in the corrections of the function (added ws parameter).

• Note that the function would be more useful if it would return a range instead of a range address so you could use e.g. Set name_rng = getColRange(Credentialing_Work_History, "Company_Name"). That could be one of your next tasks.

The Code

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