Trim and compare text via VBA macro

[lkrznchc]

I am trying to write a macro that filters a list based on criteria in a particular column and transfers the information to another sheet.

There is a problem with the line

xmitter_type = Application.Right(Trim(Cells(j + 2, 5)), 1)

within the overall procedure of:

Dim cell As Range
    Dim num_xmitters As Integer
    Dim xmitter_type As String
    Dim TransmitterArray(1 To 4, 1 To 200) As Range
    
    num_xmitters = ThisWorkbook.Worksheets("Instr List").UsedRange.Rows.Count
    
    Dim j As Integer
    
    For j = 1 To num_xmitters
        xmitter_type = Application.Right(Trim(Cells(j + 2, 5)), 1)
        Select Case xmitter_type
            Case "A"
                ThisWorkbook.Worksheets("Instr Sort").TransmitterArray(1, j).Value = ThisWorkbook.Worksheets("Instr List").Cells(j + 2, 2).Value
                
            Case "I"
                ThisWorkbook.Worksheets("Instr Sort").TransmitterArray(2, j).Value = ThisWorkbook.Worksheets("Instr List").Cells(j + 2, 2).Value
            
            Case "O"
                ThisWorkbook.Worksheets("Instr Sort").TransmitterArray(3, j).Value = ThisWorkbook.Worksheets("Instr List").Cells(j + 2, 2).Value
            
            Case "F"
                ThisWorkbook.Worksheets("Instr Sort").TransmitterArray(4, j).Value = ThisWorkbook.Worksheets("Instr List").Cells(j + 2, 2).Value
        End Select
        Next j

Is there a better way to extract the last character of these cells and compare on a 4 case scenario?

 

[Jonmo1]

Welcome to the board..

Remove the Application. from it..
VBA has it's own built in RIGHT function

so it should just be
xmitter_type = Right(Trim(Cells(j + 2, 5)), 1)

 

[lkrznchc]

I realized that I need the reference cells(j+2,5)),1) to reference a particular sheet. I am trying to use a dynamic array to accomplish this but is not working out as I expected.

 Dim cell As Range
    Dim num_xmitters As Integer
    Dim xmitter_type As String
    Dim TransmitterArray(1 To 4, 1 To 200) As Range
    Dim units() As Integer
    Dim j As Integer
    
    num_xmitters = ThisWorkbook.Worksheets("Instr List").UsedRange.Rows.Count
    units = ThisWorkbook.Worksheets.Range()
    
    For j = 1 To num_xmitters
        ReDim units(j + 2, 5)
        xmitter_type = Right(Trim(units()), 1)
        Select Case xmitter_type
            Case "A"
                ThisWorkbook.Worksheets("Instr Sort").TransmitterArray(1, j).Value = ThisWorkbook.Worksheets("Instr List").Cells(j + 2, 2).Value
                
            Case "I"
                ThisWorkbook.Worksheets("Instr Sort").TransmitterArray(2, j).Value = ThisWorkbook.Worksheets("Instr List").Cells(j + 2, 2).Value
            
            Case "O"
                ThisWorkbook.Worksheets("Instr Sort").TransmitterArray(3, j).Value = ThisWorkbook.Worksheets("Instr List").Cells(j + 2, 2).Value
            
            Case "F"
                ThisWorkbook.Worksheets("Instr Sort").TransmitterArray(4, j).Value = ThisWorkbook.Worksheets("Instr List").Cells(j + 2, 2).Value
        End Select
        Next j

 

[lkrznchc]

I am getting a mismatch error with the same line I had issues with before.

xmitter_type = Right(Trim(units()), 1)

I also deleted the definition line with unit()=thisworkbook.worksheets("instr list").range(" ")
how do i get that range to reference that particular column in the workbook?