VBA if cell contain text

Sparda142

Board Regular
Joined
Dec 19, 2018
Messages
51
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>
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,447
Office Version
365, 2010
Platform
Windows
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
 

Sparda142

Board Regular
Joined
Dec 19, 2018
Messages
51
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
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,447
Office Version
365, 2010
Platform
Windows
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...
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,295
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
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,447
Office Version
365, 2010
Platform
Windows
@ 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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,510
Office Version
2010
Platform
Windows
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.
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,447
Office Version
365, 2010
Platform
Windows
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...
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,510
Office Version
2010
Platform
Windows
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.
 

Forum statistics

Threads
1,082,359
Messages
5,364,916
Members
400,815
Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top