VBA if cell contain text

Sparda142

Board Regular
Joined
Dec 19, 2018
Messages
52
Hello,

I'm trying to create a VBA that states if within Column A if there is a text then paste to the right. If the cell in column A has a numeric value then do nothing.


ab
1john
21
33
4jake
5bill
67

<tbody>
</tbody>
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Does this do what you want...

Code:
Sub test()
    Dim rng As Range, i As Long
    Set rng = Range("A2:B" & Cells(Rows.Count, 1).End(xlUp).Row)
    For i = 1 To rng.Cells.Count Step 2
        With rng
            If Not IsNumeric(.Cells(i).Value) Then .Cells(i + 1) = .Cells(i).Value
        End With
    Next
End Sub
 
Upvote 0
Does this do what you want...

Code:
Sub test()
    Dim rng As Range, i As Long
    Set rng = Range("A2:B" & Cells(Rows.Count, 1).End(xlUp).Row)
    For i = 1 To rng.Cells.Count Step 2
        With rng
            If Not IsNumeric(.Cells(i).Value) Then .Cells(i + 1) = .Cells(i).Value
        End With
    Next
End Sub

Didn't work
 
Upvote 0
Other than perhaps that as per your graphic the range should have correctly started at "A1" and not "A2", as My Aswer asked, what happened...
 
Upvote 0
Here is how I would write the code:
Code:
Sub My_Sub()
'Modified  8/6/2019  5:27:34 PM  EDT
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        If Not IsNumeric(Cells(i, 1).Value) Then Cells(i, 2).Value = Cells(i, 1).Value
    Next
End Sub
 
Upvote 0
@ My Aswer,

Just for shiggles, I ran my test data out to 250k rows of data (I meant to go 100k, but was talking to someone and forgot to let go of the mouse button). On my machine our first two codes completed in about 12.3 seconds.

The code below did it in ¼ seconds.

Code:
Sub test2()
    Dim arr, i As Long
    arr = Range("A1:B" & Cells(Rows.Count, 1).End(xlUp).Row)
    For i = LBound(arr) To UBound(arr)
            If Not IsNumeric(arr(i, 1)) Then arr(i, 2) = arr(i, 1)
    Next
    Range("A1").Resize(UBound(arr, 1), 2) = arr
End Sub
 
Upvote 0
Here is a macro that does not use a loop...
Code:
Sub CopyTextOnly()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Application.ScreenUpdating = False
  Range("B1:B" & LastRow).Value = Range("A1:A" & LastRow).Value
  Columns("B").SpecialCells(xlConstants, xlNumbers).Clear
  Application.ScreenUpdating = True
End Sub
@igold... Do you still have your test setup? If so, I would be interested to know the execution time of the above code.
 
Upvote 0
Hi Rick,

Ran it twice, ~49 seconds both times.

As a check, ran the other code right after to make sure that the machine was not busy somewhere else and got the same results...
 
Upvote 0
Hi Rick,

Ran it twice, ~49 seconds both times.

As a check, ran the other code right after to make sure that the machine was not busy somewhere else and got the same results...
Yeah, I thought copying all the data over then filtering might slow things down, but I didn't think it would do so that adversely. Thanks for doing the test.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top