Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next Loop and If..."Relative dose*"...Then
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, Id 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--- -- RyGuy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next Loop and If..."Relative dose*"...Then
I did not see an END IF before Next...
"ryguy7272" wrote: 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, Id 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--- -- RyGuy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next Loop and If..."Relative dose*"...Then
Also what is c in Range(c)? Named range?
"ryguy7272" wrote: 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, Id 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--- -- RyGuy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next Loop and If..."Relative dose*"...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 "Sheeloo" wrote: Also what is c in Range(c)? Named range? "ryguy7272" wrote: 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, Id 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--- -- RyGuy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next Loop and If..."Relative dose*"...Then
Remove the * from the following line;
If InStr(1, Cells(i, 1), "Relative dose*", vbTextCompare) Then "ryguy7272" wrote: 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" wrote: Also what is c in Range(c)? Named range? "ryguy7272" wrote: 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, Id 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--- -- RyGuy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next Loop and If..."Relative dose*"...Then
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" wrote: Remove the * from the following line; If InStr(1, Cells(i, 1), "Relative dose*", vbTextCompare) Then "ryguy7272" wrote: 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" wrote: Also what is c in Range(c)? Named range? "ryguy7272" wrote: 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, Id 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--- -- RyGuy |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next Loop and If..."Relative dose*"...Then
It will pick all cells which have "Relative dose" anywhere in them...
"ryguy7272" wrote: 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" wrote: Remove the * from the following line; If InStr(1, Cells(i, 1), "Relative dose*", vbTextCompare) Then "ryguy7272" wrote: 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" wrote: Also what is c in Range(c)? Named range? "ryguy7272" wrote: 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, Id 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--- -- RyGuy |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next Loop and If..."Relative dose*"...Then
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" wrote: It will pick all cells which have "Relative dose" anywhere in them... "ryguy7272" wrote: 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" wrote: Remove the * from the following line; If InStr(1, Cells(i, 1), "Relative dose*", vbTextCompare) Then "ryguy7272" wrote: 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" wrote: Also what is c in Range(c)? Named range? "ryguy7272" wrote: 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, Id 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--- -- RyGuy |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next Loop and If..."Relative dose*"...Then
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Next Loop and If..."Relative dose*"...Then
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Question on determining "ROW" inside of a "For .. RANGE " loop | Excel Programming | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
LOOP BETWEEN "FRONT" AND "END" SHEETS? | Excel Programming | |||
Change "relative" to "absolute" (database) | Excel Programming |