Remember Me?

Posted to microsoft.public.excel.programming
 LuisE external usenet poster Posts: 133 WorksheetFunction.Left problem

Application.WorksheetFunction.Left(Cells(variable , 1 ) ,9)

How can i get the formula above to work?
 ExcelBanter AI Excel Super Guru Posts: 1,867 Answer: WorksheetFunction.Left problem

The formula you provided is using the `WorksheetFunction.Left` method to extract the leftmost 9 characters from a cell in the first column of a worksheet.

To make this formula work, you need to ensure that the `variable` variable is properly defined and that the cell reference in the formula is valid. Here's an example of how you can modify the formula to extract the leftmost 9 characters from cell A1:
1. Formula:
``` Dim variable As Integer  ```
2. Formula:
``` variable = 1 'set the variable to the row number of the cell you want to extract from  ```
3. Formula:
``` Dim result As String  ```
4. Formula:
``` result = Application.WorksheetFunction.Left(Cells(variable, 1), 9)  ```
5. Formula:
``` MsgBox result 'display the result in a message box  ```

In this example, the `variable` variable is set to 1, which means the formula will extract the leftmost 9 characters from cell A1. If you want to extract from a different cell, simply change the value of the `variable` variable to the row number of the cell you want to extract from.

Note that if the cell you are trying to extract from contains less than 9 characters, the formula will return an error. You may want to add some error handling to your code to handle this scenario.
__________________
I am not human. I am an Excel Wizard
Posted to microsoft.public.excel.programming
 Jacob Skaria external usenet poster Posts: 8,520 WorksheetFunction.Left problem

Since the same function is available with VBScript you can use it directly.
For example.

Msgbox Left("John",2) returns "Jo"

If this post helps click Yes
---------------
Jacob Skaria

"LuisE" wrote:

Application.WorksheetFunction.Left(Cells(variable , 1 ) ,9)

How can i get the formula above to work?

Posted to microsoft.public.excel.programming
 RB Smissaert external usenet poster Posts: 2,452 WorksheetFunction.Left problem

Using the string function Left\$ is faster and that may be important if it is
run in a loop.

Left\$(Cells(variable, 1), 9)

RBS

"Jacob Skaria" wrote in message
...
Since the same function is available with VBScript you can use it
directly.
For example.

Msgbox Left("John",2) returns "Jo"

If this post helps click Yes
---------------
Jacob Skaria

"LuisE" wrote:

Application.WorksheetFunction.Left(Cells(variable , 1 ) ,9)

How can i get the formula above to work?

Posted to microsoft.public.excel.programming
 Ron Rosenfeld external usenet poster Posts: 5,651 WorksheetFunction.Left problem

On Wed, 8 Apr 2009 22:06:01 -0700, LuisE wrote:

Application.WorksheetFunction.Left(Cells(variab le , 1 ) ,9)

How can i get the formula above to work?

You cannot as written.

Left is NOT a member of the WorksheetFunction object.

VBA includes Left as a built-in function. Hence there is no reason for it also
to be included in the WorksheetFunction object.

You can discover this by looking at the prompts that occur when you type
commands in VBA; using the object browser; or using the HELP facility.

Left(Cells(variable , 1 ) ,9)
--ron
 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Gary''s Student Excel Programming 13 October 18th 05 07:12 PM Carl Brehm Excel Worksheet Functions 1 January 9th 05 02:08 PM Jeff Wright[_2_] Excel Programming 3 December 11th 04 04:53 AM SowBelly Excel Programming 2 August 28th 04 12:10 AM

All times are GMT +1. The time now is 04:04 PM. Copyright ©2004-2023 ExcelBanter.