VBA - Search string left, offset and remove found string

[Whaletacos]

Hello wise people of the internet.

I'm tinkering with a code in VBA. It's purpose is to look at each cell in a given range.

Part 1: Looking at the LEFT side of a string, it searches for "/56-Test1/" And it MUST be the first part of a string.
Example: "A/56-Test1/AAA" should be ignored while "/56-Test1/AAA" Should be formatted.

If the "If" condition is true, then it offsets a value, and removes the found part of the string, while leaving the rest intact.
Example: Before: "/56-Test1/AAA" Example after: "AAA" and then some offset value somewhere.



I am using the Len - to remove the found string, but is there an easier way of doing it? Ie. more secure, so I dont accidentally remove a letter too much?



Part 2: exactly the same as part 1, only this time from the RIGHT side of the same string

Example: "/56-Test1/AAAA/36-Test/"




Its part of a lot of ifs. But I've attached an example below.

'Part 1 
For Each rngSequence In rngSequence.Cells




            If Left(rngSequence, Len(rngSequence) + 8) Like "/56-Test1/*" Then
                    rngSequence.Offset(0, 4).Value = "5-TestOffset1"
                    rngSequence = Right(rngSequence, Len(rngSequence) - 10)


            ElseIf Left(rngSequence, Len(rngSequence) + 9) Like "/56-Test2/*" Then
                    rngSequence.Offset(0, 4).Value = "5-TestOffset2"
                    rngSequence = Right(rngSequence, Len(rngSequence) - 10)

'Stuff happens

'Part 2
 For Each rngSequence In rngSequence.Cells


                If rngSequence Like "*/36-Test1/" Then
                    rngSequence.Offset(0, 5).Value = "3-TetsOffset1"
                    rngSequence = Left(rngSequence, Len(rngSequence) - 9)

A big thanks to anybody who read this I hope some internet wizard is able to help

 

[Peter_SSs]

A big thanks to anybody who read this

I have read it so: "You are welcome".

However, I'm not sure what you actually want. Are you able to provide a small set of sample dummy data and the expected results and explain again in relation to that sample data?

 

[Whaletacos]

Sure

I usually have some 10-50 cells to process, and earlier in the code I do some range defining.
Some example data would look kinda like this


/56-Test1/AAAAAAAA/36-Test1/
/56-Test1/TTTTTTTTTTTTTTTTTT/36-Test1/
AAAATTTTTAAAA/36-Test1/
AA/56-Test1/ATTTAAATTT
/56-Test1/AATTTAAATTTAAATA


I want the code to look at each string.
If the very first part from the left contains exactly "/56-Test1/" Then it offsets a value a few cells left, and then removes the "/56-Test1/" from the string.

So looking at the example data it would look like this after processing:

AAAAAAAA/36-Test1/ - /56-TestOffset1/
TTTTTTTTTTTTTTTTTT/36-Test1/ - /56-TestOffset1/
AAAATTTTTAAAA/36-Test1/
AA/56-Test1/ATTTAAATTT
AATTTAAATTTAAATA - /56-TestOffset1/



This after doing this on all the cells, from the left side, I want it to do the same, only from the right side, hence removing and replacing the /36-Test1/ part of the cells

 

[Peter_SSs]

If the very first part from the left contains exactly "/56-Test1/" Then it offsets a value a few cells left, and then removes the "/56-Test1/" from the string.

What exactly do you mean by the blue part?

 

[Whaletacos]

It just offsets a value based on the found string, I added some code below to try to display it. Sorry for the confussion

The sub runs through some 15-20 if statements looking for the Various string parts. Like the X and Y test below.


My main concern is the first and last line of code below.

First concern is if the string does not start with "X", would it then ignore it?
As in, it should ignore the following string "GXGG", but process this one "XGGG"


Second concern is the removal of letters/characters.
Is there an easy way to remove the found string?

If string starts with "X" = True
Then cell offset by (0,4).value = "Y"
Replace only the starting "X" in search string with ""

The problem here is in the following string "XGGXG"
If the above statement is true, we can't do a search and replace on "X" as there are 2x's. So I am using the Len to remove the first letter.

If Left(rngSequence, Len(rngSequence) + 1) Like "X*" Then
                    rngSequence.Offset(0, 4).Value = "Y"
                    rngSequence = Right(rngSequence, Len(rngSequence) - 1)

 

[Peter_SSs]

I'm sorry, I haven't been able to spend time on the forum for a while and that will continue for a while longer.