• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using LEFT in macro

M

mediatx

Member
Hi Guys

I got two fields in different Sheets.
Sheet1.L2 contains Number & Text (0 Days; 1 Day; 2 Days; 21 Days;...)
The Reference Cell (ExecutionDays) only numbers

I Need to compare the two cells which is easy using =--LEFT(A1;FIND(" ";A1)-1) to only get the number value from Sheet1.A1. As I cant add any columns to the sheet I Need to do this in vba.
I am able to some very Basic comparison, but cant get it to run to meet m actual requirement as written above.

Can someone please share sample code how I can get the following code to work?
...
If Range("L" & cLine).Value <> bExecutionDays Then
...
 
Hi, mediatx!
Try this:
Code: 
    With Worksheets("Sheet1")
        If Val(.Left(Range("L" & cLine).Value), InStr(Range("L" & cLine).Value, " ") - 1) <> bExecutionDays Then
        End If
    End With
Regards!
 
thanks for taking the time to respond.
I am getting an error on Val, does Need to be declared?
Also: I am working with two worksheets, however, the corresponding variables are declared.

Any hint?
 
Hi ,

Let me understand your requirement in the following words :

You have two cells , one of which is called ExecutionDays , and the other which is referenced as Sheet1!L2.

ExecutionDays contains numbers 0 , 1 , 2 ,...., 21 and so on.

Sheet1!L2 contains text strings within which are the numbers 0 , 1 , 2 ,...., 21 , in the form 0 Days , 1 Day , 2 Days and so on.

You wish to compare the two cells to see if the numbers match e.g. 7 should match with 7 Days.

To get the numeric value from the text string , try out the following in the Immediate Window of your Visual Basic editor :

x = "7 Days"
?Val(x)
7

x = "0 Days"
?Val(x)
0

x = "21 Days"
?Val(x)
21

You can see that to get the numeric value from the text string , as long as the text string starts with the number , all you have to do is use the Val function.

To compare the numeric value in the named range ExecutionDays , with the text string containing a number in Sheet1!L2 , you can use the following statement :

If [ExecutionDays] = Val(Range("Sheet1!L2").Value) Then

Narayan
 
@SirJB7 & Narayan: Will something like the below help, we can add the match criteria as well, if acceptable?

Code: 
Sub mediatx()
Dim myarray As Variant
myarray = Array("1 Days", "2 Days", "3 Days", "4 Days", "5 Days", _
  "6 Days", "7 Days", "8 Days", "9 Days", "10 Days") 'change to the required values
For Each i In myarray
  r = Replace(i, " Days", "")
  Cells(1, 1).Value = r
Next i
End Sub
 
@Abhijeet R. Joshi
Hi!
I tested and worked fine to convert into numeric values, but isn't it a bit cumbersome?
The Val+Left or Val only are better choices, in my opinion.
Regards!
 
@Abhijeet R. Joshi
Hi!

Just as a tip, remember that you can place the cursor (keyboard, so click, don't hover with mouse) on any VBA reserved word and press F1 to access the built-in and online help.

Same as from within Excel if you enter in an empty cell "=XXX(" (unquoted) and press the "fx" button at the left of the edition bar text box, you'll be prompted with the function wizard which shows every argument, gives a brief description of the function and by the bottom left link lets you access the built-in or online help. It works for any function.

In the case of Val function, it just converts to a number the first numbers (if any) from a text string. If you type in the immediate window this:
?val("8"),val("8 days"),val("8days"),val("days8")
you'll get this:
8 8 8 0

Regards!
 
You must log in or register to reply here.
Back
Top