Is there a way to skip lines (insert value every other line)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike - 21 May 2008 18:38 GMT

I have a macro that will prompt me with boxes and create a list of numbers
but I am looking for a way to get the macro to put the values into every
other cell. Any help would be great!

Here’s what I have so far:

Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
a = InputBox("How many samples?", "How many samples")
b = InputBox("Split where?", "Split after what number?")

a1 = 1
b1 = b
For i = 1 To b
Worksheets("button to make list").Range("A2").Offset(i - 1, 0).Value = a1
a1 = a1 + 1
Next

Reply to this Message

Herrick Andrews - 21 May 2008 19:36 GMT

After your "for" line, put in "Step 2" I.e.,

> Range("A2").Select
> Range(Selection, Selection.End(xlDown)).Select
[quoted text clipped - 8 lines]
> a1 = a1 + 1
> Next

Note, that you have a variable a and another variable b1 that you never use.
I'm assuming this is just a snippet and they are used elsewhere.

BTW, I think you are writing code the hard way. I've rewritten your code
below in what I think is an easier approach:

Columns(2).ClearContents
a = InputBox("How many samples?", "How many samples")
b = InputBox("Split where?", "Split after what number?")

a1 = 1
For i = 1 to b Step 2
Worksheets("button to make list").Cells(i, 2) = a1
a1 = a1 + 1
Next i

Or better yet:

Option Explicit

Const WS_SAMPLES = "button to make list"

Sub FillSamples()
Dim a1 As Long
Dim b As Long
Dim i As long
Dim wsSamples As Excel.Worksheet

Set wsSamples = ThisWorkbook.Worksheets(WS_SAMPLES)

b = InputBox("Split where?", "Split after what number?")
a1 = 1

For i = 1 to b Step 2
wsSamples.Cells(i, 2) = a1
a1 = a1 + 1
Next i

Set wsSamples = Nothing

End Sub

> I have a macro that will prompt me with boxes and create a list of numbers
> but I am looking for a way to get the macro to put the values into every
[quoted text clipped - 14 lines]
> a1 = a1 + 1
> Next

Reply to this Message

Mike - 21 May 2008 20:36 GMT

The Step 2 worked great thanks!

I am sure I am doing this the hard way so thanks for the help and insight
into better ways to do it

Mike

> After your "for" line, put in "Step 2" I.e.,
>
[quoted text clipped - 71 lines]
> > a1 = a1 + 1
> > Next

Reply to this Message

mikebres - 21 May 2008 19:41 GMT

I haven't tried it but it seems that adding the Step 2 should work

a1 = 1
b1 = b
For i = 1 To b Step 2
Worksheets("button to make list").Range("A2").Offset(i - 1, 0).Value = a1
a1 = a1 + 1
Next

Reply to this Message