Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to skip lines (insert value every other line)
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! Heres 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to skip lines (insert value every other line)
After your "for" line, put in "Step 2" I.e.,
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 Step 2 ' <<<<< Worksheets("button to make list").Range("A2").Offset(i - 1, 0).Value = a1 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 "Mike" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to skip lines (insert value every other line)
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 "Mike" wrote: 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! Heres 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to skip lines (insert value every other line)
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 "Herrick Andrews" wrote: After your "for" line, put in "Step 2" I.e., 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 Step 2 ' <<<<< Worksheets("button to make list").Range("A2").Offset(i - 1, 0).Value = a1 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 "Mike" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert line every x lines until end of data | Excel Programming | |||
macro to insert blank line when lines sum to zero | Excel Discussion (Misc queries) | |||
How do I copy cells and skip lines? | Excel Discussion (Misc queries) | |||
how do you skip lines when reading a text file | Excel Programming | |||
Excel skip some lines of VBA code | Excel Programming |