Skip Cells

[mef1sto]

Hi guys and a Happy New Year!
Please give me a helping hand in this matter:

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A7")) Is Nothing Then
    Dim lastc As String, j, firstc As String, sstep
    Dim i As Long, s As Long, e As Long
    firstc = InputBox("Fill in the first cell ex. A7")
    If Len(firstc) = 0 Then Exit Sub
        j = InputBox("First cell value, ex. 1 sau 3 etc.")
        lastc = InputBox("Last cell in asc order, ex. A10")
    If Len(lastc) = 0 Then Exit Sub
        sstep = InputBox("Value to increment cells ex. 1 sau 2 sau 3 sau cum doriti")
    Dim index As Integer
' ?? HERE I NEED ANOTHER OPTION TO SKIP UPON 1 OR MORE CELL AND ADD THE +1 INCREMENT OF THE VALUE 
    If Len(sstep) = 0 Then Exit Sub
        For i = 1 To e Step -1
        Range(firstc) = j '
        Range(firstc + i, lastc + i).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=sstep, Trend:=False
        Next i
End If
End Sub

I would attach the .xlsm file but i donno how

Thanks for your aid!

 

[Rick Rothstein]

In my code the selectionChange event is happening only when selecting that particular cell A7 (that's the starting cell and only then the inputboxes should raise).

Yes, that is true if you know that cell A7 is always the starting cell, but your first InputBox question is "Insert first starting cell eg. A7"... if you are asking the user this question, then A7 is not always the starting cell... you do not know the starting cell until the user answers this first InputBox question. But if that is the case, what triggered he SelectionChange event? It could not be cell A7 because the user has not told you that yet. That is why I said you cannot use the SelectionChange event. I do have this right, correct? Or have I misunderstood something in your question? I'm going to sleep now, so I'll pick up your response when I get up.

 

[mef1sto]

Have a good night sleep and many thanks while you at it Yes, your logic makes perfect sense, though mine is a bit "tragic" ) Well i thought i could have like a cell to begin my questions with and afterwards to change that starting cell by what pleases me and from that starting point come the other questions and the series arise. But i agree with another aproach as well but i'd prefer on the same pattern to let the user make his choises, if possible. Cheers!

 

[mef1sto]

