Sleeper: Macro for Segregation of Text & Numer from range

[excelliot]

Hi all
i m veryt new & trying to write my first VBA macro wherin user provides first range in which data is there which is segregated in to text & numerical column both text & numerical column is also provided by user, i tried foll code but it doesnot work.
pls help me

Sub TxtNum()
Dim i As Long
Dim j As Long
Dim n As Long
Dim LastRow As Long
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range
Set r1 = Application.InputBox("Select the target range with the mouse", Type:=8)
Set r2 = Application.InputBox("Select the target range with the mouse", Type:=8)
Set r3 = Application.InputBox("Select the target range with the mouse", Type:=8)
LastRow = Cells(Rows.Count, "r1").End(xlUp).Row
For i = 1 To LastRow
If IsNumeric(Range("A" & i).Value) Then
With Range.Select.r2
.Value = Range("A" & i).Text
.NumberFormat = Range("A" & i).NumberFormat
End With
Else
With Range.Select.r3
.Value = Range("A" & i).Text
.NumberFormat = Range("A" & i).NumberFormat
End With
End If
Next i
End Sub

[mdmackillop]

I'm not sure that this code method is the fukll solution to your problem, but you're nor rar away with your coding. Once r1 etc is set as a range, you don't call it as Range(r1), r1.whatever will do. Also, don't enclose it in quotes or it simply becomes a string.
Very good attempt though! Correcting these gives the following:

Sub TxtNum()
    Dim i As Long
    Dim j As Long
    Dim n As Long
    Dim LastRow As Long
    Dim r1 As Range
    Dim r2 As Range
    Dim r3 As Range
Set r1 = Application.InputBox("Select the target range with the mouse", Type:=8)
    Set r2 = Application.InputBox("Select the target range with the mouse", Type:=8)
    Set r3 = Application.InputBox("Select the target range with the mouse", Type:=8)
LastRow = r1.End(xlUp).Row
    For i = 1 To LastRow
        If IsNumeric(Range("A" & i).Value) Then
            With r2
                .Value = Range("A" & i).Text
                .NumberFormat = Range("A" & i).NumberFormat
            End With
        Else
            With r3
                .Value = Range("A" & i).Text
                .NumberFormat = Range("A" & i).NumberFormat
            End With
        End If
    Next i
End Sub

r1 is not used, and I think you maybe should be looking for LastRow associated with column A, if that is where your data is. I think there are some other logical errors, but I'm sure you'll enjoy working these out!

[excelliot]

now i wants to paste selected text to text range & number range
but it is not working????

For i = 1 To LastRow
If IsNumeric(Range("A" & i).Value) Then
With r2
.Value = Range("A" & i).Text
.NumberFormat = Range("A" & i).NumberFormat
.PasteSpecial Paste:=xlPasteAll
End With

[Bob Phillips]

Hi excelliot,

I am slightly confused by what you are trying to do.

What is confusing me is that you prompt for 3 ranges, yet all the messages are the same. What is the difference?

Then, although you have 3 ranges selected, you have hard-coded column A in the code.


Can you explain a bit more?

Here is a stab though

Sub TxtNum()
    Dim i As Long
    Dim j As Long
    Dim n As Long
    Dim cell As Range
    Dim r1 As Range
    Dim r2 As Range
    Dim r3 As Range
Set r1 = Application.InputBox("Select, with the mouse, the range to be re-assigned", Type:=8)
    Set r2 = Application.InputBox("Select, with the mouse, the target text area", Type:=8)
    Set r3 = Application.InputBox("Select, with the mouse, the target numeric area", Type:=8)
For Each cell In r1
        If IsNumeric(cell.Value) Then
            With Cells(cell.Row, r2.Column)
                .Value = cell.Text
            End With
        Else
            With Cells(cell.Row, r3.Column)
                .Value = cell.Text
            End With
        End If
    Next cell
End Sub

[mdmackillop]

If your text is a mixture eg "123 text", you can use the Split function to divide the cell at the space (or other delimiter) eg

Sub TestSplit()
    Dim Test
    Test = Split([A7], " ")
    [B7] = Test(0)
    [C7] = Test(1)
End Sub

[excelliot]

Hi excelliot,

I am slightly confused by what you are trying to do.

What is confusing me is that you prompt for 3 ranges, yet all the messages are the same. What is the difference?

Then, although you have 3 ranges selected, you have hard-coded column A in the code.


Can you explain a bit more?

Here is a stab though

Sub TxtNum()
Dim i As Long
Dim j As Long
Dim n As Long
Dim cell As Range
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range
Set r1 = Application.InputBox("Select, with the mouse, the range to be re-assigned", Type:=8)
Set r2 = Application.InputBox("Select, with the mouse, the target text area", Type:=8)
Set r3 = Application.InputBox("Select, with the mouse, the target numeric area", Type:=8)
For Each cell In r1
If IsNumeric(cell.Value) Then
With Cells(cell.Row, r2.Column)
.Value = cell.Text
End With
Else
With Cells(cell.Row, r3.Column)
.Value = cell.Text
End With
End If
Next cell
End Sub

I Wants to segregate cell with numerical & text data in to two differrent data.
if cell contains numerical data then paste data in to range of numerical data.

[Bob Phillips]

I Wants to segregate cell with numerical & text data in to two differrent data.
if cell contains numerical data then paste data in to range of numerical data.

Yes, I understand that, but you didn't address my points.

Did the code we suipplied not work, get near?