none
Fill down using macro, but missing out a row after each number RRS feed

  • Question

  • I'm trying to create a macro that will automatically fill down information from another sheet in the same workbook.  I can get it just to fill down in number order, but I want it to miss out a row in between each number in one column?

    Basically I want it to look something like this:

    A1   A2

    B1 1 Right

    B2 Left

    B3 2 Right

    B4 Left

    I'd also like the numbers in column A to be merged so that each number has a right and left applied to it.  I know it has something to do with counters, but everything I've tried so far isn't registered or makes it go into confused mode and throws me out.

    Thanks muchly

    Monday, November 3, 2014 9:35 PM

Answers

  • Hi Nya,

    >>I can get it just to fill down in number order, but I want it to miss out a row in between each number in one column?

    Basically I want it to look something like this:

    A1   A2

    B1 1 Right

    B2 Left

    B3 2 Right

    B4 Left<<

    Did you mean that you want 'B1' at cell A1 and 'B2' at A3 and so on. If I understand correctly, the 'Fill Down' feature provide by Excel application is not suitable to achieve the goal. We can write a subroutine to do that and here is a sample for your reference:

    Sub FillDown()
    For i = 1 To 10
        Range("A" & (i - 1) * 2 + 1).Value = "B" & i
    Next i
    End Sub


    >>I'd also like the numbers in column A to be merged so that each number has a right and left applied to it. <<

    Did you mean that you want to attach 'left' or 'right' behind the content of column A? If I understando correctly, you can refer to the code below:

    Sub FillDown()
    For i = 1 To 10
        
        If i Mod 2 = 1 Then
           Range("A" & (i - 1) * 2 + 1).Value = "B" & i & "left"
        Else
           Range("A" & (i - 1) * 2 + 1).Value = "B" & i & "right"
        End If
    Next i
    End Sub

    Also if you are interested in the build in function provide by Excel, you can use Record Macros feature get the code for referencing. And here is the Excel object model for your reference:
    Object model reference (Excel 2013 developer reference)

    If I misunderstood, please feel free to let me know.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 5, 2014 6:15 AM
    Moderator
  • Hi Fei, now that i've tried this it's actually the opposite.  

    Background to it is that I have am running some trials and want to be able to create sheets that are specific for each study/part of body.

    Each study has x amount of patients (using a totalN value from an info sheet) and for each patient we are looking at the right and left part of the body.

    Am I just trying to make things too complicated?  

    Thanks

    Nya

    p.s. i have had a bit of a route around in the link you attached, but I haven't got much time to devote to finding the answer unfortunately.


    SJ Welsted

    • Marked as answer by Nya82 Saturday, November 29, 2014 11:55 AM
    Tuesday, November 18, 2014 9:24 PM

