Macro to add text manipulated column wont run

[rlekkala]

I wanted to add a column next to existing column where all the cells in column P satisfy the condition
P2 = LEFT(O2,FIND(",",O2)+5)+0

Why does not my macro below fail with error "Sub or function not defined"?

Sub Go()
Dim a As Long, x As Long
x = Cells(Rows.Count, 15).End(xlUp).Row
For a = 2 To x + 1

Cells(a, 16) = Left(Cells(a, 15), Find(",", Cells(a, 15)) + 5) + 0
a = a + 1
Next a
MsgBox "Complete"
End Sub

 

[doofusboy]

Several things. First, you don't need "a = a + 1" inside a For loop as the For increments 'a' for you [unless you want 'a' incremented by 2 each time, in which case you could use "For a = 2 To x + 1 Step 2"].

Next, what line is highlighted as erroring?

Lastly, what is the purpose of the "+ 0"? You're adding nothing?

 

[rlekkala]

Several things. First, you don't need "a = a + 1" inside a For loop as the For increments 'a' for you [unless you want 'a' incremented by 2 each time, in which case you could use "For a = 2 To x + 1 Step 2"].

Next, what line is highlighted as erroring?

Lastly, what is the purpose of the "+ 0"? You're adding nothing?

Hi,
The reason for the 0 is to make the value into date format.
Excel 2007
A B
2 February 22, 2011 1:59:21 PM GMT-05:00 22-Feb-11
3 June 25, 2010 3:22:05 PM GMT-05:00 25-Jun-10
4 June 25, 2010 7:57:12 AM GMT-05:00 25-Jun-10
Sheet1


Worksheet Formulas
Cell Formula
B2 =LEFT(A2,FIND(",",A2)+5)+0

Once I use the above formula and drag the cells down, the column is ready to be formatted as date mmm-yy and it works and cold also be sorted in chronological order

The error shows on the Find, Compile error: Sub or Function not defined

 

[doofusboy]

the column is ready to be formatted as date mmm-yy

February 22, 2011 1:59:21 PM GMT-05:00 22-Feb-11
June 25, 2010 3:22:05 PM GMT-05:00 25-Jun-10
June 25, 2010 7:57:12 AM GMT-05:00 25-Jun-10


With the above data, if you are trying to get to mmm-yy, couldn't you use

Cells(a, 16) = RIGHT(Cells(a, 15), 6)

 

[rlekkala]

February 22, 2011 1:59:21 PM GMT-05:00 22-Feb-11
June 25, 2010 3:22:05 PM GMT-05:00 25-Jun-10
June 25, 2010 7:57:12 AM GMT-05:00 25-Jun-10


With the above data, if you are trying to get to mmm-yy, couldn't you use

Cells(a, 16) = RIGHT(Cells(a, 15), 6)

Sorry the values in cell A end with time (text)
The month, date and year you see in the pasted data are from cell B
Cell B is the calculated cell that can be formatted as date and incidentally it was formatted to show date when I pasted

 

[doofusboy]

Well then, could you try:

Cells(a, 16) = RIGHT(Cells(a, 2), 6)

 

[rlekkala]

Well, thats ending at the time, I need to strip the time off too, so I used -21 for offset instead of -16 (21 will remove the time even if hours are double digits)
But that failed

 

[rlekkala]

Well then, could you try:

Cells(a, 16) = RIGHT(Cells(a, 2), 6)

That did not work either
How about get the first three characters and then the year by coming back from right?

 

[rlekkala]

Well then, could you try:

Cells(a, 16) = RIGHT(Cells(a, 2), 6)

The values with both single and double digit hours were succesfully converted to Date format yyyy-mm
<TABLE style="WIDTH: 270pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=360><COLGROUP><COL style="WIDTH: 222pt; mso-width-source: userset; mso-width-alt: 10825" width=296><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 222pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=17 width=296>February 22, 2011 1:59:21 PM GMT-05:00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 width=64 align=right>2011-02</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 222pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=17 width=296>June 25, 2010 13:22:05 PM GMT-05:00</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right>2010-06</TD></TR></TBODY></TABLE>

I finally got it to work, your RIGHT() approach gave me some idea,
Sub Go()
Dim a As Long, x As Long
x = Cells(Rows.Count, 15).End(xlUp).Row
For a = 2 To x + 1
Cells(a, 16) = Left((Left(Cells(a, 15), 3) & " " & Right(Cells(a, 15), 26)), 9)
Next a
End Sub
Now I am waiting for help on the below
http://www.mrexcel.com/forum/showthread.php?t=540291