# VB Find Question

#### rlee1999

##### Board Regular
I have a report that sends raw data in space separated values. Each row has a text string starting with either
"VX#######", or ###.# , where # = a number.

The VX numbers are easy enough to deal with. I say find VX, text to columns, next. Now I need to deal with these random numbers the appear in rows between some of the VX numbers. I need to find these random numbers and drop them into a cell associated with the VX number below it.

I am thinking 'Find "VX", look one cell above, if that cell is <> "VX" then cut and paste to ColF of this VX row, if it is = to "VX" find next and do it again.'

I need it to run to the bottom of the page and stop when it finds no more VX numbers

Does this logic work? and if so, how do I write it?

Any help would be appreciated!

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
I have a report that sends raw data in space separated values. Each row has a text string starting with either
"VX#######", or ###.# , where # = a number.

The VX numbers are easy enough to deal with. I say find VX, text to columns, next. Now I need to deal with these random numbers the appear in rows between some of the VX numbers. I need to find these random numbers and drop them into a cell associated with the VX number below it.

I am not entirely clear on what your data looks like. You are talking about "space separated" values (numbers with spaces between them, basically a text string?), numbers with VX (only the first number in the text string?), and numbers without VX (where, in their own cells? is there non-VX, non-numeric values mixed in the column?). Can you show us a sample of your data and what you want that data to look like after being processed?

Sub FindVXNumbers()
C = Columns(1).SpecialCells(xlCellTypeConstants, 23).Cells.Count
For Each cell In Range("A2", Range("A2").Offset(C, 0)).Cells
If Left(cell.Value, 2) = "VX" Then
If Left(cell.Offset(-1, 0).Value, 2) <> "VX" Then
cell.Offset(0, 5).Value = cell.Offset(-1, 0).Value
End If
End If
Next
End Sub

assuming your data is in column A and there is no blank rows in between the data

Yes, a text string separated by spaces as opposed to commas

Before:
.......A............................................................B.....C.....D.....E.....F
1 VX0012345 DO V000378162 40 M OUTMDCR
2 VX0123456 MA V000140285 94 F OUTMDCR
3 250.13
4 VX1234567 YA V000432301 18 F OUTMDCR

After:
.........A........B.......C......D.......E.........F
1 VX0012345 DO V00037 40 OUTMDCR
2 VX0123456 MA V00014 94 OUTMDCR
3
4 VX1234567 YA V00043 18 OUTMDCR 250.13

Does this macro do what you want...

Code:
``````Sub KeepVX()
Dim X As Long, ColC As Variant
On Error Resume Next
Columns("A").SpecialCells(xlConstants, xlNumbers).Clear
On Error GoTo 0
Columns("A").TextToColumns Range("A1"), xlDelimited, , , False, False, False, True, False
ColC = Range("C1").Resize(Cells(Rows.Count, "C").End(xlUp).Row).Value
With Range("G1:G" & Cells(Rows.Count, "C").End(xlUp).Row)
.FormulaR1C1 = "=LEFT(RC3,6)"
.Offset(, -4).Value = .Value
.Clear
End With
End Sub``````

That didn't do it. Here is what I am using to get the strings starting with "VX" to columns

Function VXSeparate()

Cells.Find(What:="VX", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(29, 1), Array(42, 1), Array(46, 1), _
Array(49, 1), Array(62, 3), Array(76, 3), Array(85, 1), Array(90, 1), Array(101, 1), Array( _
113, 1)), TrailingMinusNumbers:=True

End Function

Unfortunately I have to enter the function into the Sub about 100 times, because I don't know how to tell it to run to the end of the report and shut off. But that part seems to be working. Unfortunately I can't share a live example of my data, because I am dealing with medical records. The random numbers that sometimes appear above the VX numbers need to be moved to ColF of the row containing the VX number they are associated with. In the data example I included earlier in the thread, the number from A3 needs to end up in F4.

Ok, I had to delete the blank rows between records and used the solution from bikeordie1 now it is working beautifully as far as copying, but I need to do more of a cut and paste or delete those non VX numbers after they have been copied.

Mr. Rothstein's solution keeps hanging at "Columns("A").TextToColumns Range("A1"), xlDelimited, , , False, False, False, True, False" It asks if I am sure I want to replace the cell contents. If I choose yes, it destroys the formatting. If I choose no, it takes me to the debugger

if you want to get rid of the numbers after they're copied then run this instead:

Sub FindVXNumbers()
C = Columns(1).SpecialCells(xlCellTypeConstants, 23).Cells.Count
For Each cell In Range("A2", Range("A2").Offset(C, 0)).Cells
If Left(cell.Value, 2) = "VX" Then
If Left(cell.Offset(-1, 0).Value, 2) <> "VX" Then
cell.Offset(0, 5).Value = cell.Offset(-1, 0).Value
cell.Offset(-1, 0).EntireRow.Delete
End If
End If
Next
End Sub

Every Q&A is a learning opportunity for me and I appreciate you sharing your knowledge. Thank you both for your help!

Replies
0
Views
415
Replies
2
Views
270
Replies
7
Views
567
Replies
6
Views
338
Replies
1
Views
216

1,211,772
Messages
6,103,876
Members
447,882
Latest member
LORENA

### 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.

### Which adblocker are you using?

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

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