Sub AddStuff()
Dim Sheet As Worksheet
Dim FoundRange As Range
Dim LastRow As Long
Set Sheet = Worksheets("Sheet1")
LastRow = Sheet.Cells(Sheet.Rows.Count, 2).End(xlUp).Row
If LastRow < 2 Then Exit Sub
On Error Resume Next
Set FoundRange = Sheet.Range("B2:B" & LastRow).SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If FoundRange Is Nothing Then Exit Sub
UpdateColumnValues FoundRange, "A", "AVC", LastRow, 2, Sheet, True
UpdateColumnValues FoundRange, "C", "#", LastRow, 2, Sheet, True
UpdateColumnValues FoundRange, "D", "12", LastRow, 2, Sheet, True
UpdateColumnValues FoundRange, "E", "ab", LastRow, 2, Sheet, True
UpdateColumnValues FoundRange, "J", "NA", LastRow, 2, Sheet, True
UpdateColumnValues FoundRange, "M", "t", LastRow, 2, Sheet, True
UpdateColumnValues FoundRange, "N", "US", LastRow, 2, Sheet, True
UpdateColumnValues FoundRange, "U", "", LastRow, 2, Sheet, True
End Sub
Sub UpdateColumnValues( _
ByVal EmulationRange As Range, _
ByVal ColumnNumberOrLetter As Variant, _
ByVal ColumnValue As Variant, _
ByVal ColumnLastRow As Long, _
Optional ByVal ColumnStartRow = 2, _
Optional ByVal ColumnSheet As Worksheet, _
Optional ByVal Status As Boolean = False _
)
Dim Completed As Boolean
Dim ColumnOffset As Long
If ColumnSheet Is Nothing Then
If ActiveSheet Is Nothing Then
GoTo Finish
End If
Set ColumnSheet = ActiveSheet
End If
On Error Resume Next
ColumnOffset = GetColumnNumber(ColumnNumberOrLetter) - EmulationRange.Column
ColumnSheet.Range(ColumnSheet.Cells(ColumnStartRow, EmulationRange.Column), ColumnSheet.Cells(ColumnLastRow, EmulationRange.Column)).Offset(0, ColumnOffset).ClearContents
EmulationRange.Offset(0, ColumnOffset).Value = ColumnValue
On Error GoTo 0
If Err.Number > 0 Then GoTo Finish
Completed = True
Finish:
If Status Then
If Completed Then
Debug.Print "Process completed successfully for column '" & ColumnNumberOrLetter & "' on '" & ColumnSheet.Name & "' with a value of '" & ColumnValue & "'."
Else
Debug.Print "Process incomplete for column '" & ColumnNumberOrLetter & "' on '" & ColumnSheet.Name & "'."
End If
End If
End Sub
Public Function GetColumnNumber( _
ByVal ColumnLetter As Variant _
) As Long
Dim Column As Range
If Not ValidColumnLetter(ColumnLetter) Then Exit Function
Set Column = ThisWorkbook.Worksheets(1).Cells(1, ColumnLetter)
GetColumnNumber = Column.Column
End Function
Public Function ValidColumnLetter( _
ByVal ColumnLetter As Variant _
) As Boolean
Dim Column As Range
On Error Resume Next
Set Column = ThisWorkbook.Worksheets(1).Cells(1, ColumnLetter)
ValidColumnLetter = Not Column Is Nothing
End Function
