Function LEFT / RIGHT

[JJanower]

Hello dear Community

I am looking for the correct syntax of what would be this code:

[VBA]Worksheets("Sheet2").(Left(Cells(p, q)),5)[/VBA]

I am trying to pull the first 5 digits from a cell in "sheet2" to my "sheet1", but it just wont work for me like that. I also tried something like this:

[VBA]With Worksheets("Sheet2")
Shift = Left(Cells(q, q), 5)
End With[/VBA]

but still is pulling the info from sheet1.

Any comments would be appreciated.

[Aflatoon]

I think that you want:
[vba]Left(Worksheets("Sheet2").Cells(p, q), 5)[/vba]

[Bob Phillips]

Hello dear Community

I am looking for the correct syntax of what would be this code:

[VBA]Worksheets("Sheet2").(Left(Cells(p, q)),5)[/VBA]

I am trying to pull the first 5 digits from a cell in "sheet2" to my "sheet1", but it just wont work for me like that. I also tried something like this:

[VBA]With Worksheets("Sheet2")
Shift = Left(Cells(q, q), 5)
End With[/VBA]

but still is pulling the info from sheet1.

Any comments would be appreciated.

In the second example, change Cells to .Cells, so that you access the worksheet identified in the With statement.

[JJanower]

thx alot it worked, but now it doenst works the way I intended it to work.

I have on one cell in "Sheet2" this information: "09:30-15:30"

so i was trying to pull the first 5 digits and then compare them with another cell from "Sheet3" that has this information time "11:00"
[VBA]
With Worksheets("Sheet2")
Shift = Left(.Cells(p, q), 5)
End With[/VBA]

So it will pull the time "09:30"
meaning Shift = 09:30

[vba]If Shift > Worksheets("Sheet3").Cells(p, q) Then 'Cells(p, q) is iin this case = 11:00
Worksheets("Report").Cells(j + 2, q + 1) = 0

Else: Worksheets("Report").Cells(j + 2, q + 1) = 1[/vba]

It always returns "0" even if i change or modify the time.
I suspect that when I pull the "09:30" with the left function, it is pulling it as TEXT and then I compare it to the "11:00", which has the format hh:mm.

Do you have any suggetions, that when i pull the "09:30" it is saved as hh:mm and not as text, in case that is the problem.

I tried something like this

Left(Worksheets("Sheet2").Cells(p, q), 5).value
but didnt work.

[Bob Phillips]

[vba]

Shift = Left(Worksheets("Sheet2").Cells(p, q), 5)
If Shift > Worksheets("Sheet3").Cells(p, q).Text Then

Worksheets("Report").Cells(j + 2, q + 1) = 0
Else

Worksheets("Report").Cells(j + 2, q + 1) = 1
End If
[/vba]

[Aflatoon]

You might use CDate to convert to a real date/time (assuming Shift is declared appropriately):
[vba]CDate(Left(Worksheets("Sheet2").Cells(p, q), 5))[/vba]

[JJanower]

[vba]

Shift = Left(Worksheets("Sheet2").Cells(p, q), 5)
If Shift > Worksheets("Sheet3").Cells(p, q).Text Then

Worksheets("Report").Cells(j + 2, q + 1) = 0
Else

Worksheets("Report").Cells(j + 2, q + 1) = 1
End If
[/vba]

it might be a problem when is change the code to :

[vba]

Shift = Left(Worksheets("Sheet2").Cells(p, q), 5)
If Shift > Worksheets("Sheet3").Cells(p, q).Text + Worksheets("Sheet3").Cells(p, q+1).Text Then

Worksheets("Report").Cells(j + 2, q + 1) = 0
Else

Worksheets("Report").Cells(j + 2, q + 1) = 1
End If
[/vba]

[Bob Phillips]

Is that a question or a statement?