And you know why i'd like to have control over the options, because i'm filling up with dates, and of course i prefer not to have the dates of the weekends (saturdays and sundays) so if i choise a starting date in cell A7 like e.g 6/1/2014 (d/m/y ) and i also put the last cell A23, i'll practically have a whole working week day (from 6/1/2014 to 10/1/2014) for column A and the next column would have to have starting the 13th (e.g. 13/1/2014) and end on 17/1/2014 (cell G23, column G) and so on. That's why i'm saying. Maybe you could figure out a different way to have this done. Also don't forget that maybe the user might choose a different starting cell (A7 is just as an eg. of course - i've chosen it only to have a starting point instead of a macro and to ALT F8, you know?) Good night or better good morning :D

 

[Rick Rothstein]

And you know why i'd like to have control over the options, because i'm filling up with dates, and of course i prefer not to have the dates of the weekends (saturdays and sundays) so if i choise a starting date in cell A7 like e.g 6/1/2014 (d/m/y ) and i also put the last cell A23, i'll practically have a whole working week day (from 6/1/2014 to 10/1/2014) for column A and the next column would have to have starting the 13th (e.g. 13/1/2014) and end on 17/1/2014 (cell G23, column G) and so on. That's why i'm saying. Maybe you could figure out a different way to have this done. Also don't forget that maybe the user might choose a different starting cell (A7 is just as an eg. of course - i've chosen it only to have a starting point instead of a macro and to ALT F8, you know?) Good night or better good morning :D

Oh, I see what you are saying... you want to use cell A7 as if it were a "button"... clicking it would initiate code asking where you would really like to output your dates to. I can do that for you, but, personally, I do not think that is a particularly good interface... I would use a real button and assign the macro I suggested when I thought your interface was something different to that button. Anyway, I can code this up however you want, just let me know.

 

[mef1sto]

Hi there! Well yes, that was the idea precisely, very good! Yes, you are right a button would be nicer, but it will do just the same for now the idea i brought up, so i would appreciate very much your help to my personal knowledge on this issue!

 

[Rick Rothstein]

Hi there! Well yes, that was the idea precisely, very good! Yes, you are right a button would be nicer, but it will do just the same for now the idea i brought up, so i would appreciate very much your help to my personal knowledge on this issue!

I changed your questions a little to make programming easier. Only thing I wasn't sure of (easily changed if I guessed wrong) is the number of columns... I fixed it at three because that is where we started, but if you want to make that user definable, I can add the question and change the code to account for it easily enough... just let me know. Now, as for the questions I do ask... the first question asks you to type in the address OR select the cell directly for the starting cell; the second question asks you to type in the starting value (this can be a date or just any number (I did that because we started with dates, but your latest example code showed it could by plain numbers as well); the third question asks you how much to increase the values from cell to cell downward (defaulted to 1); the fourth question asks you how many add (skip) to get to the next value when moving from column to column (if you are inputting dates and you ending cell is 5 down from your starting cell and you wanted to skip weekends, like in your original setup, just change the value to 3... the value is defaulted to 1, but can be changed easily enough); the fifth question asks you to type in the address OR select the cell directly for the last cell in the first column; and the sixth question asks you to type in the address OR select the cell directly for the top of the second column. That's it.. once you have answered those question, the three (assumed by me unless you tell me otherwise) will be generated automatically. Here is the SelectionChange event code (triggered by selecting cell A7 as you requested)...

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim Rws As Long, Cols As Long, Index As Long, Skip As Long, Increment As Long
  Dim StartValue As String, StartCell As Range, DownCell As Range, RightCell As Range
  If Target.Address(0, 0) = "A7" Then
    With Application
      On Error GoTo BadEntry
      Set StartCell = .InputBox("Please select (or type the address of) the starting cell.", Type:=8)
      StartValue = .InputBox("Please enter the starting value.", Type:=2)
      If StartValue = "" Then GoTo BadEntry
      Increment = .InputBox("How much should each cell increment by?", Default:=1, Type:=1)
      If Increment = 0 Then GoTo BadEntry
      Skip = .InputBox("Please enter the amount to skip when moving to next column.", Default:=1, Type:=1)
      If Skip = 0 Then GoTo BadEntry
      Set DownCell = .InputBox("Please select (or type the address of) the last cell in Column " & _
                               Split(StartCell.Address, "$")(1) & " that will display data.", Type:=8)
      Set RightCell = .InputBox("Please select (or type the address of) next cell in Row " & _
                                StartCell.Row & " that the data will continue on to.", Type:=8)
    End With
    Rws = DownCell.Row - StartCell.Row
    Cols = RightCell.Column - StartCell.Column
    For Cols = StartCell.Column To StartCell.Column - 1 + 3 * Cols Step Cols
      For Rws = StartCell.Row To DownCell.Row
        If IsDate(StartValue) Then
          Cells(Rws, Cols).Value = CDate(StartValue) + Index
          Cells(Rws, Cols).NumberFormat = "dd/mm/yyyy"
        Else
          Cells(Rws, Cols).Value = CDbl(StartValue) + Index
          Cells(Rws, Cols).NumberFormat = "General"
        End If
        Index = Index + Increment
      Next
      Index = Index - Increment + Skip
    Next
  End If
BadEntry:
End Sub

 

[mef1sto]

The questions are fine but still filling isn't done correctly. After finishing answering the questions it automatically fills in the cells from a7 to let's say my last cell in column A, a23 with dates increased by 1 (ALL CELLS FROM A7 to A23) and it should only fill in cell a7 then the next filled cell should be 4 cells down a11 with the values increased by 1, then another 4 cells down to a15 (with its increased cell value by 1 from previous cell a11) and so on until cell a23 and next column the same but as you said the first cell in the next column should start 3 times increased and yes, selecting the number of column would help, let's say it might depend on the month (might be of 30 or 31 days). I hope i explained correctly

 

[mef1sto]

Hey Rick! Still waiting for a solution. Any good news for me? Cheers!