I have created a new worksheet but I am not unable to set the cell value in the new worksheet in a loop.

Dim dueWs As Worksheet

Sub CreateDue()
Dim i, intsal As Integer
Dim firstDue As String

Set dueWs = Sheets.Add
dueWs.Name = "Due date Table"

firstDue = Range("B11").Value
instal = Range("B7").Value

With dueWs
    .Range("A1").Value = "Instalment"
    .Range("B1").Value = "Due date"

    For i = 1 To instal
        .Range("A" & i   1).Value = i
        .Range("B" & i   1).Value = firstDue
        firstDue = DateAdd("m", 1, firstDue)
    Next i
End With

End Sub

CodePudding user response：

Create a Sequential List

• Use Option Explicit, it will force you to declare all variables. It would have warned you about the "intsal" typo.
• When declaring multiple variables in one line, each has to have an As clause or it will be declared As Variant, e.g.
  Dim i As Long, Instal As Long, firstDue As Date.
• Every sheet has a workbook so reference it and use it to qualify the sheet with e.g.
  Set ws = wb.Sheets(...).
• Every range has a worksheet so reference it and use it to qualify the range with e.g.
  Set rg = ws.Range(...).
• Not a big issue, but you could write the last two lines in the loop using a single one:
  .Range("B" & i 1).Value = DateAdd("m", i - 1, firstDue).

Option Explicit

Sub CreateDue()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code

    Dim unoWs As Worksheet: Set unoWs = wb.Sheets("You know its name")

    Dim Instal As Long, firstDue As Date
    
    With unoWs
        Instal = .Range("B7").Value
        firstDue = .Range("B11").Value
    End With

    Dim dueWs As Worksheet: Set dueWs = Sheets.Add

    Dim i As Long
    
    With dueWs
        .Name = "Due date Table"

        .Range("A1").Value = "Instalment"
        .Range("B1").Value = "Due date"

        For i = 1 To Instal
            .Range("A" & i   1).Value = i
            .Range("B" & i   1).Value = firstDue
            firstDue = DateAdd("m", 1, firstDue)
        Next i
    
        .Columns("A:B").AutoFit
    End With

    MsgBox "Due date table created.", vbInformation
 
End Sub

CodePudding user response：

Being said what in my comment, you could also avoid looping:

Option Explicit

Sub CreateDue()

    Dim mainSh As Worksheet
        Set mainSh = ActiveSheet ' change 'ActiveSheet' to your actual "main" data worksheet
    
    With Sheets.Add ' add and reference a new sheet
    
        .Name = "Due date Table" ' name the referenced sheet

        .Range("A1:B1").Value = Array("Instalment", "Due date") ' write range A1:B1 of the referenced sheet

        With .Range("A2").Resize(mainSh.Range("B7").Value, 2) ' reference a range in the referenced sheet starting from A2 with two columns and as many rows as per "main" sheet cell B7 value
            .Columns(1).FormulaR1C1 = "=ROW()-1 ' write the first column of the referenced range"
            .Columns(2).FormulaR1C1 = "=" & mainSh.Name & "!R11C2  RC[-1]-1" ' write the second column of the referenced range"
            .Value = .Value ' leave only values in the referenced range
        End With
    
    End With
 
End Sub