PDA

View Full Version : Sleeper: Macro for Segregation of Text & Numer from range



excelliot
07-19-2005, 12:12 AM
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

:dunno

mdmackillop
07-19-2005, 12:48 AM
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!:clap:

excelliot
07-19-2005, 01:32 AM
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
07-19-2005, 02:42 AM
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
07-19-2005, 05:34 AM
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
07-24-2005, 04:11 AM
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.:banghead:

Bob Phillips
07-24-2005, 05:59 AM
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?