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:
[TABLE="class: grid, width: 370"]
<tbody>[TR]
[TD="width: 74"][/TD]
[TD="width: 74"]Text-1[/TD]
[TD="width: 74"]Text-2[/TD]
[TD="width: 74"]Text-3[/TD]
[TD="width: 74"]Text-4[/TD]
[TD="width: 74"]Text-5[/TD]
[/TR]
</tbody>[/TABLE]

After code:
[TABLE="class: grid, width: 370"]
<tbody>[TR]
[TD]Text[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]




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