I'm working on an Excel macro to parse a string, extracting one substring into a second cell and cutting down the original string. When I try to run the macro, I get an "object required" error on both of the ActiveCell.Text = [string] lines. I apologize for the variable names; it's a short program, and hopefully the comments will help. All help on resolving the issue is appreciated. Thanks in advance!
Dim a As String 'this will be the original string
Dim b As String 'this will be the LRU type string
Dim c As Integer 'this will be the return value of InStr(a,b) indicating where the type starts in string a
Dim d As Integer 'this will be the length of the type string, b
Dim e As Integer 'this will the length of a
a = ActiveCell.Text
b = "" 'initialize b to the empty string
c = 0 'initialize c to zero
e = Len(a)
While (c = 0) 'while we haven't found the correct LRU type yet
If b = "" Then 'keep changing b, looking for the correct LRU type
b = "PABTS" 'cycle through all of the possibilities, one by one
ElseIf b = "PABTS" Then
b = "ISP" 'This is an exhaustive list of all of the possibilities
ElseIf b = "ISP" Then 'for the type of LRU.
b = "OSP"
ElseIf b = "LM2" Then
b = "LM3"
ElseIf b = "LM3" Then
b = "POL"
Else: b = "" 'If it is not in the list above, then it will be a special case.
If b = "" Then
MsgBox ("Special case, LRU type not found.")
c = -1
c = InStr(a, b) 'find the start position c of substring b in superstring a
If c > 0 Then
d = Len(b)
ActiveCell.Text = Right(a, e - c - d) 'set the active cell to be the details of the LRU
ActiveCell.Offset(0, -1).Range("A1").Select 'shift the active cell one to the left, to LRU type
ActiveCell.Text = b 'set the LRU type cell to the type determined above