using LEFT function in Excel VBA

[basb87]

Hi there,

I want to use the LEFT function in VBA. But it doesn't work and I cannot find the solution on internet.

Can you help, please?

here is my code:

Sub automatisch()
Dim landcode As String
If Range("A1").Value = 2 And Left(range.("B1").Value, 2) = "Ha" Then
    Range("C1").Value = "OK!"
        
    Else: Range("C1") = "A1 is not 2 and/or B1 doesn't start with 'Ha'."
    
End If
    
End Sub

 

[DushiPunda]

Why doesn't it work? If that is the exact copy of your code, then it's not working because of the period in between "range" and ("B1")...

Try this:

Sub automatisch()
Dim landcode As String
If Range("A1").Value = 2 And Left(Range("B1").Value, 2) = "Ha" Then
    Range("C1").Value = "OK!"
Else
    Range("C1") = "A1 is not 2 and/or B1 doesn't start with 'Ha'."
End If
End Sub

Note: as you can see in your code, "range" is not capitalized...if you ever type something without capitalizing it (and it should be capitalized), and it's in the correct format/syntax, then it will be automatically capitalized once you leave that line. If it doesn't auto-capitalize, then you know you've typed it wrong. Hopefully that makes sense.

Edit: Also, in case you wanted to get more specific with your C1 value:

Sub automatisch()
Dim landcode As String
If Range("A1").Value = 2 And Left(Range("B1").Value, 2) = "Ha" Then
    Range("C1").Value = "OK!"
Else
    If Range("A1").Value <> 2 And Left(Range("B1").Value, 2) <> "Ha" Then
        Range("C1").Value = "A1 is not 2 and B1 doesn't start with 'Ha'."
    ElseIf Range("A1").Value <> 2 Then
        Range("C1").Value = "A1 is not 2"
    ElseIf Left(Range("B1").Value, 2) <> "Ha" Then
        Range("C1").Value = "B1 doesn't start with 'Ha'."
    End If
End If
End Sub

 

[Scott T]

Try

Left(range("B1"), 2)

 

[basb87]

Thanks!

But now I also would like to apply this to the whole column, except the first row (that's a header). (the amount of rows differ from time to time)
So A2 needs to be empty and B2 needs to be "Ha" and then C3 wil be "OK!". The same counts for the 4th row and so on.

Is that possible?

Thanks in advance!!

 

[Scott T]

This finds the last row based on column A. If need to find the last row based on a different column then change 1 to the column number to use.

Sub automatisch()
Dim landcode As String
Dim lr As Long
lr = Cells(Rows.Count, [COLOR=#FF0000]1[/COLOR]).End(xlUp).Row
For i = 2 To lr
    If Range("A" & i).Value = 2 And VBA.left(Range("B" & i), 2) = "Ha" Then
        Range("C" & i).Value = "OK!"
        
        Else: Range("C" & i) = "A" & i & " is not 2 and/or B" & i & " doesn't start with 'Ha'."
        
    End If
Next I
End Sub