Excel 2007 VBA to select range of cells

Question

I am attempting to select a range of cells. I have done this before but am having trouble with the syntax.

Sub ChgDateX()

Range("A41").Select

Do
    If ActiveCell.Value = "Last Updated" Then
    mydate = ActiveCell.Offset(-40, 0).Value

    'Cells(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 9)).Select
    ActiveCell.Offset(0, 1).Value = mydate
    ActiveCell.Offset(0, 2).Value = mydate
    ActiveCell.Offset(0, 3).Value = mydate
    ActiveCell.Offset(0, 4).Value = mydate
    ActiveCell.Offset(0, 5).Value = mydate
    ActiveCell.Offset(0, 6).Value = mydate
    ActiveCell.Offset(0, 7).Value = mydate
    ActiveCell.Offset(0, 8).Value = mydate
    ActiveCell.Offset(0, 9).Value = mydate

    ActiveCell.EntireRow.Select
    Selection.NumberFormat = "m/d/yyyy"
    ActiveCell.Offset(1, 0).Select
        Else: ActiveCell.Offset(1, 0).Select
    End If
Loop Until ActiveCell.Value = "" & ActiveCell.Offset(-3, 0).Value = ""

End Sub

I am trying to get away from the individual offset = mydate type coding.

Tim I have a date housed from the active cell, .Offset(-1,-39) That I want to overwrite the existing values in the range group I am having trouble referencing. — Desert Spider
– Desert Spider, Commented Dec 17, 2012 at 19:55
I have not placed the additional code for the passing of the If statment. — Desert Spider
– Desert Spider, Commented Dec 17, 2012 at 19:56

Joseph · Accepted Answer · 2012-12-17 20:34:32Z

Couple of things:

This code:

Loop Until ActiveCell.Value = "" & ActiveCell.Offset(-3, 0).Value = ""

Doesn't work as expected because the correct operator you're looking for is And and not &

You don't have to "Select" anything. You can just put a reference to a cell in a variable (see my code).

Also, since you are always moving down to the next cell in the loop, you can put that outside of the IF statement.

Based on your code I think you're looking for something like this:

Option Explicit

Sub test()
    Dim r As Range
    Dim myDate As Date

    Set r = Range("A41")

    Do
        If (r.Value = "Last Updated") Then
            myDate = r.Offset(-40, 0).Value

            With Range(r.Offset(0, 1), r.Offset(0, 9))
                .Value = myDate
                .NumberFormat = "m/d/yyyy"
            End With
        End If

        Set r = r.Offset(1, 0)
    Loop Until r.Value = vbNullString And r.Offset(-3, 0).Value = ""

End Sub

John Bustos · Accepted Answer · 2012-12-17 20:21:15Z

0

Try this:

ActiveCell.Range("B1:J1").Value = MyDate

answered Dec 17, 2012 at 20:21

John Bustos

19.7k18 gold badges102 silver badges162 bronze badges

Comments

Scott Holtzman · Accepted Answer · 2012-12-17 20:32:18Z

0

How about this ... with 1 note. I wouldn't advise the looping like this. Perhaps you can filter on Column A <> "" or something, then just loop through the visible cells? Hard to say without knowing what you are doing.

Option Explicit

Sub ChgDateX()

Range("A41").Select

Do
    If ActiveCell.Value = "Last Updated" Then

        Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 9)).Value = ActiveCell.Offset(-40, 0).Value

        ActiveCell.EntireRow.NumberFormat = "m/d/yyyy"

    End If

    ActiveCell.Offset(1).Select

Loop Until ActiveCell.Value = "" & ActiveCell.Offset(-3, 0).Value = ""

End Sub

answered Dec 17, 2012 at 20:32

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Collectives™ on Stack Overflow

Excel 2007 VBA to select range of cells

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related