CODEWITHSUNDEEP
vbaexcelrange
iontom
iontom

Reputation: 473

How do I loop across both rows and columns of a range in VBA?

I'm trying to create a Gantt chart type display using only cell values. Formulas will not work due to formatting so I need to have VBA run instead.

I want to loop across the both the columns and rows of a single named range. However, I don't think the VBA format is correct as this errors out "438: Object doesn't support this property or method"

Dim d As Integer
Dim e As Integer
For d = 1 To **Range("PRcal").Cols.Count**
    For e = 1 To **Range("PRcal").Rows.Count**
        If [Range("PRcal").Cells.(e, d).Value = Range("PRcal").Cells(1, d).Value] Then
            Cells(e, d).Value = Cells(e, 1)
        End If
    Next e
Next d

Any suggestions? Thanks!

Upvotes: 0

Views: 1514

Answers (1)

assylias
assylias

Reputation: 328568

  • Ranges don't have a Cols property - they do have a Columns property
  • you have an extra dot in your if in Cells.(e,d)
  • you can't use square brackets in your if

This should work fine:

Dim d As Integer
Dim e As Integer
For d = 1 To Range("PRcal").Columns.Count
    For e = 1 To Range("PRcal").Rows.Count
        If Range("PRcal").Cells(e, d).Value = Range("PRcal").Cells(1, d).Value Then
            Cells(e, d).Value = Cells(e, 1)
        End If
    Next e
Next d

Upvotes: 3

Related Questions