About Us

ryguy7272

Thanks so much Sheeloo!!!

Regards,
Ryan---

--
RyGuy

"Sheeloo" wrote:

Try this (I tested and it worked)
As I had said earlier 'c' was not defined or set to anything...
Also you were not selecting the Cells(i,1) so ActiveCell was not set to
Cell(i,1)
Do take care of continuation character "_"
Sub TtoC()
'Dim c As Variant
Dim LastRow As Long
Dim i As Long
ActiveSheet.UsedRange.Rows.Hidden = False
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = LastRow To 2 Step -1
If InStr(1, Cells(i, 1), "Relative dose", vbTextCompare) Then
Cells(i, 1).Select
'ActiveCell.Select
Selection.TextToColumns Destination:=Cells(i, 1), DataType:=xlDelimited _
, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True
End If

Next
End Sub

"ryguy7272" wrote:

I made a slight modification, but it still is not working. I am now using
this code:

Sub TtoC()
'Dim c As Variant
Dim LastRow As Long
Dim i As Long
ActiveSheet.UsedRange.Rows.Hidden = False
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = LastRow To 2 Step -1
If InStr(1, Cells(i, 1), "Relative dose", vbTextCompare) Then

ActiveCell.Select
Selection.TextToColumns Destination:=Range(c), DataType:=xlDelimited _
, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)),
TrailingMinusNumbers:=True
End If

Next
End Sub

It fails on this line:
Selection.TextToColumns Destination:=Range(c), DataType:=xlDelimited _

Any suggestions or do I need to reconsider the method of my approach?

Thanks,
Ryan---

--
RyGuy

RyGuy

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell	Steve Kay	Excel Discussion (Misc queries)	2	August 8th 08 01:54 AM
Question on determining "ROW" inside of a "For .. RANGE " loop	David Schrader[_2_]	Excel Programming	2	January 3rd 07 08:18 PM
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next	BCB	New Users to Excel	7	May 13th 06 10:02 PM
LOOP BETWEEN "FRONT" AND "END" SHEETS?	ewan7279	Excel Programming	7	March 17th 05 03:11 PM
Change "relative" to "absolute" (database)	Len Dolby[_2_]	Excel Programming	4	November 3rd 03 05:36 PM

Menu

About Us