VB Find Question

rlee1999

Board Regular
Joined
Sep 19, 2006
Messages
135
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?
 
Upvote 0
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 :)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Every Q&A is a learning opportunity for me and I appreciate you sharing your knowledge. Thank you both for your help!
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top