Challenge for the VB experts

[g_purve]

Hello, I am trying to re-lable the first column of data into sections, usually 60 sample increments, in order to post process all of the samples that are contained within the recorded section.
So I have a worksheet with column showing test step = usually ranging from 0-10 and the data recorded is displayed in the other columns.

There are usually 60 samples recorded per test step.

Currently I am having to select 60 cells and input the test step value manually into the cells then manually select the next 60 and input the test step value until I reach the end of the test.

I have a user input window that asks the operator how many data points are recorded in each test step, then stores the answer as variable.
Then I have a user input window that asks the operator how many test steps are performed, then stores the answer as variable.

I am having trouble because I can't get the for loop to properly lable the test step number more than one time.
Please help

 

[jproffer]

If you post your code (that is hopefully indented and easy to follow ) using the code tags...(or copy this ---> [ code ]paste here[ /code ], and remove the spaces) then someone would probably be able to help you.

 

[g_purve]

Private Sub CommandButton2_Click()
    Dim Target          As Range
    Dim Cancel          As Boolean
    Dim Rng1            As Range
    Dim Prompt          As String
    Dim Title           As String
    Dim Completed       As Long
    Dim Total           As Long
    Dim Samples         As Long
    Dim Chunks          As Long
    Dim i               As Long
    Dim Range1End       As Long
 
' first user window to determine # of samples per test step
    Prompt = "How many samples recorded per step?"
    Title = "Samples Recorded Per Step"
    Samples = InputBox(Prompt, Title)
 
' second user window to determine # of test steps performed
    Prompt = "How many total steps did you perform?"
    Title = "Total Number of Steps"
    Chunks = InputBox(Prompt, Title)
 
    i = Chunks  ' initiating first test step
 
    For i = Chunks To 0 Step -1
        Worksheets("Sheet1").Activate
        Sheet1.Range("A34:A34+Samples").Select ' I can't get the range selected correctly
        Range1End = Selection.rowend.Value
        Selection.Value = i
 
    Next i

 

[jproffer]

Private Sub CommandButton2_Click()
    Dim Target          As Range
    Dim Cancel          As Boolean
    Dim Rng1            As Range
    Dim Prompt          As String
    Dim Title           As String
    Dim Completed       As Long
    Dim Total           As Long
    Dim Samples         As Long
    Dim Chunks          As Long
    Dim i               As Long
    Dim Range1End       As Long
    Dim Rws As Long
 
' first user window to determine # of samples per test step
    Prompt = "How many samples recorded per step?"
    Title = "Samples Recorded Per Step"
    Samples = InputBox(Prompt, Title)
    Rws = Samples + 34
' second user window to determine # of test steps performed
    Prompt = "How many total steps did you perform?"
    Title = "Total Number of Steps"
    Chunks = InputBox(Prompt, Title)
 
    i = Chunks  ' initiating first test step
 
    For i = Chunks To 0 Step -1
        Worksheets("Sheet1").Activate
        Sheet1.Range("A34:A" & Rws).Select ' I can't get the range selected correctly
        Range1End = Selection.rowend.Value
        Selection.Value = i
 
    Next i

If it's always A34, try that. If it's not always A34, we'll have to change it up a bit.

 

[g_purve]

Thanks jproffer, it at least makes the correct selection. The first row of data is always row 34 but how do I get it to move to the next chunk of data and increment the test step without re-labeling the initial test step.
Right now if you put in 60 for number of samples and 3 for the number of test steps it will originally label the data as test step 3 then it labels it as test step 2 then test step 1 then test step 0.

 

[jproffer]

I'm not really following that question...I see the loop for "chunks", but the way you describe it, it's doing what you told it to do here:

For i = Chunks to 0

speaking of that, you can get rid of the "i = Chunks" line. When you start the For/Next loop, it would do that anyway.



...but in the mean time, this:

Range1End = Selection.rowend.Value

means nothing...rowend, if anything, would be a variable. But used that way would either error out, or just do nothing.

 

[g_purve]

Thanks for the reply, I must be missing something in the loop because it is only working with the first chunk of data. Meaning that it is only working with the first number of samples , most of the time it is 60 seconds which is rows A34 to A.
I get the same result no matter what value I give the variable "chunks" because it never advances to the next group rows A to A.

For the Range1End code, you are right it does just error out.
I was trying to create a variable that reads the rownumber, in most case it will be 93, so I can replace the range selection starting point A34 with the Range1End+1 which in most cases will be A94.
Thanks for the help so far and sorry for the confusion.

 

[jproffer]

If you want to find the first available row at the beginning of each runtime, try this.

LR = Range("A" & Rows.Count).End(xlUp).Row + 1

then use LR as the variable row to start on.



I think...that was typed here and not in a code module, but I think that's right....looks right

 

[g_purve]

How would it be easiest to make the loop execute on more than the first test step? so it would select the cells A34 to A93 and give them the value 1, then select cells A94 to A153 and give them the value of 2, then select cells A154 to A213 and give them a value of 3, and so on and so on.
Thanks,

 

[jproffer]

I think I'm starting to pick up what you're trying to do....I think. You want to number rows in column A?? Starting with 0 up to the number of "chunks"?

So if there are 60 samples and 3 chunks, you'd have 60 "0"'s then below that 60 "1"'s, then "2"'s, then "3"'s....right?

If that's the case, try this:

Sub test()
Dim Samples As Long
Dim Chunks As Long
Dim Prompt As String
Dim Title As String
Dim Startrow As Long
Dim i As Long
    Prompt = "How many samples recorded per step?"
    Title = "Samples Recorded Per Step"
    
    Samples = InputBox(Prompt, Title)
    
    Prompt = "How many total steps did you perform?"
    Title = "Total Number of Steps"
    
    Chunks = InputBox(Prompt, Title)
    
    Startrow = 34
    
    
    For i = 0 To Chunks
        Sheets("Sheet1").Range("A" & Startrow & ":A" & Startrow + Samples).Value = i
        Startrow = Startrow + Samples
    Next i
        
End Sub

It may not be exactly there yet, so test it on a COPY of your workbook. See how close we are, then we can adjust.