Excel VBA Macro

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

Excel VBA Macro

Post by TheDewd » 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.

User avatar
jeeswg
Posts: 6902
Joined: 19 Dec 2016, 01:58
Location: UK

Re: Excel VBA Macro

Post by jeeswg » 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: 1506
Joined: 19 Dec 2013, 11:16
Location: USA

Re: Excel VBA Macro

Post by TheDewd » 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

User avatar
lmstearn
Posts: 688
Joined: 11 Aug 2016, 02:32
Contact:

Re: Excel VBA Macro

Post by lmstearn » 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

Post Reply

Return to “Other Programming Languages”