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]

For future questions you may ask, try and use example dates where the day number is greater than 12 so that we can see what date format you are using (m/d/y or d/m/y). Now, see if this change event code does what you want (it is not a macro, it is event code like you posted originally, but the Change event, not the SelectionChange event)...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim R As Long, C As Long, Index As Long
  If Target.Address(0, 0) = "A7" Then
    If IsDate(Target.Value) Then
      For C = 1 To 19 Step 6
        For R = 7 To 23 Step 4
          Cells(R, C).Value = Target.Value + Index
          Index = Index + 1
        Next
      Next
    End If
  End If
End Sub

Did you install it in the worksheet module , not a general module (where macros are installed), for the worksheet that you want to have this functionality?

 

[mef1sto]

Indeed, i'm so much annoyed on myself for being stupid, but you my friend are one genius i might say, thank you so much for your support and quick answer to my issue! A+

 

[mef1sto]

Dear Rick, i also tried my way but with no results. i guess it's much harder to work with both strings and long variables, isn't it? Thank you again!

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A7")) Is Nothing Then
    Dim lastc As String, j, firstc, firstcc As String, sstep
     Dim R As Long, C As Long, Index As Long
    firstc = InputBox("Introduceti prima celula de start ex. A7")
    firstcc = InputBox("Introduceti prima celula de start ex. A7")
    If Len(firstc) = 0 Then Exit Sub
        j = InputBox("Introduceti valoarea primei celule, ex. 1 sau 3 etc.")
        lastc = InputBox("Introduceti ultima celula pana la care se va face numerotarea ascendenta, ex. A10")
    If Len(lastc) = 0 Then Exit Sub
        sstep = InputBox("Introduceti numarul cu care se va incrementa valoarea ex. 1 sau 2 sau 3 sau cum doriti")
    If Len(sstep) = 0 Then Exit Sub
    If IsDate(firstc.Value) Then
        Range(firstc) = j
        'For C = 1 To 19 Step 6
        'For R = 7 To 23 Step 4
        '  Cells(R, C).Value = Target.Value + Index
        '  Index = Index + 1
        For C = firstc To lastc Step 6 ' here i had the idea of something like Range(firstcell on first column, lastcell on last column)
        For R = firstc To firstcc Step 4 ' here is the firstcell and lastcell on the same column
        Range(C, R).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=sstep, Trend:=False 'furthermore i'm not so sure if my logic is correct but it isn't working!!
        Range(C, R).Value = Target.Value + Index
        Index = Index + 1
        Next
        Next
    End If
End If
End Sub

 

[Rick Rothstein]

Dear Rick, i also tried my way but with no results. i guess it's much harder to work with both strings and long variables, isn't it? Thank you again!

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A7")) Is Nothing Then
    Dim lastc As String, j, firstc, firstcc As String, sstep
     Dim R As Long, C As Long, Index As Long
    firstc = InputBox("Introduceti prima celula de start ex. A7")
    firstcc = InputBox("Introduceti prima celula de start ex. A7")
    If Len(firstc) = 0 Then Exit Sub
        j = InputBox("Introduceti valoarea primei celule, ex. 1 sau 3 etc.")
        lastc = InputBox("Introduceti ultima celula pana la care se va face numerotarea ascendenta, ex. A10")
    If Len(lastc) = 0 Then Exit Sub
        sstep = InputBox("Introduceti numarul cu care se va incrementa valoarea ex. 1 sau 2 sau 3 sau cum doriti")
    If Len(sstep) = 0 Then Exit Sub
    If IsDate(firstc.Value) Then
        Range(firstc) = j
        'For C = 1 To 19 Step 6
        'For R = 7 To 23 Step 4
        '  Cells(R, C).Value = Target.Value + Index
        '  Index = Index + 1
        For C = firstc To lastc Step 6 ' here i had the idea of something like Range(firstcell on first column, lastcell on last column)
        For R = firstc To firstcc Step 4 ' here is the firstcell and lastcell on the same column
        Range(C, R).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=sstep, Trend:=False 'furthermore i'm not so sure if my logic is correct but it isn't working!!
        Range(C, R).Value = Target.Value + Index
        Index = Index + 1
        Next
        Next
    End If
End If
End Sub

Being that the text in the MessageBoxes are coming out in a language other than English, I am afraid I cannot figure out what you expect the user to enter nor what should xpect to be done with it later on in your code? My guess is you can do what you want, it is just I cannot figure out what that is from your MessageBox texts.

 

[mef1sto]

Oh...i didn't check the code i pasted, i'm sorry. I'll translate:

