Userform Not Closing

[Ark68]

I am trying to shift a range of cells defined by (example) ws_cd..Range(.Cells(cdrow, 45), .Cells(cdrow, 93)), where cdrow = 2, left 7 spaces. So, I am attempting to move cells AS2:CO2 seven cells to the left, to AL2. With this code below, this is not happening. Nothing appears to be happening. Looking for someone to kindly show me my errors.

This is the code I'm using:

With ws_cd
        .Unprotect
        .Range(.Cells(cdrow, cdsvc_col), .Cells(cdrow, cdsvc_col + 6)).Value = ""
        If index < 8 Then
            If index = 1 Then
                .Range("AL" & cdrow) = .Range(.Cells(cdrow, 45), .Cells(cdrow, 93)).Offset(0, -7)
            ElseIf index = 2 Then 'move services 3-8 left 7 cells
                .Range("AS" & cdrow) = .Range(.Cells(cdrow, 52), .Cells(cdrow, 93)).Offset(0, -7)
            ElseIf index = 3 Then 'move services 4-8 left 7 cells
                .Range("AZ" & cdrow) = .Range(.Cells(cdrow, 59), .Cells(cdrow, 93)).Offset(0, -7)
            ElseIf index = 4 Then 'move services 5-8 left 7 cells
                .Range("BG" & cdrow) = .Range(.Cells(cdrow, 66), .Cells(cdrow, 93)).Offset(0, -7)
            ElseIf index = 5 Then 'move services 6-8 left 7 cells
                .Range("BN" & cdrow) = .Range(.Cells(cdrow, 73), .Cells(cdrow, 93)).Offset(0, -7)
            ElseIf index = 6 Then 'move services 7-8 left 7 cells
                .Range("BU" & cdrow) = .Range(.Cells(cdrow, 80), .Cells(cdrow, 93)).Offset(0, -7)
            Else 'index = 7 Then 'move services 8-8 left 7 cells
                .Range("CB" & cdrow) = .Range(.Cells(cdrow, 80), .Cells(cdrow, 93)).Offset(0, -7)
            End If
        End If
        .Protect
    End With

 

[RoryA]

So if cdrow = 2, then a line like this:

.Range("AL" & cdrow) = .Range(.Cells(cdrow, 45), .Cells(cdrow, 93)).Offset(0, -7)

is equivalent to:

.Range("AL2").Value = .Range("AS2:CO2").Offset(0, -7).Value

Since 7 columns to the left of AS2:CO2 is AL2:CH2, and if you assign an array to one cell, you just get the first value of the array, your original code boils down to:

.Range("AL2").Value = .Range("AL2").Value

which is why you don't see anything happen.

 

[Ark68]

Thank you Rory for your reply, despite the fact that my subject had nothing to do with my question even remotely. It was a rement of the previous message I was creating.

I am trying to interpret your lesson , but honestly, I'm having difficulty

Based on my interpretation, I tried this, but this was very incorrect. Instead of sending the array to one cell, I enlarged the single cell to the same size as the range.

.Range(.Cells(cdrow, 38), .Cells(cdrow, 79)) = .Range(.Cells(cdrow, 52), .Cells(cdrow, 93)).Offset(0, -7)

 

[Ark68]

I found another way of doing ...

rng_shft.Cut .Cells(cdrow, cdsvc_col)

Where rng_shft is the predefined range of cells that I wanted shifted 7 cells to the left. Instead of trying to figure out offset (never relied on unfortunately), this does what I need.