Macro in Excel Stops in one cell-CodePudding

I'm new in VBA and I am having a small problem. This is my code:

Sub my_first_macro()
    Range("D4") = "My text Here"
    Range("I4") = "1"
    Range("D5").Select
End Sub

The idea of this macro is to have a button associated to it

that would fill the cell D4 with "My text Here" AND I4 with "1".
When I hit for the second time it would fill the cell D5 with "My text Here" AND J4 with "1".
And then, when I hit for the third time it would fill the cell D6 with "My text Here" AND K4 with "1".
And then, when I hit for the fourth time it would fill the cell D7 with "My text Here" AND L4 with "1".

It's really simple but I am not able to solve this.

Any help?

CodePudding user response：

To make the value of the currently selected cell "1" and select the cell underneath it you can do:

Sub my_macro()
    Selection.Value = "1"
    Selection.Offset(1, 0).Select
End Sub

CodePudding user response：

Shifting Row and Column at the Same Time

Modify the cCell and rCell ranges to see its flexibility.

Option Explicit

Sub my_first_macro()
    
    Dim cCell As Range: Set cCell = Range("D4")
    Dim rCell As Range: Set rCell = Range("I4")
    
    Dim cfRow As Long: cfRow = cCell.Row
    Dim cCol As Long: cCol = cCell.Column
    
    Dim rRow As Long: rRow = rCell.Row
    Dim rfCol As Long: rfCol = rCell.Column
    
    Dim cLastRow As Long: cLastRow = Cells(Rows.Count, cCol).End(xlUp).Row
    Debug.Print cLastRow, cfRow
    
    If cLastRow < cfRow Then
        cCell.Value = "My text Here"
        rCell.Value = "1"
    Else
        Cells(cLastRow   1, cCol).Value = cCell.Value
        Cells(rRow, cLastRow   rfCol - cfRow   1).Value = rCell.Value
    End If
    
End Sub

CodePudding user response：

One possible solution: I've commented my code.

Public Sub shift_text_and_numbers()

'Use lngCounter as static variable, that means it keeps its value under following conditions:
'1. if the sub is finished and called again and 
'2. as long as there occurs no error in your vba-project
    
    Static lngCounter As Long

'Provide your content here

    Dim strText As String: strText = "My text here"
    Dim lngNumber As long: lngNumber = 1

'Set the first cell for each shift

    Dim rngStartCellText As Range: Set rngStartCellText = Worksheet1.Range("D4")
    Dim rngStartCellNumber As Range: Set rngStartCellNumber = Worksheet1.Range("I4")

'Calculates the next cell depending on lngCounter and the first cells, represents also your sheme

    Dim rngNextCellText As Range: Set rngNextCellText = Worksheet1.Cells(rngStartCellText.Row   lngCounter, rngStartCellText.Column)
    Dim rngNextCellNumber As Range: Set rngNextCellNumber = Worksheet1.Cells(rngStartCellNumber.Row, rngStartCellNumber.Column   lngCounter)
    
'Write the content in the next cells

    rngNextCellText.Value = strText
    rngNextCellNumber.Value = lngNumber

'Count up for the next call

    lngCounter = lngCounter   1

End Sub

Thanks @VBasic2008 for learning a new way to declare variables lovely.