Change the text within a cell to the text within another cell and paste in another co

[Tony_Caliente]

I have a table with 360,000 rows and approximately 26 columns. The first column header/label is called "Division"; it also happens to be the first/left-most column.
Where a cell within the "Division" column starts with the text "Ext" or "ext", possibly followed by more characters, spaces, dashes, etc., I would like to copy the values and formatting from Column 3, named "Group" and paste the values and formatting in Column 2,named "RevisedDivision". If the cell text in the "Division" column doesn't begin with "Ext" or "ext", then simply copy the values from the "Division" column to Column 2 ("RevisedDivsion".) I have seen this performed using the Case "function, but have not been able to replicate it.


Thank you for reading and any help!

I have also posted this here, but have only received one response after several hours. Thank you again!
Change the text within a cell to the text within another cell and paste in another column

[alansidman]

Try this aircode:

Option Explicit

Sub ams()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row

    Application.ScreenUpdating = False
    For i = 2 To lr
        If Left(Range("A" & i), 3) = "Ext" Or Left(Range("A" & i), 3) = "ext" Then
            Range("C" & i).Copy Range("B" & i)
        Else: Range("A" & i).Copy Range("B" & i)
        End If
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "complete"

End Sub

[snb]

sub M_snb()
  sn=sheets(1).cells(1).currentregion.resize(,3)

  for j=2 to ubound(sn)
    sn(j,2)=sn(j,3)
    if lcase(left(sn(j,1),3))="ext" then sn(j,2)=""
  next
  sheets(1).cells(1).currentregion.resize(,3)=sn

  for each it in sheets(1).cells(1).currentregion.columns(2).specialcells(4)
     it.offset(,1).copy it
  next
End Sub

[Tony_Caliente]

Excellent! Thank you. I will have the opportunity to try soon. Thanks again!

[Rick Rothstein]

sub M_snb()
  sn=sheets(1).cells(1).currentregion.resize(,3)

  for j=2 to ubound(sn)
    sn(j,2)=sn(j,1)
    if lcase(left(sn(j,1),3))="ext" then sn(j,2)=""
  next
  sheets(1).cells(1).currentregion.resize(,3)=sn

  for each it in sheets(1).cells(1).currentregion.columns(2).specialcells(4)
     it.offset(,1).copy it
  next
End Sub

When I run your code, I just get Column C duplicated in Column B. I think the 3 that you had should be a 1 as shown in red above.

[Tony_Caliente]

Thank you Rick! I was looking forward to a response from you.

[Rick Rothstein]

Here is another macro that you can try (it runs quite quickly)...

Sub Test()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B2:B" & LastRow) = Evaluate(Replace("IF(LEFT(A2:A#,3)=""ext"",C2:C#,A2:A#)", "#", LastRow))
End Sub

[Rick Rothstein]

Thank you Rick! I was looking forward to a response from you.

Did you see the code I posted in Message #5 (our posts may have passed each other in the "ether")?

[Tony_Caliente]

Yes, I did. Thank you again! I meant, when I posted the question you were among several MVPs that I was hoping would answer given the code you have presented me in other Excel VBA forums.

[Rick Rothstein]

Yes, I did. Thank you again! I meant, when I posted the question you were among several MVPs that I was hoping would answer given the code you have presented me in other Excel VBA forums.

Can I assume my code worked for you then and that the code was as fast for you as I claimed it would be?