Working around quote marks in VBA

[Flexcel22]

Hi Everyone,

I am having trouble with inserting my formula that has quote marks. My formula below worked well when I manually inserted it in a cell on a worksheet but not in vba.

The code I worked with is this:

With ActiveCell
                      .Offset(0, 4) = Range("E5:E" & LastRow).Formula = "==IF(TIMEVALUE(LEFT(B5,LEN(B5)-2)&":"&RIGHT(B5,2))<TIME(14,0,0),TIMEVALUE(LEFT(B5,LEN(B5)-2)&":"&RIGHT(B5,2))+1-(14/24),TIMEVALUE(LEFT(B5,LEN(B5)-2)&":"&RIGHT(B5,2))-(14/24))"
           End With

I had tried double quote too but it didnt work or maybe there is something I missed.

Please help out as I am totally stuck.

 

[Flexcel22]

Sorry,

Something went wrong when I tried to post my thread so here is the complete formula that I want to insert in a cell.

=IF(TIMEVALUE(LEFT(B5,LEN(B5)-2)&":"&RIGHT(B5,2))<TIME(14,0,0),TIMEVALUE(LEFT(B5,LEN(B5)-2)&":"&RIGHT(B5,2))+1-(14/24),TIMEVALUE(LEFT(B5,LEN(B5)-2)&":"&RIGHT(B5,2))-(14/24))

 

[Flexcel22]

Here it is,

=IF(TIMEVALUE(LEFT(B5,LEN(B5)-2)&":"&RIGHT(B5,2))<TIME(14,0,0),TIMEVALUE(LEFT(B5,LEN(B5)-2)&":"&RIGHT(B5,2))+1-(14/24),TIMEVALUE(LEFT(B5,LEN(B5)-2)&":"&RIGHT(B5,2))-(14/24))

 

[Flexcel22]

It seems I cant copy the whole formula eventhough I did but it couldnt be shown. Only the first part of the formula is shown.

However, I do hope you guys gets what I meant by the double quoting issue that I encountered.

Please if you are able to understand my post.

 

[thisoldman]

To post your formula, insert a space before and after the less-than operator, the "<". Instead of

=RIGHT(B5,2))<​"LB",

type

=RIGHT(B5,2) < "LB".

I know one of the spaces I indicated isn't needed, but I'm too lazy to find out which one. The MrExcel BBS interpreter sometimes mistakes a less-than symbol for embedded HTML. Excel itself will properly interpret a formula with spaces surrounding the less-than symbol.

 

[MARK858]

The space before the < is the one not needed and it interprets it as a HTML tag when it is followed by a letter.
The full formula the OP is trying to post is

=IF(TIMEVALUE(LEFT(B5,LEN(B5)-2)&":"&RIGHT(B5,2))<TIME(14,0,0),TIMEVALUE(LEFT(B5,LEN(B5)-2)&":"&RIGHT(B5,2))+1-(14/24),TIMEVALUE(LEFT(B5,LEN(B5)-2)&":"&RIGHT(B5,2))-(14/24))

 

[MARK858]

The below seems to work for me

Sub xyz()
Dim LastRow as long
LastRow = Cells(Rows.count, "A").End(xlUp).Row
Range("E5:E" & LastRow). = "=IF(TIMEVALUE(LEFT(B5,LEN(B5)-2)&"":""&RIGHT(B5,2))<TIME(14,0,0),TIMEVALUE(LEFT(B5,LEN(B5)-2)&"":""&RIGHT(B5,2))+1-(14/24),TIMEVALUE(LEFT(B5,LEN(B5)-2)&"":""&RIGHT(B5,2))-(14/24))"
End Sub

I don't know what the

.Offset(0, 4) =

is there for in the first post.

 

[Flexcel22]

Thanks MARK858,

Everything is okay now.

It seems double quoting is not an issue but some part of my code maybe the cause of an error.

I had copied down your code into a new module and start inserting the formula by calling xyz and it worked with no more errors

Thanks again and thank you too to those who have contributed in this thread.