1
Sub Trim(sh,FirstCell,LastCell)
    Sheets(sh).select
    Range(FirstCell,LastCell) = [index(Upper(FirstCell,LastCell),)]
End Sub

I want to do something like this to make it dynamic. Calling this macro through other application where I'm giving input for sheet name first cell and last cell of the range.

Anurag Joshi
  • 21
  • 4
  • 5
    What is the problem. There is no question here. – Scott Craner May 09 '22 at 15:49
  • Also you need to note if `FirstCell` and `LastCell` are ranges or strings, because that will determine the answer. – Scott Craner May 09 '22 at 15:51
  • This is failing since range are declare through variable. If giving directly column name it is running successfully. – Anurag Joshi May 09 '22 at 15:53
  • Currently using it like this Sub Trim_columnV(sh) Sheets(sh).select Range("V:V") = [index(Trim(V:V),)] End Sub – Anurag Joshi May 09 '22 at 15:54
  • May I recommend reading [the documentation for the `UPPER` function?](https://support.microsoft.com/en-gb/office/upper-function-c11f29b3-d1a3-4537-8df6-04d0049963d6) (And/or [the VBA equivalent `UCase` function](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/ucase-function)?) Neither of them accept 2 arguments. – Chronocidal May 09 '22 at 15:55
  • That is calling Trim_columnV not Trim - how are you defining FirstCell and LastCell in your call - they are actually variants because you have not included a type – CHill60 May 09 '22 at 15:56
  • Please don't use `Trim` as the name of your subroutine; that shadows the [`Trim`](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/ltrim-rtrim-and-trim-functions) function. – BigBen May 09 '22 at 15:58

2 Answers2

2

One cannot use [] with variables it expects a string. Also ranges already have the Sheet in the variable and adding it is not needed:

Sub MyTrim(rng as Range)    
    rng.value = rng.Parent.Evaluate("index(Upper(" & rng.adress & "),)")
End Sub

Or use UCase:

Sub MyTrim(rng as Range)
    If rng.Cells.Count = 1 Then
        rng = UCase(rng)
        Exit Sub
    End If
    Dim RngArray() as Variant
    RngArray = rng

    Dim i as Long
    For i = 1 to Ubound(RngArray,1)
        Dim j as Long
        For j = 1 to Ubound(RngArray, 2)
            RngArray(i,j) = UCase(RngArray(i,j))
        Next j
    Next i
    rng = rngarray
End Sub
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • getting an exception for the first solution given Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH)) my input : {"A:A"} – Anurag Joshi May 09 '22 at 16:46
  • 1
    You would put `Worksheet("blah").Range("A:A")` you need to pass a range not a string. Change `Blah` to the worksheet name. – Scott Craner May 09 '22 at 17:23
2

Upper-Case a Column Using Evaluate

Sub UCaseColumnTEST()
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    UCaseColumn ws, "A1", "A20"
End Sub

Sub UCaseColumn( _
        ByVal ws As Worksheet, _
        ByVal FirstCellAddress As String, _
        ByVal LastCellAddress As String)
    With ws.Range(FirstCellAddress, LastCellAddress)
        .Value = ws.Evaluate("UPPER(" & .Address & ")")
    End With
End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • getting an exception Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH)) my input : {"datasheet","A","A"} – Anurag Joshi May 09 '22 at 16:45
  • For the workbook containing this code, `ThisWorkbook`, you could use something like the following one-liner: `UCaseColumn ThisWorkbook.Worksheets("DataSheet"), "A1", "A10"`. – VBasic2008 May 09 '22 at 20:40