correct syntax for LEFT in vba
 

Excel 2010
These two lines of code from Help work fine to return "Alp".

Dim LResult As String
LResult = Left("Alphabet",3)

What's the correct syntax to get my IF - THEN line of code to look at each Celln in range NumToColor and if the Left two characters in Celln = Range("B15") then apply ColorIndex 3.

Dim Celln As Range
Dim NumToColor As Range
Set NumToColor = Range("D14:M23")

For Each Celln In NumToColor
Celln.Interior.ColorIndex = xlNone
If Left(Celln, 2).Value = Range("B15").Value Then
Celln.Interior.ColorIndex = 3
End If
Next

Thanks.
Regards,
Howard

correct syntax for LEFT in vba
 

On Sunday, September 30, 2012 9:41:44 PM UTC-7, Howard wrote:
Excel 2010

These two lines of code from Help work fine to return "Alp".



Dim LResult As String

LResult = Left("Alphabet",3)



What's the correct syntax to get my IF - THEN line of code to look at each Celln in range NumToColor and if the Left two characters in Celln = Range("B15") then apply ColorIndex 3.



Dim Celln As Range

Dim NumToColor As Range

Set NumToColor = Range("D14:M23")



For Each Celln In NumToColor

Celln.Interior.ColorIndex = xlNone

If Left(Celln, 2).Value = Range("B15").Value Then

Celln.Interior.ColorIndex = 3

End If

Next



Thanks.

Regards,

Howard

Bingo, I got it to work with this.

Dim LResult As String
For Each Celln In NumToColor
Celln.Interior.ColorIndex = xlNone
LResult = Left(Celln, 2)
If LResult = Range("B15").Value Then
Celln.Interior.ColorIndex = 3
End If
Next

Regards,
Howard

correct syntax for LEFT in vba
 

"Howard" wrote:
What's the correct syntax
[....]
Dim Celln As Range
[....]
If Left(Celln, 2).Value = Range("B15").Value Then
Celln.Interior.ColorIndex = 3
End If

It is sufficient to write:

If Left(Celln, 2) = Range("B15") Then
Celln.Interior.ColorIndex = 3
End If

But if you feel better using .Value explicitly, then:

If Left(Celln.Value, 2) = Range("B15").Value Then
Celln.Interior.ColorIndex = 3
End If

correct syntax for LEFT in vba
 

On Sunday, September 30, 2012 9:41:44 PM UTC-7, Howard wrote:
Excel 2010

These two lines of code from Help work fine to return "Alp".



Dim LResult As String

LResult = Left("Alphabet",3)



What's the correct syntax to get my IF - THEN line of code to look at each Celln in range NumToColor and if the Left two characters in Celln = Range("B15") then apply ColorIndex 3.



Dim Celln As Range

Dim NumToColor As Range

Set NumToColor = Range("D14:M23")



For Each Celln In NumToColor

Celln.Interior.ColorIndex = xlNone

If Left(Celln, 2).Value = Range("B15").Value Then

Celln.Interior.ColorIndex = 3

End If

Next



Thanks.

Regards,

Howard

Hi joeu,
Good info, thanks a lot, I appreciate it.

Regards,
Howard