Shift cells
Is there a way to move cells beginning with '0A' from Col B to Col C?
|
Shift cells
gary wrote :
Is there a way to move cells beginning with '0A' from Col B to Col C? Do you mean IF the colB cell contents begin with "OA" then you want to move the contents to colC and clear the contents from the cell in colB? If so then you need to loop the range and use either the Left$() function to see if the 1st 2 characters = "OA", or use the InStr() function to see if "OA" = 1 for position. Either would be nested in an If...Then construct. Use a For Each loop to iterate colB. Use Offset(, 1) to put the value in colC Use .ClearContents on the cell in colB <aircode Dim c As Range For Each c In Range("B1:B?") 'edit range address to suit If InStr(1, c.Text, "OA") = 1 Then '//comment out to suit '//or... If Left$(c.Text, 2) = "OA" Then '//comment out to suit c.Offset(, 1) = c.Value: c.ClearContents End If Next 'c </aircode If there's a lot of cells to process then I suggest 'dumping' the range (both cols) into an array, work the array in memory, then 'dump' the data back into the sheet. In this case you'd use a counter with For...Next. <aircode Dim v As Variant, i As Long v = Range("B1:C?") 'edit range address to suit For i = LBound(v) To UBound(v) If Left(v(i, 1), 2) = "OA" Then '//comment out to suit '//or... If InStr(1, v(i, 1), "OA") = 1 Then '//comment out to suit v(i, 2) = v(i, 1): v(i, 1) = vbNullString End If Next 'i Range("B1:C?") = v 'edit range address to suit </aircode -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
All times are GMT +1. The time now is 11:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com