Public Sub Worksheet_SelectionChange(ByVal Target As Range)If Not Intersect(Target, Range("A7")) Is Nothing Then    Dim lastc As String, j, firstc, firstcc As String, sstep     Dim R As Long, C As Long, Index As Long    firstc = InputBox("Insert starting cell eg. A7")    firstcc = InputBox("Insert starting cell on column A eg. A7")    If Len(firstc) = 0 Then Exit Sub        j = InputBox("Insert the value of the starting cell eg. 1 or 3 ..")        lastc = InputBox("Insert last cell eg. A10")    If Len(lastc) = 0 Then Exit Sub        sstep = InputBox("Insert the number to increse the value eg. 1 or 2 or 3 or whatever")    If Len(sstep) = 0 Then Exit Sub    If IsDate(firstc.Value) Then        Range(firstc) = j        'For C = 1 To 19 Step 6        'For R = 7 To 23 Step 4        '  Cells(R, C).Value = Target.Value + Index        '  Index = Index + 1        For C = firstc To lastc Step 6 ' here i had the idea of something like Range(firstcell on first column, lastcell on last column)        For R = firstc To firstcc Step 4 ' here is the firstcell and lastcell on the same column        Range(C, R).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=sstep, Trend:=False 'furthermore i'm not so sure if my logic is correct but it isn't working!!        Range(C, R).Value = Target.Value + Index        Index = Index + 1        Next        Next    End IfEnd IfEnd Sub</PRE>

 

[Rick Rothstein]

Oh...i didn't check the code i pasted, i'm sorry. I'll translate:

Public Sub Worksheet_SelectionChange(ByVal Target As Range)If Not Intersect(Target, Range("A7")) Is Nothing Then    Dim lastc As String, j, firstc, firstcc As String, sstep     Dim R As Long, C As Long, Index As Long    firstc = InputBox("Insert starting cell eg. A7")    firstcc = InputBox("Insert starting cell on column A eg. A7")    If Len(firstc) = 0 Then Exit Sub        j = InputBox("Insert the value of the starting cell eg. 1 or 3 ..")        lastc = InputBox("Insert last cell eg. A10")    If Len(lastc) = 0 Then Exit Sub        sstep = InputBox("Insert the number to increse the value eg. 1 or 2 or 3 or whatever")    If Len(sstep) = 0 Then Exit Sub    If IsDate(firstc.Value) Then        Range(firstc) = j        'For C = 1 To 19 Step 6        'For R = 7 To 23 Step 4        '  Cells(R, C).Value = Target.Value + Index        '  Index = Index + 1        For C = firstc To lastc Step 6 ' here i had the idea of something like Range(firstcell on first column, lastcell on last column)        For R = firstc To firstcc Step 4 ' here is the firstcell and lastcell on the same column        Range(C, R).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=sstep, Trend:=False 'furthermore i'm not so sure if my logic is correct but it isn't working!!        Range(C, R).Value = Target.Value + Index        Index = Index + 1        Next        Next    End IfEnd IfEnd Sub

Can you try pasting it again (like however you did it in Message #13) as your code became a jumbled mess?

 

[mef1sto]

I wanted to let the user choose its beginning starting cell, first value of that cell, the index number that increses the value, the last cell untill he fills the series. He cares to choose these parameters, you see?

 

[mef1sto]

Here it is:

Dear Rick, i also tried my way but with no results. i guess it's much harder to work with both strings and long variables, isn't it? Thank you again!

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A7")) Is Nothing Then
    Dim lastc As String, j, firstc, firstcc As String, sstep
     Dim R As Long, C As Long, Index As Long
    firstc = InputBox("Insert first starting cell eg. A7")
    firstcc = InputBox("Insert first starting cell  eg. A7") 'first cell on column A because i wanted to create the range(A,S)
    If Len(firstc) = 0 Then Exit Sub
        j = InputBox("Insert the value of the first cell eg. 1 or 3 etc.")
        lastc = InputBox("Insert the last cell eg. A10")
    If Len(lastc) = 0 Then Exit Sub
        sstep = InputBox("Insert the number to increment the value eg. 1 or 2 or 3 or whatever") 
    If Len(sstep) = 0 Then Exit Sub
    If IsDate(firstc.Value) Then
        Range(firstc) = j
        'For C = 1 To 19 Step 6
        'For R = 7 To 23 Step 4
        '  Cells(R, C).Value = Target.Value + Index
        '  Index = Index + 1
        For C = firstc To lastc Step 6 ' here i had the idea of something like Range(firstcell on first column, lastcell on last column)
        For R = firstc To firstcc Step 4 ' here is the firstcell and lastcell on the same column
        Range(C, R).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=sstep, Trend:=False 'furthermore i'm not so sure if my logic is correct but it isn't working!!
        Range(C, R).Value = Target.Value + Index
        Index = Index + 1
        Next
        Next
    End If
End If
End Sub

 

[Rick Rothstein]

If you are going to ask the user to specify the starting cell and the value to place in it, then I do not think using event code will be practical. The SelectionChange event that you show in your non-working code would not be good because every time the user clicked anywhere on the sheet, InputBoxes would pop up in his/her face. And since the user has to specify the starting cell and its value, there would be nothing happening to raise the Change event (nothing is changing until the InputBoxes get answered which means the code has to already be running). We could have the Change event monitor every cell so if the user entered a value into a cell, it would be assumed to be the starting cell and starting value, but then the worksheet would be pretty useless afterwards (you could not type anything into any cells on the worksheet with the Change event assuming that you want to run the code again. If you still want to proceed, I think it will have to be with a macro that the user has to run manually somehow (ALT+F8, or a button its assigned to, etc.). Is that the course of action you want to pursue?

 

[mef1sto]

Well that's what i'm after, yes. 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). That's the idea. Otherwise a macro will do, if my way won't prove right.