VBA Left 6 being very naughty.

[RockandGrohl]

Christ, me again, same topic.

Have a look at this:

If Cells(ActiveCell.Row, "C").Value = tnam And [B]Left(Cells(ActiveCell.Row, "A").Value, 6)[/B] <> tno And InStr(1, Cells(ActiveCell.Row, "K").Value, pup) > 0 Then

The left bit is giving me no end of grief.

So the fabled "tno" variable is "702228"

It goes down this huge list, finds the tnam, looks across at A and sees 702228, this is invalid so it goes down another row.

Now it sees tnam, same as before, looks across and sees 702228-F, but it's only supposed to look at the 6 characters to the left, which is 702228.


It found 702228 invalid before, but not when it's 702228-F cut down to 702228.


Could it be because 702228 is an integer and 702228-F cut down to 702228 is a string?

How would I rectify this?

And CLng(Left(Cells(ActiveCell.Row, "A").Value, 6))

That should do the trick, right (CInt gave overflow error)

 

[RoryA]

Why not make tno a String?

 

[RockandGrohl]

Why not make tno a String?

I just set tno as a string by doing CLng(variable) and it says "Type mismatch"

However I set it as Cstr and it worked, hooray :D Thanks.

 

[MARK858]

Just to state that the error
is because Clng is a number and Cstr is a text string