Trying to decipher a short bit of code in a macro

[zookeepertx]

Hello all!

I have a short question that is probably simple but I just can't see it.

I have a macro that works correctly; I just can't figure out why it does. Someone else wrote parts of the code for me; that's why I'm having trouble.
The data being evaluated is quite a number of rows; way too many to do manually.

In this example, the cell that the code is looking at shows this: Vendor ID: 21069,Vendor: 19 RM Limited USD
I need it to end up like this: 21069-19 RM Limited USD. It works but I can't tell how it does.
Here's the code for that portion of the macro:

For i = 1 To lr
    If LCase(Range("B" & i).Value) Like "*vendor id:*,*vendor:*" Then
        temp = Range("B" & i).Value
        vendor = Trim$(Mid$(temp, InStr(LCase(temp), "vendor id:") + Len("vendor id:"), InStr(temp, ",") _
                    - InStr(LCase(temp), "vendor id:") - Len("vendor id:")))
        vendor = vendor & "-" & Trim$(Mid$(temp, InStr(LCase(temp), "vendor:") + Len("vendor:")))
    End If
Next i

So, I've figured out the first part, that gets the first value of "vendor" (although I don't know what the $ does in there):
Trim$(Mid$(temp, InStr(LCase(temp), "vendor id:") + Len("vendor id:"), InStr(temp, ",")- InStr(LCase(temp), "vendor id:") - Len("vendor id:")))
temp = the text in the cell
InStr(LCase(temp), "vendor id:") + Len("vendor id:") = Finds the start number. Position of "vendor id:" which is 1, plus the length of "vendor id:" which is 11. This makes the start number = 12.
InStr(temp, ",")- InStr(LCase(temp), "vendor id:") - Len("vendor id:"))) = Number of characters. I'm not TOTALLY clear on this one. I know what each bit means, individually: first is the position of the comma, which is 17, then the position of "vendor id", which is 1, then the length of "vendor id:" which is 11.
It does give the correct answer = 5, but what I don't get is why we're subtracting each of those last 2 from the 1st one - position of the comma.
Anyway, given just the math, "vendor" = 21069. What gives me trouble isn't the math part, it's the verbiage part. This equates to a word problem to me and word problems are the absolute bane of my existence!! In fact, I've already spent about 45 minutes just trying to make this question make even a little bit of sense to you guys!

The next step is where I really get in trouble:
vendor = vendor & "-" & Trim$(Mid$(temp, InStr(LCase(temp), "vendor:") + Len("vendor:")))
vendor is obviously the 21069 found previously and we're adding a dash after it.
temp = still the text in the cell
InStr(LCase(temp), "vendor:") = Finds the start number, which is 17.
Len("vendor id:"))) = obviously the # of characters in "vendor id:" which is 10

First off, with the MID formula, doesn't there need to be a part of the formula giving the number of characters? I thought that was required!
Secondly, why is it adding together the 17 + 10? The correct # of characters to pull should be 17 - 19 RM Limited USD

When I run the macro, it gives the correct answer every time; I just want to know HOW it works, in case I need to borrow the code for another macro.

Anyway, if anyone is still reading this novel, I'd love it if you could tell me how/why this works, in plain english, like you're talking to an idiot, because sometimes I am.

 

[jolivanes]

Trim is the variant version. If you use it on a string, it will need to do an unnecessary conversion.
Trim$ is the string version. Use this if you are using it on a string.

Mid needs the amount of characters in a sheet environment (function in a sheet) but it does automatically take all characters if non are specified in VBA.

 

[Alex Blakenburg]

A lot of the added complexity is that the programmer wanted to cater for Vendor ID not being at the start of what is in the cell.
So instead of assuming your phrase Vendor ID: ....... starts at 1 he is recalculating the start position in each part.

The Trim is because the programmer catered for either having a space after the colon ":" and not having a space after the colon.
So in "Vendor ID: " the Mid includes the space in the part it extracts and the Trim removes the space.
If there was no space it would still work.

Len("vendor id:"))) = obviously the # of characters in "vendor id:" which is 10

Note: the above is meant to be "vendor:" NOT "vendor id:" and is a length of 7.

I have rearranged it slightly see if this helps. Don't spend too much time on it if it doesn't and we can try a different approach.

Sub TestPart()
        Dim temp As String, vendor As String
        temp = "Vendor ID: 21069,Vendor: 19 RM Limited USD"
      
        Dim IDStartPos As Long
        Dim NameStartPos As Long
      
        ' Find where vendor id appears in the cell
        ' then add the length of vendor id to get to advance the position to the end of "vendor id:"
        IDStartPos = InStr(LCase(temp), "vendor id:") _
                            + Len("vendor id:")
        ' Get mid of temp, starting at the IDStartPos and the no of characters
        ' is the position of the comma less the IDStartPos
        vendor = Trim$( _
                    Mid$( _
                        temp, _
                        IDStartPos, _
                        InStr(temp, ",") _
                            - IDStartPos))
                          
        ' Find where vendor appears in the cell
        ' then add the length of vendor to get to advance the position to the end of "vendor:"
        NameStartPos = InStr(LCase(temp), "vendor:") + Len("vendor:")
        ' Get the mid of temp, starting at the NameStartPos and by not giving a number of characters
        ' take form that position through to the end of the string
      
        ' If you used the right function which seems more logical you need to get the position counting from the right
        ' getting the position from the left is easier and mid without the no of characters parameter allows you to do that
        vendor = vendor & "-" & _
                    Trim$( _
                        Mid$( _
                            temp, _
                            NameStartPos))
        Debug.Print vendor                      ' Print to immediate window for testing
