PDA

View Full Version : Function LEFT / RIGHT



JJanower
12-12-2011, 06:42 AM
Hello dear Community

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

Worksheets("Sheet2").(Left(Cells(p, q)),5)

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:

With Worksheets("Sheet2")
Shift = Left(Cells(q, q), 5)
End With

but still is pulling the info from sheet1.

Any comments would be appreciated. :hi:

Aflatoon
12-12-2011, 06:43 AM
I think that you want:
Left(Worksheets("Sheet2").Cells(p, q), 5)

Bob Phillips
12-12-2011, 06:59 AM
Hello dear Community

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

Worksheets("Sheet2").(Left(Cells(p, q)),5)

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:

With Worksheets("Sheet2")
Shift = Left(Cells(q, q), 5)
End With

but still is pulling the info from sheet1.

Any comments would be appreciated. :hi:

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

JJanower
12-12-2011, 07:20 AM
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"

With Worksheets("Sheet2")
Shift = Left(.Cells(p, q), 5)
End With

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

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

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
12-12-2011, 07:34 AM
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

Aflatoon
12-12-2011, 07:35 AM
You might use CDate to convert to a real date/time (assuming Shift is declared appropriately):
CDate(Left(Worksheets("Sheet2").Cells(p, q), 5))

JJanower
12-12-2011, 07:58 AM
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


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



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

Bob Phillips
12-12-2011, 12:11 PM
Is that a question or a statement?