In Microsoft Visual Basic, My code is not outpting anything

user_17357513 0
Viewed 34

To put this into context, I am extremely new to programming. I only have a basic understanding of Python, never touched VBA before this week, and rarely used excel. I know I it off way more than I can chew, but I am trying my best to learn. In my excel spreadsheet, I have a column E, and in each cell, I have a random string of either R, P, or S. I am trying to sift through each cell in this column, and for each cell, I am trying to identify anytime "R" occurs and print the next character in a separate cell. My goal is for each cell in the E column to have a correlating cell in the N column. Whenever the program runs, nothing happens in my spreadsheet.

Dim j As Integer
Dim i As Integer
Dim nextr As String

Sub Rvalue(i As Integer, nextr As String, j As Integer, cell As Variant)
For Each cell In Range("E2:E87")
    For i = 1 To Len(Text)
        If Mid((E2), i, 1) = "R" Then
            j = i + 1
            nextr = nextr & Mid((E2), j, 1)
        End If
    ActiveCell.Value = nextr
    Next i
Next cell
End Sub
vba excel
1 Answers

Chars After Char

  • With the current setup, it will loop through the cells of the one-column range E2:E87, and from each cell, it will extract the subsequent characters of each found R or r and return them in the same row of column N, e.g. if the cell in column E contains rAReRIr it will return AeI in column N.
Option Explicit

Sub CharsAfterChar()
    Dim ws As Worksheet: Set ws = ActiveSheet
    PopulateCharsAfterChar "R", ws.Range("E2:E87"), "N"
End Sub

Sub PopulateCharsAfterChar( _
        ByVal Char As String, _
        ByVal ColumnRange As Range, _
        ByVal Col As String)
    
    Dim cell As Range
    Dim n As Long
    Dim S As String
    Dim Chars As String
    
    For Each cell In ColumnRange.Columns(1).Cells
        S = CStr(cell.Value)
        For n = 1 To Len(S) - 1
            If StrComp(Mid(S, n, 1), Char, vbTextCompare) = 0 Then
                Chars = Chars & Mid(S, n + 1, 1)
            End If
        Next n
        cell.EntireRow.Columns(Col).Value = Chars
        Chars = ""
    Next cell

End Sub
user_98140690
Related