Advertisement
Guest User

Untitled

a guest
Jun 25th, 2019
64
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.06 KB | None | 0 0
  1. Sub RenameColumns(SheetName As String, ParamArray arrColumns() As Variant)
  2. Dim element As Variant
  3. Dim Counter As Integer
  4. Counter = 8
  5. For Each element In arrColumns
  6. If Counter >= 16 Then Exit For
  7. 'Error Type Mismatch occurs in the below line. CsStr(element) = <Type Mismatch >
  8. Range(GetColumnAddress(CStr(element), SheetName)).Value = arrColumns(Counter) 'Here I am getting error Type Mismatch
  9. Counter = Counter + 1
  10. Next element
  11. End Sub
  12.  
  13.  
  14. Function GetColumnAddress(ColumnName As String, SheetName As String)
  15. Dim strSearch As String
  16. Dim aCell As Range
  17.  
  18. Dim sheet As Worksheet
  19. Set sheet = Sheets(SheetName)
  20.  
  21. strSearch = ColumnName
  22.  
  23. Set aCell = sheet.Rows(1).Find(What:=strSearch, LookIn:=xlValues, _
  24. LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  25. MatchCase:=False, SearchFormat:=False)
  26.  
  27. If Not aCell Is Nothing Then
  28. GetColumnAddress = aCell.Address
  29. Else
  30. GetColumnAddress = "$A$1" 'Error!
  31. End If
  32. End Function
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement