CODEWITHSUNDEEP
vbaexcel
Gregory
Gregory

Reputation: 315

Range1.Value = Range2.Value not populating

I am attempting to move ranges of cells to one row, but different columns. The code worked fine when I was copy/pasting, but the sheet slowed down dramatically. This method seems to work faster, though the actual cells are not populating. Code below:

lastRow = Cells.Find(What:="*", _
                After:=Range("A1"), _
                LookAt:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Row

    For i = 2 To lastRow
        If Cells(i, 4).Value = Cells(i + 1, 4).Value Then                   '1st if
            Debug.Print Cells(i, 4).Value & "  " & Cells(i + 1, 4).Value

            If Cells(i, 4).Value = Cells(i + 2, 4).Value Then           '2nd if
                Debug.Print Cells(i, 4).Value & "    " & Cells(i + 2, 4).Value

                If Cells(i, 4).Value = Cells(i + 3, 4).Value Then       '3rd if
                    Debug.Print Cells(i, 4).Value & "    " & Cells(i + 3, 4).Value

                    If Cells(i, 4).Value = Cells(i + 4, 4).Value Then       '4th if
                        Debug.Print Cells(i, 4).Value & "    " & Cells(i + 4, 4).Value

                        If Cells(i, 4).Value = Cells(i + 5, 4).Value Then       '5th if

                            Debug.Print Cells(i, 4).Value & "    " & Cells(i + 5, 4).Value

                        Else '~~~~~>RANGE1.VALUE = RANGE2.VALUE BELOW

                                Cells(i + 4, 52).Value = 5
                                Range(Cells(i, 40), Cells(i, 50)).Value = Range(Cells(i + 4, 53), Cells(i + 4, 63)).Value
                                Range(Cells(i + 1, 40), Cells(i + 1, 50)).Value = Range(Cells(i + 4, 64), Cells(i + 4, 74)).Value
                                Range(Cells(i + 2, 40), Cells(i + 2, 50)).Value = Range(Cells(i + 4, 75), Cells(i + 4, 85)).Value
                                Range(Cells(i + 3, 40), Cells(i + 3, 50)).Value = Range(Cells(i + 4, 86), Cells(i + 4, 96)).Value

                                i = i + 4

                             End If

Upvotes: 0

Views: 107

Answers (1)

YowE3K
YowE3K

Reputation: 23994

In Visual Basic the statement

a = b

modifies a so that it is given the value of b. It doesn't change b to be the value of a.

Based on your comment that your data is in AN:AX, it therefore seems like you intended to use:

Range(Cells(i + 4, 53), Cells(i + 4, 63)).Value = Range(Cells(i + 0, 40), Cells(i + 0, 50)).Value
Range(Cells(i + 4, 64), Cells(i + 4, 74)).Value = Range(Cells(i + 1, 40), Cells(i + 1, 50)).Value
Range(Cells(i + 4, 75), Cells(i + 4, 85)).Value = Range(Cells(i + 2, 40), Cells(i + 2, 50)).Value
Range(Cells(i + 4, 86), Cells(i + 4, 96)).Value = Range(Cells(i + 3, 40), Cells(i + 3, 50)).Value

Upvotes: 2

Related Questions