Excel VBA Macro

Discuss other programming languages besides AutoHotkey
User avatar
TheDewd
Posts: 1349
Joined: 19 Dec 2013, 11:16
Location: USA

Excel VBA Macro

22 Feb 2019, 15:47

I want to create an Excel VBA Macro, but I need some help. I could make an AutoHotkey script easily, but I'm afraid to admit that I'm a novice at VBA.

I have several cells on the worksheet that contain a "start" and "end" number value.

They are in groups. Group 1 Start & End, Group 2 Start & End, Group 3 Start & End, etc...

I want to take the series of numbers between the start and end value and separate them with commas, but I want to alternate or pair with the other groups.

Example:
Group 1 Start : 1
Group 1 End: 13

Group 2 Start: 14
Group 2 End: 26

Output: 1,14,2,15,3,16,4,17,5,18,6,19,7,20,8,21,9,22,10,23,11,24,12,25,13,26

Each group should always contain an equal count of numbers in the series.

Code: Select all

Group1Start = Range("B4").Value
Group1End = Range("B5").Value

Group2Start = Range("B8").Value
Group2End = Range("B9").Value

Group3Start = Range("B12").Value
Group3End = Range("B13").Value

Group4Start = Range("B16").Value
Group4End = Range("B17").Value

I learned how to use a For Loop to list the number series in a cell for a single group, but I don't know how to do the alternating:

Code: Select all

Dim i As Integer

Dim List As String

For i = 1 To (Group1End - Group1Start) + 1
    List = List & (Group1Start - 1) + i & ","
Next i

'Output results to cell for test
Range("M1").Value = List



Edit: I could also just list the first start and the last end (ex: 1 & 26), and then have the user specify how many groups (ex: 2) since they groups will always have the same count of numbers in the series, then use math to determine the start and end numbers from each group, but I will save that for a later challenge.
Image Bulldozer - Sokoban inspired game from 1994 recreated in AutoHotkey.
User avatar
jeeswg
Posts: 6828
Joined: 19 Dec 2016, 01:58
Location: UK

Re: Excel VBA Macro

22 Feb 2019, 16:21

Here's a try. Cheers.

Code: Select all

Sub Macro1()
'
' Macro1 Macro 'concatenate cells
'

'
For i = 1 To 26
    Range("A" & i).Value = i
Next

Group1Start = 1
Group1End = 13
Group2Start = 14
Group2End = 26

List = ""
For i = 1 To (Group1End - Group1Start) + 1
    List = List & Range("A" & (Group1Start - 1 + i)).Value & ","
    List = List & Range("A" & (Group2Start - 1 + i)).Value & ","
Next i

List = Left(List, Len(List) - 1)
MsgBox List
Range("C1").Value = List

End Sub
homepage | tutorials | wish list | fun threads | donate
WARNING: copy your posts/messages before hitting Submit as you may lose them due to CAPTCHA
User avatar
TheDewd
Posts: 1349
Joined: 19 Dec 2013, 11:16
Location: USA

Re: Excel VBA Macro

22 Feb 2019, 17:15

jeeswg wrote:Here's a try. Cheers.
Excellent! It definitely helped me get closer to where I wanted to be.

My issue now is how to make this code work when there is no values entered for a group.

For example, if the user only inputs values for Group 1 & Group 2, but Group 3 & Group 4 are empty, how do I avoid the Subscript out of range error message due to the missing values.

I experimented with using If but could not figure it out...

Code: Select all

Sub Macro1()
    Group1Start = Range("B4").Value
    Group1End = Range("B5").Value
    
    Group2Start = Range("B8").Value
    Group2End = Range("B9").Value
    
    Group3Start = Range("B12").Value
    Group3End = Range("B13").Value
    
    Group4Start = Range("B16").Value
    Group4End = Range("B17").Value
    
    For i = 1 To (Group1End - Group1Start) + 1
        List1 = List1 & (Group1Start - 1) + i & ","
    Next i
    
    For i = 1 To (Group2End - Group2Start) + 1
        List2 = List2 & (Group2Start - 1) + i & ","
    Next i
    
    For i = 1 To (Group3End - Group3Start) + 1
        List3 = List3 & (Group3Start - 1) + i & ","
    Next i
    
    For i = 1 To (Group4End - Group4Start) + 1
        List4 = List4 & (Group4Start - 1) + i & ","
    Next i
    
    SplitCatcher1 = Split(List1, ",")
    SplitCatcher2 = Split(List2, ",")
    SplitCatcher3 = Split(List3, ",")
    SplitCatcher4 = Split(List4, ",")

    List = ""
    
    For i = 1 To (Group1End - Group1Start) + 1
        List = List & SplitCatcher1(i - 1) & ","
        List = List & SplitCatcher2(i - 1) & ","
        List = List & SplitCatcher3(i - 1) & ","
        List = List & SplitCatcher4(i - 1) & ","
    Next i
    
    List = Left(List, Len(List) - 1)
    
    Range("B19").Value = List
End Sub

FYI -- Possible values for all groups could be like the following:
Group 1
Start 1
End 13

Group 2
Start 14
End 26

Group 3
Start 27
End 40

Group 4
Start 41
End 54
Image Bulldozer - Sokoban inspired game from 1994 recreated in AutoHotkey.
User avatar
lmstearn
Posts: 231
Joined: 11 Aug 2016, 02:32
GitHub: lmstearn
Contact:

Re: Excel VBA Macro

27 Feb 2019, 10:10

You might be able to do it with the OnError Statement.
Placing it within the loop with Resume Next might not be best practise, as it's possible when more than one error is handled in the same module, the compiler will jump out of it altogether. VBA might be friendly, however. :)
:arrow: itros "ylbbub eht tuO kaerB" a ni kcuts m'I pleH

Return to “Other Programming Languages”

Who is online

Users browsing this forum: No registered users and 3 guests