# Fill down using macro, but missing out a row after each number

• ### 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

• 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.

Wednesday, November 5, 2014 6:15 AM
• 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 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.

Wednesday, November 5, 2014 6:15 AM
• 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 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.

Thursday, November 20, 2014 9:01 AM
• 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