Using .Left with specific worksheet

[l_eonandr]

Please help.
I know this should be easy but having trouble getting this line to work;

P1 = Worksheets("Past-here").Range("B" & i).Left(cell("B" & i), 2)

I get a "type mismatch" error.

I need to check the data in a worksheet for several types of data, such as 100, 109, 400, 300 etc

I need to copy all the 100's to a sheet and all the 400's to a different sheet etc

I am using the .left to do that group selection. (note the data may be alphanumeric such as 40F)

Please help me with the correct syntax of this line of code.
I have tried several variations.
What am I missing here??

Much appreciate any help.

This is a larger picture of my part of my code;

Sub Bcopy() 'hot key is cntl + e


SrcLR = Application.CountA(Sheets("Past-here").Range("A:N"))
DestLR1 = Application.CountA(Sheets("Oil").Range("A:A"), -1)
DestLR2 = Application.CountA(Sheets("200").Range("A:A"), -1)
DestLR3 = Application.CountA(Sheets("300").Range("A:A"), -1)
DestLR4 = Application.CountA(Sheets("400").Range("A:A"), -1)



For i = 1 To SrcLR

P1 = Worksheets("Past-here").Range("B" & i).Left(cell("B" & i), 2)
'P = .Left(P1, 2)

If P = "10" Then
DestLR1 = DestLR1 + 1
Sheets("Past-here").Cells(i, 1).Cells.Copy Sheets("Oil").Cells(DestLR1, 1)
Sheets("Past-here").Cells(i, 2).Cells.Copy Sheets("Oil").Cells(DestLR1, 2)
Sheets("Past-here").Cells(i, 10).Cells.Copy Sheets("Oil").Cells(DestLR1, 3)
ElseIf P = "20" Then
DestLR1 = DestLR1 + 1
Sheets("Past-here").Cells(i, 1).Cells.Copy Sheets("200").Cells(DestLR2, 1)
Sheets("Past-here").Cells(i, 2).Cells.Copy Sheets("200").Cells(DestLR2, 2)
Sheets("Past-here").Cells(i, 10).Cells.Copy Sheets("200").Cells(DestLR2, 3)
ElseIf P = "30" Then
DestLR1 = DestLR1 + 1
Sheets("Past-here").Cells(i, 1).Cells.Copy Sheets("300").Cells(DestLR3, 1)
Sheets("Past-here").Cells(i, 2).Cells.Copy Sheets("300").Cells(DestLR3, 2)
Sheets("Past-here").Cells(i, 10).Cells.Copy Sheets("300").Cells(DestLR3, 3)
ElseIf P = "40" Then
DestLR1 = DestLR1 + 1
Sheets("Past-here").Cells(i, 1).Cells.Copy Sheets("400").Cells(DestLR4, 1)
Sheets("Past-here").Cells(i, 2).Cells.Copy Sheets("400").Cells(DestLR4, 2)
Sheets("Past-here").Cells(i, 10).Cells.Copy Sheets("400").Cells(DestLR4, 3)



End If
Next i
End Sub

 

[V_Malkoti]

Are you trying to read left 2 characters of the cells. If so then you need to use Left function of VBScript, not Range.Left property. Range.Left is a property which returns distance in points from left of column A to left of the Range. It does not give you leftmost characters.

P1 = Left(Worksheets("Past-here").Range("B" & i).value, 2)

 

[l_eonandr]

Thanks, that works, I thought I had tried that way back in the beginning but got errors, must have been something else wrong back then and I got confused about where I was at in the versions of the code.
Much appreciate your reply.
And to answer your question, yes I am trying to read the first 2 characters of the cells to determine where the data should go.
I still have other problems as well with my code but now that this line is working I can continue to troubleshoot the remainder of the application.
again, much thanks...