Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel Formula to Excel VBA

Posted on 2015-01-21
12
Medium Priority
?
137 Views
Last Modified: 2015-01-21
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))

Thanks in advance
0
Comment
Question by:DougDodge
  • 6
  • 5
12 Comments
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 40563359
These formula appear to work fine. What is the issue?

You could also use

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

instead of

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

Author Comment

by:DougDodge
ID: 40563415
Yes, they work fine, but I need it in VBA code as a Sub.
0
 
LVL 14

Expert Comment

by:ThomasMcA2
ID: 40563422
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:Michael Fowler
ID: 40563482
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, "/")

Open in new window


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

Open in new window


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

Author Comment

by:DougDodge
ID: 40563521
There was no sample workbook attached..... Sorry.

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

ThomasMcA2:
Your answers just give me run-time errors.
Run-time error 1004
Application-defined or object-defined error
So neither of them work.

Micheal74:
Your answers give me run-time errors as well.
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

by:Michael Fowler
ID: 40563527
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

by:DougDodge
ID: 40563550
There was no workbook posted.....
0
 
LVL 23

Accepted Solution

by:
Michael Fowler earned 2000 total points
ID: 40563557
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

Open in new window


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

Author Closing Comment

by:DougDodge
ID: 40563569
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

by:Michael Fowler
ID: 40563579
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

Open in new window

0
 

Author Comment

by:DougDodge
ID: 40563587
Willing to work on a loop now?

I am posting again......

"Loop Synchronization"
0
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 40563589
sure
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question