Solved

# Excel Formula to Excel VBA

Posted on 2015-01-21
128 Views
I am trying to figure out how to write the two following formula strings to be  used in Excel VBA.

1) =LEFT(A1,FIND("/",A1)-1)
2) =RIGHT(A1,LEN(A1)-FIND("/",A1))

0
Question by:DougDodge
• 6
• 5

LVL 23

Expert Comment

These formula appear to work fine. What is the issue?

You could also use

=MID(A1, FIND("/", A1)+1, LEN(A1))

=RIGHT(A1,LEN(A1)-FIND("/",A1))
0

Author Comment

Yes, they work fine, but I need it in VBA code as a Sub.
0

LVL 14

Expert Comment

There's nothing wrong with using both Excel formulas and VBA to provide a solution. What matters most in the real world is results. The exception to that is when it takes too long to get those results. "Solutions" that work, albeit slowly, get redesigned every day. Only academia requires a specific solution.

It is so common to combine Excel formulas with VBA that VBA can be used to assign a formula to a cell, or even a range of cells, via code like this: Cells("A1").Formula = "=Left(A1,10)"

Now to answer your question: VBA uses "InStr" to search within strings. The equivalent of FIND("/",A1) is InStr(Cells("A1").Value, "/"). Since the syntax for Right() and Left() are the same for both VBA and Excel formulas, here are your VBA equivalents:

=LEFT(A1,FIND("/",A1)-1) . . . . . Left(Cells("A1").Value, InStr(Cells("A1").Value, "/") - 1)
=RIGHT(A1,LEN(A1)-FIND("/",A1)) . . . . . . Right(Cells("A1").Value, Len(Cells("A1").Value) - InStr(Cells("A1").Value, "/"))

Tom
0

LVL 23

Expert Comment

You can use Excel worksheet functions directly in VBA using Application.WorksheetFunction
https://msdn.microsoft.com/en-us/library/office/aa221602(v=office.11).aspx

So in VBA both of these give the same result

``````slashIndex = Application.WorksheetFunction.Find("/", Range("A1"))
slashIndex = InStr(Range("A1").Value, "/")
``````

but the Functions Left and Right are not supported under this format and you use them directly

As an example of doing this in VBA which creates a custom function which you can call like Left or Right you could use

``````Function LeftOfSlash(rng As Range) As String
Dim slashIndex As Integer

slashIndex = InStr(rng.Value, "/")

LeftOfSlash = Left(rng.Value, slashIndex - 1)

End Function

Function RightOfSlash(rng As Range) As String
Dim slashIndex As Integer

slashIndex = InStr(rng.Value, "/")

RightOfSlash = Right(rng.Value, Len(rng.Value) - slashIndex)

End Function
``````

I have attached a sample workbook. ALt+F11 to open the VBA editor
0

Author Comment

There was no sample workbook attached..... Sorry.

Again, I don't need a Function..... It has to be written into a Subroutine....

ThomasMcA2:
Run-time error 1004
Application-defined or object-defined error
So neither of them work.

Micheal74:
Run-time error 1004
Unable to get the FIND property of the worksheet function class
So none of those work.

I did manage to search around and found a script for the RIGHT that works.

Mid(Range("Z4").Value, InStr(1, Range("Z4").Value, "/") + 1)

Still need one for the left.

When you get a spreadsheet populated with 21/216, 17/43, etc. etc. and need to split them into 2 columns, one for the numerator and one for the denominator you need to use a Sub and not a Function.
The worksheet gets changed out daily, and I am not going to rebuild a bunch of formulas or functions everyday.
0

LVL 23

Expert Comment

Not sure what is happening will have another look soon but in the meantime did you look at example workbook I posted.
0

Author Comment

There was no workbook posted.....
0

LVL 23

Accepted Solution

Michael74 earned 500 total points
Here are the functions converted to Subs

``````Sub LeftOfSlashSub()

Range("D1").Value = Left(Range("A1").Value, InStr(Range("A1").Value, "/") - 1)

End Sub

Sub RightOfSlashSub()

Range("E1").Value = Right(Range("A1").Value, Len(Range("A1").Value) - InStr(Range("A1").Value, "/"))

End Sub
``````

And I have attached a working example with both Functions and Subs
Example.xlsm
0

Author Closing Comment

Greetings Micheal74:

After much digging I found the exact same answer as you did for the LEFT.

I do prefer your solution for the RIGHT.

I can't believe I spent all afternoon on this silly thing.....

Thanks for all the effort.....
0

LVL 23

Expert Comment

Just a final thought. When you are new to programming it can make it easier to read your code if you break up your method calls and store the results in variables. It makes the code verbose but it is far easier to read and maintain. Once you have gotten the hang of the syntax then this is no longer necessary.
For eaxmple
``````Sub LeftOfSlashSub()
Dim InputStr as String
Dim slashIndex as Integer

Set InputStr = Range("A1").Value

slashIndex  = InStr(InputStr , "/")

Range("D1").Value = Left(InputStr , slashIndex  - 1)

End Sub
``````
0

Author Comment

Willing to work on a loop now?

I am posting again......

"Loop Synchronization"
0

LVL 23

Expert Comment

sure
0

## Featured Post

Photo Albums in PowerPoint Photo Albums are a very useful tool in PowerPoint and allow you quickly add a large number of images. The images can be formatted in a variety of ways so that you are able to create a professional looking presentation v…
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…