All replies

  • Hi Nya,

    >>I can get it just to fill down in number order, but I want it to miss out a row in between each number in one column?

    Basically I want it to look something like this:

    A1   A2

    B1 1 Right

    B2 Left

    B3 2 Right

    B4 Left<<

    Did you mean that you want 'B1' at cell A1 and 'B2' at A3 and so on. If I understand correctly, the 'Fill Down' feature provide by Excel application is not suitable to achieve the goal. We can write a subroutine to do that and here is a sample for your reference:

    Sub FillDown()
    For i = 1 To 10
        Range("A" & (i - 1) * 2 + 1).Value = "B" & i
    Next i
    End Sub


    >>I'd also like the numbers in column A to be merged so that each number has a right and left applied to it. <<

    Did you mean that you want to attach 'left' or 'right' behind the content of column A? If I understando correctly, you can refer to the code below:

    Sub FillDown()
    For i = 1 To 10
        
        If i Mod 2 = 1 Then
           Range("A" & (i - 1) * 2 + 1).Value = "B" & i & "left"
        Else
           Range("A" & (i - 1) * 2 + 1).Value = "B" & i & "right"
        End If
    Next i
    End Sub

    Also if you are interested in the build in function provide by Excel, you can use Record Macros feature get the code for referencing. And here is the Excel object model for your reference:
    Object model reference (Excel 2013 developer reference)

    If I misunderstood, please feel free to let me know.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 5, 2014 6:15 AM
    Moderator
  • Hello Nya,

    my answer would be a case in a loop. If you follow a certain pattern then 

       loopvarone = RoundDown(Rownum / 4, 0)
       loopvartwo = Rownum - RoundDown(Rownum / 4, 0) * 4
       
       While loopvarone <= RoundDown(Rownummax / 4, 0)
       Case loopvartwo = 1
       perform1
       Rownum = Rownum + 1
       loopvarone = RoundDown(Rownum / 4, 0)
       loopvartwo = Rownum - RoundDown(Rownum / 4, 0) * 4
       Case loopvartwo = 2
       perform2
       Rownum = Rownum + 1
       loopvarone = RoundDown(Rownum / 4, 0)
       loopvartwo = Rownum - RoundDown(Rownum / 4, 0) * 4
       Case loopvartwo = n
       performn
       Rownum = Rownum + 1
       loopvarone = RoundDown(Rownum / 4, 0)
       loopvartwo = Rownum - RoundDown(Rownum / 4, 0) * 4
       Case Else
       MsgBox ("lol")
       Loop

    should be just fine; the perform would still contain a case criterion: columnnum -> case 1, case 2.

    Cheers!

    Wednesday, November 5, 2014 1:25 PM
  • Thanks for both ways of doing it.  I will give them a try and see how I get on :-)

    SJ Welsted

    Tuesday, November 18, 2014 8:12 PM
  • Hi Fei, now that i've tried this it's actually the opposite.  

    Background to it is that I have am running some trials and want to be able to create sheets that are specific for each study/part of body.

    Each study has x amount of patients (using a totalN value from an info sheet) and for each patient we are looking at the right and left part of the body.

    Am I just trying to make things too complicated?  

    Thanks

    Nya

    p.s. i have had a bit of a route around in the link you attached, but I haven't got much time to devote to finding the answer unfortunately.


    SJ Welsted

    • Marked as answer by Nya82 Saturday, November 29, 2014 11:55 AM
    Tuesday, November 18, 2014 9:24 PM
  • Hi Nya,

    >>Background to it is that I have am running some trials and want to be able to create sheets that are specific for each study/part of body.

    Each study has x amount of patients (using a totalN value from an info sheet) and for each patient we are looking at the right and left part of the body.<<

    I am not able to understand the issue excactly. Would you mind share some screenshot about the deitail of this issue? Or Could you separate the technolege issue from the requriment?

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, November 20, 2014 9:01 AM
    Moderator
  • Hi Fei,

    Sorry it's taken me so long to reply.  I can't give to much info I'm afraid from the actual spreadsheets I use, but essentially I want to be able to create the above along with other sheets that are created at the start of each study.  

    I currently am using macros to create the other sheets which automatically fill down the group number and patient no using this formula, but because I don't need to merge any cells so it can just run straight down:

    'Below: Loops filling in the group and animal number

    Do While counter < TotalPatientN

        Do While counter3 < PatientGroupMax
        Sheets("Skin Checks").Cells(8 + counter, 4).Value = CurrentGroup
        Sheets("Skin ").Cells(8 + counter, 5).Value = counter + PatientStart

    and draws the info from the image below

    Does that help you to gain an idea of how I might be able to create the sheets I want?  My main aim is to reduce the potential for copy and past mistakes from previous studies or other sheets.  I also aim to be able to get the sheets to also automatically assign each group a colour on each sheet so that it links with the treatment pages, however that can wait for now.

    Thanks and regards

    Nya



    SJ Welsted

    Saturday, November 29, 2014 12:12 PM
  • Did you want to merge cells and convert the range from second figure to the first figure? If yes, you need to merger the cells before set value. You can use Range.Merge cells, here is an example to merger cell A1 and A2:

    Range("A1:A2").Merge

    Monday, December 1, 2014 8:24 AM
  • Yeah I had thought of the merge, but it ended up being a massive list of code which isn't what I want really.  I can have up to 100 patients for the study and ideally I want the macro to literally create the sheet to suit each study without having to manually put the info in myself.  Probably asking for the world, but thought I'd give it a go.

    Thanks for the suggestion though, I'll keep playing about with the different formulas and see what I get.

    Nya


    SJ Welsted

    Tuesday, December 2, 2014 11:51 AM