VBA Bug Error

[VbaHell]

Hello all, I am using Excel 2007
I am trying to learn VBA which I find a challenge but also very interesting at the same time.
the code below keeps buging out on the Text to columns, also if there is a more efficient way to improve this that would be great, open to any help please


Sub UpdateData()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("E:E")
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited
.Value = Evaluate("if(row(" & .Address & "), text(" & .Address & ", ""mmmm""))")
End With
Columns("P:P").TextToColumns Destination:=Range("P1"), DataType:=xlDelimited
Range("AO1").FormulaR1C1 = "Site Name"
Range("AO2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-25],'Site Name'!C[-40]:C[-39],2,FALSE)"
Selection.AutoFill Destination:=Range("AO2:AO" & LR), Type:=xlFillDefault
Columns("AO:AO").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select

End Sub

 

[jim may]

You have 2 code line references to "Text to columns"; Which one causes the problem?
and What is the reported Error?

 

[VbaHell]

Jim, thank you for replying on this


This is the Runtime Error 1004 which is on this line of code
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited

 

[VbaHell]

Jim

The error also say's "No data selected to parse"

 

[xenou]

Range reference looks like it got lost in translation:

With Range("E:E")
[COLOR="Red"]Selection[/COLOR].TextToColumns Destination:=Range("E1"), DataType:=xlDelimited
.Value = Evaluate("if(row(" & .Address & "), text(" & .Address & ", ""mmmm""))")
End With

Try instead:

With Range("E:E")
.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited
.Value = Evaluate("if(row(" & .Address & "), text(" & .Address & ", ""mmmm""))")
End With

What's the IF(Row(),Text()) supposed to be doing?

 

[VbaHell]

I am attemping to turn the date format in a text value of the month IE: 2/3/2012 would be March if that makes sense

 

[xenou]

Hmm, so why not just format the dates to show as March? Why does the values have to be literal text?
ξ

 

[VbaHell]

I have some Countifs matching against the months, if I format these cells as the month it shows as the month but the cell value is still as the actual date. Also the dates in the report are formatted as 02/03/12 (2nd March 2012) and Excel sometimes get's confused as an american date format. (Feb 3rd 2012)
I am new to VBA so if you know a better way to solve this I would really appreciate your help

 

[Norie]

What are you using for the delimiter in Text to columns?

Or are you not using it to split a column of data out?

 

[VbaHell]

Hi Norie

I am not using it to split a column of data out. To be honest with you are I am grasping at straws with this. I think the text to columns isn't doing anything other than formatting the cell to 2/3/2012 instead of the existing value of 2/3/12