For Next Loop and If..."Relative dose*"...Then

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

I am trying to select cells in a used range and perform a ‘text to column’
change on all cells that fall into this used range. I have about 20
occurrences of this: ‘Relative dose [%]’ with some blanks in the cell right
after this language, and then some, well, basically random variables, after
that. Ideally, I’d like to identify the value in each cell in the used
range, and if the value in the cell is ‘Relative dose [%]’ then perform the
‘text to column’ operation on the next cell below that, and run until Excel
finds a blank cell, and then search again for ‘Relative dose [%]’, and so on
and so forth until Excel reaches the end of the used range. My code is below:

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

cell.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

Next
End Sub

It fails on the next line. Can someone tell me what is wrong here?

Thanks,
Ryan---
 
End If! Of course. it was late when I started this things last night. Now,
with the End If in the appropriate spot, the macro still doesn't do what I
wanted it to do. As I F8 through the code I notice that it seems to go form
the If... to the End if... to Next... and goes through this cycle over and
over. I guess there is something wrong with the IF statement, but I'm not
sure what it is. Can someone point out the problem.

TIA,
Ryan---



--
RyGuy


Sheeloo said:
Also what is c in Range(c)? Named range?


ryguy7272 said:
I am trying to select cells in a used range and perform a ‘text to column’
change on all cells that fall into this used range. I have about 20
occurrences of this: ‘Relative dose [%]’ with some blanks in the cell right
after this language, and then some, well, basically random variables, after
that. Ideally, I’d like to identify the value in each cell in the used
range, and if the value in the cell is ‘Relative dose [%]’ then perform the
‘text to column’ operation on the next cell below that, and run until Excel
finds a blank cell, and then search again for ‘Relative dose [%]’, and so on
and so forth until Excel reaches the end of the used range. My code is below:

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

cell.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

Next
End Sub

It fails on the next line. Can someone tell me what is wrong here?

Thanks,
Ryan---
Click to expand...
Click to expand...
 
Remove the * from the following line;
If InStr(1, Cells(i, 1), "Relative dose*", vbTextCompare) Then

ryguy7272 said:
End If! Of course. it was late when I started this things last night. Now,
with the End If in the appropriate spot, the macro still doesn't do what I
wanted it to do. As I F8 through the code I notice that it seems to go form
the If... to the End if... to Next... and goes through this cycle over and
over. I guess there is something wrong with the IF statement, but I'm not
sure what it is. Can someone point out the problem.

TIA,
Ryan---



--
RyGuy


Sheeloo said:
Also what is c in Range(c)? Named range?


ryguy7272 said:
I am trying to select cells in a used range and perform a ‘text to column’
change on all cells that fall into this used range. I have about 20
occurrences of this: ‘Relative dose [%]’ with some blanks in the cell right
after this language, and then some, well, basically random variables, after
that. Ideally, I’d like to identify the value in each cell in the used
range, and if the value in the cell is ‘Relative dose [%]’ then perform the
‘text to column’ operation on the next cell below that, and run until Excel
finds a blank cell, and then search again for ‘Relative dose [%]’, and so on
and so forth until Excel reaches the end of the used range. My code is below:

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

cell.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

Next
End Sub

It fails on the next line. Can someone tell me what is wrong here?

Thanks,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
Yes, that would work, but there is some stuff after that in some cells. For
instance, one cell has this:
Relative dose [%] Dose [Gy] Ratio of Total Structure Volume [%]
Many cells are different; that's why I tried the * wildcard, after the
'Relative dose'. Any more suggestions?

Thanks,
Ryan---


--
RyGuy


Sheeloo said:
Remove the * from the following line;
If InStr(1, Cells(i, 1), "Relative dose*", vbTextCompare) Then

ryguy7272 said:
End If! Of course. it was late when I started this things last night. Now,
with the End If in the appropriate spot, the macro still doesn't do what I
wanted it to do. As I F8 through the code I notice that it seems to go form
the If... to the End if... to Next... and goes through this cycle over and
over. I guess there is something wrong with the IF statement, but I'm not
sure what it is. Can someone point out the problem.

TIA,
Ryan---



--
RyGuy


