Got a formula I would like to change into a macro.

**tripey** · 10-18-2012, 05:08 PM

So I got this formula that will look at a cell and delete the last string of characters after the last space.

=LEFT(B2,FIND("|",SUBSTITUTE(B2," ","|",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))))-1)

I don't completely understand as in how it's doing it, but I would like to write a macro to produce the result in Cell B2 and consecutive rows in Column B.

Any insight would be greatly appreciated.

Thanks

**Artik** · 10-18-2012, 05:19 PM

Like this:

Sub AAA()
  Dim LastRow As Long
  
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  
  Range("C2:C" & LastRow).Formula = "=LEFT(B2,FIND(""|"",SUBSTITUTE(B2,"" "",""|"",LEN(B2)-LEN(SUBSTITUTE(B2,"" "",""""))))-1)"
End Sub

Artik

**tigeravatar** · 10-18-2012, 05:30 PM

tripey,

Welcome to the forum!
Here's an alternate method that puts the result as a value into the cells in column B directly:

Sub tgr()
    
    With Range("B2", Cells(Rows.Count, "B").End(xlUp))
        If .Row < 2 Then Exit Sub   'No data
        .Value = Evaluate("Index(Left(" & .Address & ",FIND(""|"",SUBSTITUTE(" & .Address & ","" "",""|"",LEN(B" & .Row & ")-LEN(SUBSTITUTE(" & .Address & ","" "",""""))))-1),)")
    End With
    
End Sub

**tripey** · 10-19-2012, 08:16 AM

Awesome! I'm gonna check these out.

**tripey** · 10-21-2012, 09:16 PM

@ tigeravatar

I couldn't get your code to work tiger, when I ran it... I ended with some cells that had #VALUE! in them. I ended up using Artik's code and then cut and paste into column B. The excel file is done but I'm curious to see if I can get this to work with tiger's code.

From what I can understand:

This is dictating which column in the range. Count the rows in B? End(xlUp) - Does this mean going up from the end of counted rows?

   
With Range("B2", Cells(Rows.Count, "B").End(xlUp))
    
If Range.Row is less than 2, exit routine right?  If the rows counted in the range, which starts from B2, is less than 2... then exit.

If .Row < 2 Then Exit Sub   'No data

Not sure about this next one at all.
Range.Value = Evaluate the Index starting from the left? and the Range.Address? (Tried googling Address, don't know what this is), and FIND the cell border line "|".
Substitute whatever Range.Address is with (","" "", <---- Not sure what is this. Spaces?) and "|" cell border line, Length of characters in Cell Column B .Row MINUS Length of ... uh I don't know.. Whatever that was Substituted and the .Address and spaces MINUS 1 space at the end.

        .Value = Evaluate("Index(Left(" & .Address & ",FIND(""|"",SUBSTITUTE(" & .Address & ","" "",""|"",LEN(B" & .Row & ")-LEN(SUBSTITUTE(" & .Address & ","" "",""""))))-1),)")
    End With

I kind of get it, but as you can see... not really. Heh. I'd like to be able to dissect and completely understand it so I can try to write some code myself.

Any insight would be greatly appreciated

**tigeravatar** · 10-22-2012, 10:49 AM

tripey,

When I tested the code, it ran just fine on my test file. I'd have to see an example file that is experiencing the problem.
As for the Evalue method, all it does is interpret a text string as if it were an Excel Formula. The .Address is a replacement for the cell reference B2 from your original post, but other than that, it uses the exact formula you provided in your original post. .Address just makes it so that it works on the defined range instead of just 1 cell.