Change variable based on search result

[BarefootPaul]

I want to search a column (F) to determine if a value matches a string and then change the string using sIRID = LEFT(value in cell in column F, 14) & "next letter in alphabet" - though could be next number if next letter doesn't work.

Another way to think about it is if the last character of the value is "a" then I want to set the last character of the string variable to be "b" and so on down the line.

Any help on this?

 

[JPM]

I am not sure if I understand correctly what you are asking, but if you are looking for a formula that would read the value in e.g. cell F5 which happens to be xxxxxxxxxxxxxxA and then post xxxxxxxxxxxxxxB, this formula might work for you:

=LEFT(F5,14)&CHAR(CODE(RIGHT(F5,1))+1)

 

[BarefootPaul]

I actually just finished working out something along those lines (I think its along those lines). Here is the code:

With ActiveWorkbook.Sheets("Incidents")
    Set rFindIRID = .Range("F:F").Find(sIRID, LookIn:=xlValues, LookAt:=xlWhole) 'searches for presence of sIRID with "a" at the end
    If rFindIRID Is Nothing Then 'Checks if sIRID value is already present in column F
        sIRID = sIRID 'Since the value is not present, sIRID is not changed
    ElseIf rFindIRID.Value = sIRID Then 'If sIRID is present Then
        If Right(sIRID, 1) = "a" Then 'Checks if last character is "a" Then
            sIRID = Left(sIRID, 14) & "b" 'Keeps 14 left characters and changes last character to "b"
            Set rFindIRID = .Range("F:F").Find(sIRID, LookIn:=xlValues, LookAt:=xlWhole) 'searches for presence of new sIRID with a "b" at the end
            If rFindIRID Is Nothing Then
                sIRID = sIRID
            ElseIf rFindIRID.Value = sIRID Then
                If Right(sIRID, 1) = "b" Then
                    sIRID = Left(sIRID, 14) & "c"
                    Set rFindIRID = .Range("F:F").Find(sIRID, LookIn:=xlValues, LookAt:=xlWhole) 'searches for presence of new sIRID with a "c" at the end
                    If rFindIRID Is Nothing Then
                        sIRID = sIRID
                    ElseIf rFindIRID.Value = sIRID Then
                        If Right(sIRID, 1) = "c" Then
                            sIRID = Left(sIRID, 14) & "d"
                            Set rFindIRID = .Range("F:F").Find(sIRID, LookIn:=xlValues, LookAt:=xlWhole)
                            If rFindIRID Is Nothing Then
                                sIRID = sIRID
                            ElseIf rFindIRID.Value = sIRID Then
                                If Right(sIRID, 1) = "d" Then
                                    sIRID = Left(sIRID, 14) & "e"
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If
End If
End With
ActiveCell.Offset(0, 5) = sIRID

This is done when updating the sheet "Incidents" from a userform. It works, but seems like there might be a better way. I am learning as I go and don't know how your code works. I looked at Char and Code in the Excel formulas and think I understand, though don't know where my computers character set resides.

 

[BarefootPaul]

I tried using your code by changing things to:

With ActiveWorkbook.Sheets("Incidents")
    Set rFindIRID = .Range("F:F").Find(sIRID, LookIn:=xlValues, LookAt:=xlWhole)
    If rFindIRID Is Nothing Then
        sIRID = sIRID 
    ElseIf rFindIRID.Value = sIRID Then 
        sIRID = Left(sIRID, 14) & Char(Code(Right(sIRID, 1))+1)
    End If
End With

It works if I used it as a formula on the table, but I need to be able to do it from within vba. I know that I have used functions in vba before, but I get an error on Code of "Sub or Function not defined" and can't remember how I have done that in the past.