CODEWITHSUNDEEP
excelvba
ShrimpCrackers
ShrimpCrackers

Reputation: 4532

Excel VBA - Assign row to variable and manipulate it?

I've been searching how to assign a row to a variable and manipulate cells through the variable but I can't seem to find how to do this.

x = Sheet5.Range("A1").EntireRow
    MsgBox x(1, 1)

The above code will get me the row into 'x', but is there any way that I can change a cells value using the variable 'x'? x(1,1) = "foo" will not work, and since it's not an object I can't access .Value.

Upvotes: 1

Views: 9595

Answers (1)

Doug Glancy
Doug Glancy

Reputation: 27478

Here's some sample code:

Sub Ranging()
Dim rng As Excel.Range
Dim ws As Excel.Worksheet

Set ws = ActiveSheet
Set rng = ws.Range("A1").EntireRow
    With rng
        Debug.Print .Cells(1).Value
        Debug.Print .Cells(5).Address
        .Cells(43).Value = "SurfN'Turf"
    End With
End Sub

Debug.Print prints to the VBE's Immediate Window (access with Ctrl-G)

Upvotes: 2

Related Questions