Trying To Understand OFFSET in VBA

[Ark68]

With reference to this code the code below.
It copies and pastes a range of cells to the cell defined by svcRow and svcCol. In our example, assume svcRow and svcCol = 3 and 10 respectively. (J3)
From there, I want to populate cell J7 with the value on that worksheet at cell M1. I am doing this with my failed use of offset. Using J3 as the base cell, I want to put the value of M1 at the same column (10) but at row 7. So, no offset of column, but an offset of 4 rows.

My cell (J7) isn't populating with the value of M1. Where have I gone wrong?

    Dim LkFor As Variant
    Dim dstart As Long, dend As Long
    Dim ws As String
    
    'Diamonds
    With ws_front
        .Unprotect
        svcNum = 0
        dstart = 0
        dend = 0
        LkFor = "D"
        ws = "Master"
        FirstAndLastRows ws, LkFor, dstart, dend
        Stop
        For i = dstart To dend
            svcNum = svcNum + 1
            .Range("G2") = svcNum
            .Range("E2") = Right(ws_master.Cells(i, 1), 3)
            svcRow = Application.WorksheetFunction.VLookup(svcNum, ws_lists.Range("A1:C51"), 2, False)  '3
            svcCol = Application.WorksheetFunction.VLookup(svcNum, ws_lists.Range("A1:C51"), 3, False)   '10 (J)
            rngTemp.Copy Destination:=.Cells(svcRow, svcCol)
            .Cells(svcRow, svcCol).Offset(4, 0) = .Range("A1")  'destination M7? ... nothing
            .Cells(svcRow, svcCol).Offset(4, 16) = ws_master.Cells(i, 6) 'destination Y7? ... nothing
            .Cells(svcRow, svcCol).Offset(4, 22) = ws_master.Cells(i, 6) 'destination AE7? ... nothing
        Next i
        .Protect
    End With
End Sub

 

[rlv01]

I want to populate cell J7 with the value on that worksheet at cell M1.

Using your example of svcRow and svcCol = 3 and 10 respectively, this line

.Cells(svcRow, svcCol).Offset(4, 0) = .Range("A1")  'destination M7? ... nothing

references cell J7 on the left side of the equation, and is the same as writing

.Range("J7") = .Range("A1")

If you want to populate J7 with the value of M1 then use

.Cells(svcRow, svcCol).Offset(4, 0) = .Range("M1")

 

[Ark68]

rlv01, hello and thank you.

That solved that, but now the remaining two offset commands are not populating the cells as needed.

.Cells(svcRow, svcCol).Offset(4, 16) = ws_master.Cells(i, 6) 'destination Y7? ... nothing
.Cells(svcRow, svcCol).Offset(4, 22) = ws_master.Cells(i, 7) 'destination AE7? ... nothing

I am trying to populate cell Y7 with the value in worksheet 'mw_master', cell F13 (i=13) based on .cells(svcRow,svcCol) = .cells(3,10) (J10)
I am trying to populate cell AE7 with the value in worksheet 'mw_master', cell G13 (i=13) based on .cells(svcRow,svcCol) = .cells(3,10) (J10)

There are values in both F13 and G13 of worksheet 'ws_master' as tested in the immediate window.

I'm still not doing something right.

 

[rlv01]

I am trying to populate cell Y7 with the value in worksheet 'mw_master', cell F13 (i=13) based on .cells(svcRow,svcCol) = .cells(3,10) (J10)

You have to learn how to assess what cell/range you are specifying when you use Offset. So for example, in this line:

.Cells(svcRow, svcCol).Offset(4, 16) = ws_master.Cells(i, 6) 'destination Y7? ... nothing

You say you want .Cells(svcRow, svcCol).Offset(4, 16) to resolve to Y7. How do you know if it is doing that? The answer is to test it, and a simple way to do that is to insert a Debug.Print statement like this:

Debug.Print .Cells(svcRow, svcCol).Offset(4, 16).address

Which will display the cell address in the VBE 'Immediate' window, and will tell you that it actually resolves to Z7, not Y7, and therefore you should adjust your line of code to:

.Cells(svcRow, svcCol).Offset(4, 15) = ws_master.Cells(i, 6) 'destination Y7? ... nothing

Analyzing the other lines using Offset is similar.

 

[Ark68]

Thank you rvl01 for the explanation and ongoing support. The test has helped, although I've been doing it in the immediates window before I actually write my code.