Message box for user input to run macro on a range VBA

[seattletimebandit]

Hello!

I have a bit of code that manipulates data starting in a specific cell. Trying to figure out how to do this by asking the user to select the range, not matter where the table of data is located. The code I have now:

Sub SplitDepthsFromSampleID()
  Dim ID As String
  ID = Left(Range("B1").Value, InStrRev(Range("B1").Value, "-") - 1)
  Range("A1").Value = ID
  Range("B1", Cells(1, Columns.Count).End(xlToLeft)).Replace ID & "-", "", xlPart, , , , False
End Sub

Before code:

Text-1

Text-2

Text-3

Text-4

Text-5

<tbody>
</tbody>

After code:

Text

1

2

3

4

5

<tbody>
</tbody>

I'm working on it, and have the Msg Box bit for selecting the range, but not sure how to run the macro on the selection. There would only be 1 row, but number of columns is variable.

Thanks!

Russell

 

[Fluff]

How about

Sub SplitDepthsFromSampleID()
  Dim ID As String
  Dim Rng As Range
  
  Set Rng = Application.InputBox("Please select the range", Default:="$A$1", Type:=8)
  ID = Split(Rng.Resize(1, 1), "-")(0)
  Rng.Offset(, -1).Resize(1, 1) = ID
  Rng.Replace ID & "-", "", xlPart, , , , False
End Sub

 

[seattletimebandit]

Fluff, that works great!

But i forgot one thing, sometimes the data will be something like "text-1-2, text-1-5, text-1-10, etc.". Your code worked with the single dash, but when i added the second dash, the result was a date: "2-Jan, 5-Jan, 10-Jan, etc." I need to be able to strip out the second dash as well. My static macro did that, but for some reason your code doesn't replace the "-" with a "". Thoughts?

 

[Fluff]

Ok, how about

Sub SplitDepthsFromSampleID()
  Dim ID As String
  Dim Rng As Range
  
  Set Rng = Application.InputBox("Please select the range", Default:="$A$1", Type:=8)
  ID = Left(Rng.Resize(1, 1), InStrRev(Rng.Resize(1, 1), "-") - 1)
  Rng.Offset(, -1).Resize(1, 1) = ID
  Rng.Replace ID & "-", "", xlPart, , , , False
End Sub

 

[seattletimebandit]

Fluff, that's it! The "InStrRev" was what I was looking for!

Thanks a bunch. Works great.

stb

 

[Fluff]

Glad to help & thanks for the feedback