End Sub

 

[zookeepertx]

Trim is the variant version. If you use it on a string, it will need to do an unnecessary conversion.
Trim$ is the string version. Use this if you are using it on a string.

Mid needs the amount of characters in a sheet environment (function in a sheet) but it does automatically take all characters if non are specified in VBA.

Wow! I did not know that! Very good information to have. Thank you!

 

[zookeepertx]

A lot of the added complexity is that the programmer wanted to cater for Vendor ID not being at the start of what is in the cell.
So instead of assuming your phrase Vendor ID: ....... starts at 1 he is recalculating the start position in each part.

The Trim is because the programmer catered for either having a space after the colon ":" and not having a space after the colon.
So in "Vendor ID: " the Mid includes the space in the part it extracts and the Trim removes the space.
If there was no space it would still work.


Note: the above is meant to be "vendor:" NOT "vendor id:" and is a length of 7.

I have rearranged it slightly see if this helps. Don't spend too much time on it if it doesn't and we can try a different approach.

Sub TestPart()
        Dim temp As String, vendor As String
        temp = "Vendor ID: 21069,Vendor: 19 RM Limited USD"
    
        Dim IDStartPos As Long
        Dim NameStartPos As Long
    
        ' Find where vendor id appears in the cell
        ' then add the length of vendor id to get to advance the position to the end of "vendor id:"
        IDStartPos = InStr(LCase(temp), "vendor id:") _
                            + Len("vendor id:")
        ' Get mid of temp, starting at the IDStartPos and the no of characters
        ' is the position of the comma less the IDStartPos
        vendor = Trim$( _
                    Mid$( _
                        temp, _
                        IDStartPos, _
                        InStr(temp, ",") _
                            - IDStartPos))
                        
        ' Find where vendor appears in the cell
        ' then add the length of vendor to get to advance the position to the end of "vendor:"
        NameStartPos = InStr(LCase(temp), "vendor:") + Len("vendor:")
        ' Get the mid of temp, starting at the NameStartPos and by not giving a number of characters
        ' take form that position through to the end of the string
    
        ' If you used the right function which seems more logical you need to get the position counting from the right
        ' getting the position from the left is easier and mid without the no of characters parameter allows you to do that
        vendor = vendor & "-" & _
                    Trim$( _
                        Mid$( _
                            temp, _
                            NameStartPos))
        Debug.Print vendor                      ' Print to immediate window for testing
End Sub

Hi Alex.

I believe I just had an epiphany!! All this time I've been fussing over those dang spaces and was going to ask you a question to try to clarify. But before I did I reread your post more carefully and when I got to the phrase "Trim removes the space" the light bulb went onTrim removes the space! Trim removes the space! That's my mantra for today, LOL!
So, if I get this right:
IDStartPos = InStr(LCase(temp), "vendor id:") + Len("vendor id:") returns the number 11, which is the position of the space right after vendor id:
Then:
vendor = Trim$(Mid$(temp,IDStartPos,InStr(temp, ",") - IDStartPos)) finds the position of the comma, which is 17, then gets rid of the space, then subtracts the IDStartPos, which is 11

Do I have that right? I hope so, because it would make me so happy!

I saw a meme that explains my "word problem" problem perfectly: "Here's what word problems look like to me: If you have 3 apples and I have a pencil, how many pizzas will fit on the roof. The answer is, Purple, because aliens don't wear hats" First time I saw that it was like someone was inside my head because that's EXACTLY what they look like to me, LOL! I can do the math; it's just figuring out which part of each sentence goes where in the calculations.

Anyway, I hope I've finally gotten it through my skull.

Jenny

 

[Alex Blakenburg]

vendor = Trim$(Mid$(temp,IDStartPos,InStr(temp, ",") - IDStartPos)) finds the position of the comma, which is 17, then gets rid of the space, then subtracts the IDStartPos, which is 11

Subtracting the IDStartPos, which is 11 is still part of the mid statements. The Trim happens after that.

See if the below helps, after the mid you still have the space
ie Trim(mid())
mid(phrase, 11, 6) = " 21069"
Trim(" 21069") = "21069"

 

[zookeepertx]

Subtracting the IDStartPos, which is 11 is still part of the mid statements. The Trim happens after that.

See if the below helps, after the mid you still have the space
ie Trim(mid())
mid(phrase, 11, 6) = " 21069"
Trim(" 21069") = "21069"

View attachment 103833

AHA!! NOW I've got it! I had to study on it for a little bit but I got it. That helped me figure out the next formula in the macro, too. If you gave me a thousand years I could never have come up with this formula, but maybe I'll be able to use something like it in the future.

Thank you SO MUCH for your patience. Often I get fixated on one idea and can't seem to see the forest for the trees till someone shows me. You've been a huge help.

Have a great day!

Jenny

 

[Alex Blakenburg]

You too. Glad that worked for you. Thanks for letting us know.