Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert 19 cells "Shift to the right" if cell contains "-"
I have been looking for a sub that can find a cell in column A that ha a "-" in it the actuall cell will contain "P-9999999" the only thin consistant is the "-". When it finds the cell I need it to shift 1 cells to the Right. Thanks for the help or any ideas, Bo -- robertjtucke ----------------------------------------------------------------------- robertjtucker's Profile: http://www.excelforum.com/member.php...fo&userid=2180 View this thread: http://www.excelforum.com/showthread.php?threadid=38933 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert 19 cells "Shift to the right" if cell contains "-"
I have this and it works, it inserts cells until it gets out of the range but takes way to long. One at a time. Any one have any ideas on how to get it insert 19 cells at a time or outside the range. Sub Macro64() With Worksheets("Imported").Range("imp2") Set c = .Find("-", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Insert Shift:=xlToRight Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With End Sub -- robertjtucker ------------------------------------------------------------------------ robertjtucker's Profile: http://www.excelforum.com/member.php...o&userid=21805 View this thread: http://www.excelforum.com/showthread...hreadid=389337 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert 19 cells "Shift to the right" if cell contains "-"
Hi,
You can use this. Remember, the key here is that once the range is found, Excel will not find the next range unless you swap the text in the first range for something else. Then, at the end, I swap the text back. ....Bill Sub hhh() Dim LoopEnd As Boolean Dim SomeRange As Range Const Txt = "SomeUnlikelyText" Do While Not LoopEnd Set SomeRange = Cells.Find(What:="-") If SomeRange Is Nothing Then LoopEnd = True Else SomeRange.Value = Application.Substitute(SomeRange.Value, "-", Txt) SomeRange.Cut SomeRange.Offset(0, 19) End If Loop Cells.Replace What:=Txt, Replacement:="-", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub "robertjtucker" wrote in message news:robertjtucker.1sko2p_1122037851.4973@excelfor um-nospam.com... I have been looking for a sub that can find a cell in column A that has a "-" in it the actuall cell will contain "P-9999999" the only thing consistant is the "-". When it finds the cell I need it to shift 19 cells to the Right. Thanks for the help or any ideas, Bob -- robertjtucker ------------------------------------------------------------------------ robertjtucker's Profile: http://www.excelforum.com/member.php...o&userid=21805 View this thread: http://www.excelforum.com/showthread...hreadid=389337 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert 19 cells "Shift to the right" if cell contains "-"
Thanks for that, but I need all the cells next to the cells containinig the "-" to be moved also thats why I wanted to insert so I could shift everything from A:S to start in T:AL Thanks again, Bob -- robertjtucker ------------------------------------------------------------------------ robertjtucker's Profile: http://www.excelforum.com/member.php...o&userid=21805 View this thread: http://www.excelforum.com/showthread...hreadid=389337 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert 19 cells "Shift to the right" if cell contains "-"
Minor (very) change:
Sub hhh() Dim LoopEnd As Boolean Dim SomeRange As Range Const Txt = "SomeUnlikelyText" Do While Not LoopEnd Set SomeRange = Cells.Find(What:="-") If SomeRange Is Nothing Then LoopEnd = True Else SomeRange.Value = Application.Substitute(SomeRange.Value, "-", Txt) ActiveSheet.Range(SomeRange, SomeRange.Offset(0, 18)).Cut SomeRange.Offset(0, 19) End If Loop Cells.Replace What:=Txt, Replacement:="-", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End Sub "robertjtucker" wrote in message news:robertjtucker.1skz6r_1122052018.8388@excelfor um-nospam.com... Thanks for that, but I need all the cells next to the cells containinig the "-" to be moved also thats why I wanted to insert so I could shift everything from A:S to start in T:AL Thanks again, Bob -- robertjtucker ------------------------------------------------------------------------ robertjtucker's Profile: http://www.excelforum.com/member.php...o&userid=21805 View this thread: http://www.excelforum.com/showthread...hreadid=389337 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert 19 cells "Shift to the right" if cell contains "-"
Thanks Bill it worked real well. Appreciate the help, Bo -- robertjtucke ----------------------------------------------------------------------- robertjtucker's Profile: http://www.excelforum.com/member.php...fo&userid=2180 View this thread: http://www.excelforum.com/showthread.php?threadid=38933 |
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) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Lost "File Menu" - now it's "Edit / View / Insert.." but no "F | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) |