reformat text using VBA

[i800499]

I need some help to reformat a range of text in a spreadsheet. The text is currently in the following format:

cell A1: "ABCD"
cell B1: "A1 - A5"

I need to convert the range in cell B1 so that the range is expanded downwards in column B. The output should look like this:

A1: "ABCD" B1: "A1"
A2: "ABCD" B2: "A2"
A3: "ABCD" B3: "A3"
A4: "ABCD" B4: "A4"
A5: "ABCD" B5: "B5"

Any suggestions on how to do this using VBA?

 

[iliace]

Is it actually A1, A2, etc? What is the range of values that this could contain?

 

[i800499]

The range text was just an example. I'm trying to handle a technical parts list.

The part description always contains both alpha and number characters. The alpha characters always come before the numbers. There could either be 1 or 2 alpha characters at the beginning of the part name, but never more than that.

Here are some examples of the range possibilities:

XL22 - XL27
F17 - F89
AB57 - AB102
T45 - T52

 

[Norie]

So what would be the expected result from the 'real' data?

Something like this?

XL22
XL23
XL24
XL25
XL26
XL27
F17
F18
...
F89
AB57
AB58
...
AB102
T45
T46
...
T52

 

[i800499]

Hi Norie,

Yes - your example looks exactly like the output I'm trying to produce. Please have a look at my first post for a detailed example of the input and output.

To summarize - the input is 2 cells. The first cell contains a single value which is associated with a range appearing in the second cell.

As output, the value in the first cell will be repeated down through the column and the range will be "expanded" so that each value within the range appears in the column.

Let me know if that makes sense...

Regards,
Bob

 

[iliace]

Select A1:B1, and run this code. Or any range that contains the data as you indicated. Note that the result will automatically populate directly below.

Public Sub ReformatText()
  Dim rngSource As Range
  Dim str1 As String, str2 As String
  Dim s As String
  
  Dim n1 As Long, n2 As Long
  Dim i As Long
  Dim strPrefix As String
  
  Dim v As Variant
  
  On Error Resume Next
    Set rngSource = Selection
    If Err.Number <> 0 Then Exit Sub
  On Error GoTo 0
  
  Debug.Print rngSource.Address
  
  If rngSource.Cells.Count <> 2 Or rngSource.Columns.Count <> 2 Then Exit Sub
  
  s = rngSource.Cells(2).Value
  
  str1 = Trim(Left(s, InStr(1, s, "-") - 1))
  str2 = Trim(Right(s, Len(s) - InStr(1, s, "-")))
  
  Debug.Print str1, str2
  
  For i = 1 To Len(str1)
    If IsNumeric(Mid(str1, i, 1)) Then Exit For
  Next i
  
  If i > Len(str1) Then Exit Sub
  
  strPrefix = Left(str1, i - 1)
  n1 = CLng(Right(str1, Len(str1) - i + 1))
  
  For i = 1 To Len(str2)
    If IsNumeric(Mid(str2, i, 1)) Then Exit For
  Next i
  
  If i > Len(str2) Then Exit Sub
  
  n2 = CLng(Right(str2, Len(str2) - i + 1))
  s = rngSource.Cells(1).Value
  
  Debug.Print s, strPrefix, n1, n2
  
  ReDim v(1 To n2 - n1 + 1, 1 To 2)
  
  For i = n1 To n2
    v(i - n1 + 1, 1) = s
    v(i - n1 + 1, 2) = strPrefix & i
  Next i
  
  Debug.Print rngSource.Cells(1).Offset(1, 0).Resize(UBound(v, 1), UBound(v, 2)).Address
  
  rngSource.Cells(1).Offset(1, 0).Resize(UBound(v, 1), UBound(v, 2)).Value = v
End Sub

 

[Norie]

Bob

I think we need to know exactly what you want to do here - I'm a little confused.

In your first post and your third it appeared that you were working with data in one cell, not two.

 

[i800499]

Thanks for the response - I'm going to check it out now.

Saw that you are from Maryland - which part? I graduated from UMD many years ago and hoping against hope that the Terps have an answer for Duke tonight...

 

[iliace]

Bob

I think we need to know exactly what you want to do here - I'm a little confused.

In your first post and your third it appeared that you were working with data in one cell, not two.

I just assumed that you will work with 2 cells, selected prior to running the macro. The two cells are in the same row and adjacent to each other. First one contains the value to repeat, the second one contains the range (e.g. "A1 - A5"). The code will populate the cells directly below the selection.

It might be more complicated than that, but a good starting point notwithstanding.

Saw that you are from Maryland - which part? I graduated from UMD many years ago and hoping against hope that the Terps have an answer for Duke tonight...

I'm currently staying in Columbia. Got my undergrad from UMD several years ago.

 

[i800499]

This solves the most difficult portion of my problem. I can tweak this this to get to where I need to be.

Thanks, again.