Sheeloo said:
Also what is c in Range(c)? Named range?


:

I am trying to select cells in a used range and perform a ‘text to column’
change on all cells that fall into this used range. I have about 20
occurrences of this: ‘Relative dose [%]’ with some blanks in the cell right
after this language, and then some, well, basically random variables, after
that. Ideally, I’d like to identify the value in each cell in the used
range, and if the value in the cell is ‘Relative dose [%]’ then perform the
‘text to column’ operation on the next cell below that, and run until Excel
finds a blank cell, and then search again for ‘Relative dose [%]’, and so on
and so forth until Excel reaches the end of the used range. My code is below:

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

cell.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

Next
End Sub

It fails on the next line. Can someone tell me what is wrong here?

Thanks,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
It will pick all cells which have "Relative dose" anywhere in them...

ryguy7272 said:
Yes, that would work, but there is some stuff after that in some cells. For
instance, one cell has this:
Relative dose [%] Dose [Gy] Ratio of Total Structure Volume [%]
Many cells are different; that's why I tried the * wildcard, after the
'Relative dose'. Any more suggestions?

Thanks,
Ryan---


--
RyGuy


Sheeloo said:
Remove the * from the following line;
If InStr(1, Cells(i, 1), "Relative dose*", vbTextCompare) Then

ryguy7272 said:
End If! Of course. it was late when I started this things last night. Now,
with the End If in the appropriate spot, the macro still doesn't do what I
wanted it to do. As I F8 through the code I notice that it seems to go form
the If... to the End if... to Next... and goes through this cycle over and
over. I guess there is something wrong with the IF statement, but I'm not
sure what it is. Can someone point out the problem.

TIA,
Ryan---



--
RyGuy


:

Also what is c in Range(c)? Named range?


:

I am trying to select cells in a used range and perform a ‘text to column’
change on all cells that fall into this used range. I have about 20
occurrences of this: ‘Relative dose [%]’ with some blanks in the cell right
after this language, and then some, well, basically random variables, after
that. Ideally, I’d like to identify the value in each cell in the used
range, and if the value in the cell is ‘Relative dose [%]’ then perform the
‘text to column’ operation on the next cell below that, and run until Excel
finds a blank cell, and then search again for ‘Relative dose [%]’, and so on
and so forth until Excel reaches the end of the used range. My code is below:

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

cell.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

Next
End Sub

It fails on the next line. Can someone tell me what is wrong here?

Thanks,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
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


Sheeloo said:
It will pick all cells which have "Relative dose" anywhere in them...

ryguy7272 said:
Yes, that would work, but there is some stuff after that in some cells. For
instance, one cell has this:
Relative dose [%] Dose [Gy] Ratio of Total Structure Volume [%]
Many cells are different; that's why I tried the * wildcard, after the
'Relative dose'. Any more suggestions?

Thanks,
Ryan---


--
RyGuy


Sheeloo said:
Remove the * from the following line;
If InStr(1, Cells(i, 1), "Relative dose*", vbTextCompare) Then

:

End If! Of course. it was late when I started this things last night. Now,
with the End If in the appropriate spot, the macro still doesn't do what I
wanted it to do. As I F8 through the code I notice that it seems to go form
the If... to the End if... to Next... and goes through this cycle over and
over. I guess there is something wrong with the IF statement, but I'm not
sure what it is. Can someone point out the problem.

TIA,
Ryan---



--
RyGuy


:

Also what is c in Range(c)? Named range?


:

I am trying to select cells in a used range and perform a ‘text to column’
change on all cells that fall into this used range. I have about 20
occurrences of this: ‘Relative dose [%]’ with some blanks in the cell right
after this language, and then some, well, basically random variables, after
that. Ideally, I’d like to identify the value in each cell in the used
range, and if the value in the cell is ‘Relative dose [%]’ then perform the
‘text to column’ operation on the next cell below that, and run until Excel
finds a blank cell, and then search again for ‘Relative dose [%]’, and so on
and so forth until Excel reaches the end of the used range. My code is below:

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

cell.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

Next
End Sub

It fails on the next line. Can someone tell me what is wrong here?

Thanks,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
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
 
Back
Top