correct syntax for LEFT in vba

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

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
 
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
Click to expand...

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
 
Howard said:
What's the correct syntax [....]
Dim Celln As Range [....]
If Left(Celln, 2).Value = Range("B15").Value Then
Celln.Interior.ColorIndex = 3
End If
Click to expand...

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
 
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
Click to expand...

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

Regards,
Howard
 
Back
Top