Use of Array in Macro

[Livin404]

Greetings, I have a Macro which works great, but I need to add the letters "S" and "L". I'm pretty sure it will require an array. Looked for possible example and could not come accross one.

The current macro works greaat moving the A, but I need it to move an S and L too if found in Column R. These letters will be by themselves in Column R.

Public Sub MoveLetters()
    Dim cell As Range
        Application.ScreenUpdating = False
        For Each cell In Range("R1:R" & Cells(Rows.Count, "R").End(xlUp).Row)
        If InStr(cell.Text, "A") > 0 Then
            cell.Offset(0, 1) = cell.Text
            cell.ClearContents
        End If
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

Thank you,

 

[Micron]

how about

For Each cell In Range("R1:R" & Cells(Rows.Count, "R").End(xlUp).Row)
    Select Case cell.Text
       Case "R","S","L"
         cell.Offset(0, 1) = cell.Text
         cell.ClearContents
        Case Else ' what to do if not R,S or L?
          'If nothing, can eliminate these two lines
       End Select
 Next cell

I'm assuming your cell contains the single letter. If not, then you're using Instr to find "A" in Apple?

 

[igold]

Using an array, which would eliminate going back and forth to the worksheet within your loop, you could try this...

Public Sub MoveLetters()
    Dim arr, i As Long
        Application.ScreenUpdating = False
        arr = Range("R1:S" & Cells(Rows.Count, "R").End(xlUp).Row)
        For i = 1 To UBound(arr)
            If InStr(arr(i, 1), "A") > 0 Then arr(i, 2) = "A"
            If InStr(arr(i, 1), "S") > 0 Then arr(i, 2) = "S"
            If InStr(arr(i, 1), "L") > 0 Then arr(i, 2) = "L"
            If Not arr(i, 2) = "" Then arr(i, 1) = ""
        Next
        Range("R1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
    Application.ScreenUpdating = True
End Sub

 

[igold]

Oops I used "A" instead of "R", so a correction and using a Case Statement which would be faster than using a For/Next loop should be the fastest...

Public Sub MoveLetters()
    Dim arr, i As Long
    arr = Range("R1:S" & Cells(Rows.Count, "R").End(xlUp).Row)
    For i = 1 To UBound(arr)
        Select Case arr(i, 1)
            Case "R", "S", "L"
                arr(i, 2) = arr(i, 1)
                arr(i, 1) = ""
        End Select
    Next
    Range("R1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

 

[Fluff]

Oops I used "A" instead of "R",

But the OP wanted A & not R.

 

[igold]

Nothing good ever happens when you rush...

Public Sub MoveLetters()
    Dim arr, i As Long
    arr = Range("R1:S" & Cells(Rows.Count, "R").End(xlUp).Row)
    For i = 1 To UBound(arr)
        Select Case arr(i, 1)
            Case "A", "S", "L"
                arr(i, 2) = arr(i, 1)
                arr(i, 1) = ""
        End Select
    Next
    Range("R1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

 

[Micron]

For i = 1 To UBound(arr)

Won't that miss first array member because arrays are 0 based?
Should it be For i = 0 to UBound(arr)-1

 

[Fluff]

Won't that miss first array member because arrays are 0 based?

No it wont because when you load an array direct from a sheet it will be 1 based, regardless of settings.

 

[Micron]

Learned something there. Thanks.

 

[Livin404]

how about

For Each cell In Range("R1:R" & Cells(Rows.Count, "R").End(xlUp).Row)
    Select Case cell.Text
       Case "R","S","L"
         cell.Offset(0, 1) = cell.Text
         cell.ClearContents
        Case Else ' what to do if not R,S or L?
          'If nothing, can eliminate these two lines
       End Select
 Next cell

I'm assuming your cell contains the single letter. If not, then you're using Instr to find "A" in Apple?

So many choices in the end I elected to go with yours. I have the others too if something goes wrong in the future. Many thanks!