how to relay on a number in a cell and list columns based on that number in vba

Question

Basically, I have list of credits in a spreadsheet. Each credit heading on the sheet is displayed as Credit(5) and it displays below that 5 credits i.e:

Credit(5)
Cre1
Cre2
Cre3
Cre4
Cre5

Then I have another heading with Credit(3) and that displays 3 credits below it i.e:

Credit(3)
Cre1
Cre2
Cre3

Now my question is how to do this in VB and relay on the numbers (3) and (5) and display below the heading list according to the number in the heading? so in other words have 5 columns below the heading if heading has (5) and 3 for the other one.

Not sure what you want to do. Just getting the numbers 5 and 3 from the headings is easy, but what do you want to do with the numbers? Enter the text `Cre1 ... Cre5` in the rows below the heading or what? — Olle Sjögren, Mar 08 '17 at 12:47
Just record a macro when you do it manually. But I suspect that is not what you really want to do, and your real requirements are more involved. Please read the HELP topics for [How do I Ask a Good Question](http://stackoverflow.com/help/how-to-ask), and also [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) — Ron Rosenfeld, Mar 08 '17 at 12:49
I want to copy those columns to a different page but instead of doing it cell by cell i want to copy according to the heading so if it says (5) then display columns cred1 to cre5 on that separate sheet as well as the the heading if that makes sense? — Moza, Mar 08 '17 at 12:52
I believe that you actually want to copy 3 or 5 _rows_ from that _column_ to the other work[sheet|book]. Follow @RonRosenfeld advice and record a macro while you do it by hand. From there you can start to modify it to A) identify how many rows to pick up and B) how many columns to process. When you get stuck on a specific step, show your code and ask for help on that specific thing. — FreeMan, Mar 08 '17 at 13:01
Use the Right or Split function to get the number from the cell, then store that in a variable and make a loop using that variable. — Luuklag, Mar 08 '17 at 13:08

score 0 · Accepted Answer · edited May 23 '17 at 12:00

0

Something like this? Credit to paxdiablo here. Edited.

Private Sub CommandButton1_Click()
    Dim sTitle As String
    Dim openPos As Integer
    Dim closePos As Integer
    Dim midBit As Integer
    Dim i As Integer
    Dim k As Integer

    For k = 1 To 50
        sTitle = Worksheets("Sheet1").Cells(1, k)
        If sTitle <> "" Then
            openPos = InStr(sTitle, "(")
            closePos = InStr(sTitle, ")")
            midBit = Mid(sTitle, openPos + 1, closePos - openPos - 1)

            For i = 1 To midBit
                Worksheets("Sheet1").Cells(i + 1, k) = "cre" & i
            Next i
        End If
    Next k
End Sub

edited May 23 '17 at 12:00

Community

1
1

answered Mar 08 '17 at 13:09

CMArg

1,525
3
13
28

So instead of sTitle = Worksheets("Sheet1").Cells(1, 1) and select only cell 1,1 I want to look from A1 to A50 for any titles (because theres more than one title heading) I get type mismatch message when I do sTitle = Worksheets("Sheet1").Range("A1:A50") – Moza Mar 08 '17 at 15:56
See edited answer. – CMArg Mar 08 '17 at 16:23

how to relay on a number in a cell and list columns based on that number in vba

1 Answers1