excelvba

Cell Name Containing a Variable


I want to to automate the data collection of some cells of the sheet NomeSheet to fil some cells of sheet DATA, starting at row 45, collecting data for cells E45, I45, M45 and Q45 up to row 64, so I tried using for as show below, but it's not working. Data to be collected are in the cells D47 to D50 of the NomeSheet sheet. Basically I need to include the variable m in the name of the cells. Can anyone help?

Sub NOVOS_GRAFICOS_FINAL()
' NOVOS_GRAFICOS Macro
' Keyboard Shortcut: Ctrl+j
    
    Dim m As Integer
    Dim NomeSheet, EE As String

    NomeSheet = InputBox("Enter the name of the Sheet")

    Sheets("DATA").Select

    For m = 45 To 64
        Range("E & m &").Select
        Worksheets("DATA").Range("E & m &").Formula = "='" & NomeSheet & "'!$D$47"
        Range("I & m &").Select
        Worksheets("DATA").Range("I & m &").Formula = "='" & NomeSheet & "'!$D$48"
        Range("M & m &").Select
        Worksheets("DATA").Range("M & m &").Formula = "='" & NomeSheet & "'!$D$49"
        Range("Q & m &").Select
        Worksheets("DATA").Range("Q & m &").Formula = "='" & NomeSheet & "'!$D$50"
    Next

End Sub

Solution

  • It is not necessary to select a cell before updating the formula.

    How to avoid using Select in Excel VBA

    Microsoft documentation:

    Range.Resize property (Excel)

    Range object (Excel)

    Pls try

    Sub NOVOS_GRAFICOS_FINAL()
        Dim m As Integer
        Dim NomeSheet, EE As String
        NomeSheet = InputBox("Enter the name of the Sheet")
        With Worksheets("DATA")
            For m = 45 To 64
                .Range("E" & m).Formula = "='" & NomeSheet & "'!$D$47"
                ' or using Cells(row#, col#/index)
                ' .Cells(m, "E").Formula = "='" & NomeSheet & "'!$D$47"
                .Range("I" & m).Formula = "='" & NomeSheet & "'!$D$48"
                .Range("M" & m).Formula = "='" & NomeSheet & "'!$D$49"
                .Range("Q" & m).Formula = "='" & NomeSheet & "'!$D$50"
            Next
        End With
    End Sub
    

    OR

    Sub NOVOS_GRAFICOS_FINAL1()
        Dim m As Integer
        Dim NomeSheet, EE As String
        NomeSheet = InputBox("Enter the name of the Sheet")
        With Worksheets("DATA")
            .Range("E" & 45).Resize(20).Formula = "='" & NomeSheet & "'!$D$47"
            .Range("I" & 45).Resize(20).Formula = "='" & NomeSheet & "'!$D$48"
            .Range("M" & 45).Resize(20).Formula = "='" & NomeSheet & "'!$D$49"
            .Range("Q" & 45).Resize(20).Formula = "='" & NomeSheet & "'!$D$50"
        End With
    End Sub