ma1h
g30 to 35 works
ma1hg30
ma1hg31
ma1hg32
ma1hg33
ma1hg34
ma1hg35
however
ma1h
9C0 to 5 does not
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
unless u start with 9c1 but not zero
when i use this code
Code
Dim pref As String, myStr As String, x, LastR As Range
pref = InputBox("Enter Prefix")
myStr = InputBox("Enter datarange")
If myStr = "" Then Exit Sub
x = GetDetails(myStr)
If IsArray(x) Then
pref = pref & x(0)
Set LastR = Sheets("Sheet1").[d8]
If LastR <> "" Then Set LastR = LastR.Parent.Cells(Rows.Count, LastR.Column).End(xlUp)(2)
LastR.Resize(Val(x(2)) - Val(x(1)) + 1) = Evaluate("""" & pref & """&row(" & x(1) & ":" & x(2) & ")")
End If
End Sub
Function GetDetails(ByVal txt As String)
With CreateObject("VBScript.RegExp")
.IgnoreCase = True
.Pattern = "^(.*?)(\d+) to (\d+)$"
If .test(txt) Then GetDetails = Split(.Replace(txt, "$1^^$2^^$3"), "^^")
End With
End Function
Display More