How to write range with variable in vba

Question

I am unable to write vba code where mentioned range has variable which is changing with every iteration.

Here is a code

Letter = "H"

LastRow = Cells(Rows.Count, 10).End(xlUp).Row

Set Rng = Range("J5:J" & LastRow)

For Each cell In Rng

If cell.Text <> "" Then

'ThisWorkbook.Sheets("Plg").Range("G6: N6").Value = Application.WorksheetFunction.Transpose(Rng)

ThisWorkbook.Sheets("Plg").Range("G6: Letter" & 6).Value =Application.WorksheetFunction.Transpose(Rng) ' Giving error

End If 

Letter = Chr(Asc(Letter) + 1)

Next cell

I want my code to perform like a quoted statement however it is giving an error.

so [edit] to quote the error... and preferably to indent/format your code properly too. — underscore_d, May 24 '21 at 15:37

norie · Accepted Answer · 2021-05-24T16:05:27.447

You aren't doing the concatenation properly - Letter should not be inside the quotes.

This is how the concatenation should be done.

    Letter = "H"

    LastRow = Cells(Rows.Count, 10).End(xlUp).Row

    Set Rng = Range("J5:J" & LastRow)

    For Each cell In Rng

        If cell.Text <> "" Then

            'ThisWorkbook.Sheets("Plg").Range("G6: N6").Value = Application.WorksheetFunction.Transpose(Rng)

            ThisWorkbook.Sheets("Plg").Range("G6:" & Letter & "6").Value = Application.WorksheetFunction.Transpose(Rng)    ' Giving error

        End If

        Letter = Chr(Asc(Letter) + 1)

    Next cell

You could also do it without concatenation by setting up the destination range at the start and then resizing it on each iteration of the loop.

Dim rngDst As Range
Dim rngSrc As Range

    LastRow = Cells(Rows.Count, 10).End(xlUp).Row

    Set rngSrc = Range("J5:J" & LastRow)
    Set rngDst = Sheets("Plg").Range("G6:H6")

    For Each cell In Rngsrt

        If cell.Text <> "" Then

            'ThisWorkbook.Sheets("Plg").Range("G6: N6").Value = Application.WorksheetFunction.Transpose(Rng)

            rngDst.Value = Application.WorksheetFunction.Transpose(rngSrc)    ' Giving error

        End If

        Set rngDst = rngDst.Resize(, rngDst.Columns.Count + 1)

    Next cell

You could also use Cells instead of Range as BigBen suggested.

    lngColNo = 8

    LastRow = Cells(Rows.Count, 10).End(xlUp).Row

    Set Rng = Range("J5:J" & LastRow)

    For Each cell In Rng

        If cell.Text <> "" Then

            'ThisWorkbook.Sheets("Plg").Range("G6: N6").Value = Application.WorksheetFunction.Transpose(Rng)

            With ThisWorkbook.Sheets("Plg")
                .Range("G6", .Cells(6, lngColNo)).Value = Application.WorksheetFunction.Transpose(Rng)    ' Giving error
            End With
        End If

        lngColNo = lngColNo + 1

    Next cell

Both of these options are preferable to using concatenation, mainly because updating the column letter isn't going to work beyond column Z using the current code.

How to write range with variable in vba

1 Answers1

Linked