VB Find Question

[rlee1999]

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!

 

[Rick Rothstein]

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?

 

[bikeordie1]

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

 

[rlee1999]

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

 

[Rick Rothstein]

Does this macro do what you want...

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

 

[rlee1999]

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.

 

[Rick Rothstein]

That didn't do it.

What did it do that it should not have done?

 

[rlee1999]

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

 

[bikeordie1]

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

 

[rlee1999]

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