View Single Post
 
Old 03-17-2018, 06:52 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

You're not clear on what you're wanting the result to be,
88 89 90 91 92 93 is not the same as 88;89;90;91;92;93
but you can make it whatever you want in a macro.

This macro will fire when you type a new value into A1 or B1.
You've given no ground rules for the numbers in A & B column so I just put some in that may or may not be practical for your purpose.

Right click the sheet tab, select View Code and paste this into the sheet module.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer, str As String
    
'limit to single cell
If Target.Count > 1 Then Exit Sub
'limit to row 1
If Target.Row <> 1 Then Exit Sub
'limit to columns A and B
If Target.Column > 2 Then Exit Sub

'clear anything existing in C
'disable events so this macro doesn't call itself when it changes a cell
Application.EnableEvents = False
Cells(Target.Row, "C").ClearContents
're-enable events
Application.EnableEvents = True

'make sure both cells have numbers
If Not IsNumeric(Cells(Target.Row, "A")) Or Not IsNumeric(Cells(Target.Row, "B")) Then Exit Sub
'make sure A cell is smaller than B cell
If Cells(Target.Row, "A") >= Cells(Target.Row, "B") Then Exit Sub

'put together what's to go into C1
For i = Cells(Target.Row, "A").Value To Cells(Target.Row, "B").Value
    str = str & ";" & i
Next i

Application.EnableEvents = False
'write the string to C, remove the leading ;
Cells(Target.Row, "C").Value = Mid(str, 2)
Application.EnableEvents = True

End Sub
Let me know if this is something you can work with.
Reply With Quote