Cell selection not moving to next row while using if then in excel vba code

gm2612 · Jul 5th 2021

Column Q contains numbers, and string"Group By". Column R contains both Names (against the cells containing string), and part numbers in other rows. I want to copy only the names from column R, if column Q is value= "Group By". I have written the following code.

Sub WithDoWhileAndIf()

Range("Q2").Activate

Do While ActiveCell.Value <> ""

For i = Q2 To Q1000

If ActiveCell.Value = "Group By" Then

ActiveCell.Offset(0, 1).Copy

ActiveCell.Offset(0, -1).PasteSpecial Paste:=xlValue

ActiveCell.Offset(0, 1).Activate

ActiveCell.Offset(1, 0).Activate

End If

End Sub

It copies only the first row value, and then the selection is not moving down in column Q. Can anyone please help me to rectify the error?

Thanks a lot

GM

gm2612 · Jul 5th 2021

Wow, I have identified a solution for this. But, I want this to work only upto 1000 rows. Can experts please guide me to improve my code?

Sub WithDoWhileAndIf()

Range("Q2").Activate

Do While ActiveCell.Value <> ""

For i = Q2 To 1000

If ActiveCell.Value = "Group By" Then

ActiveCell.Offset(0, 1).Copy

ActiveCell.Offset(0, -1).PasteSpecial Paste:=xlValue

Application.CutCopyMode = False

ActiveCell.Offset(0, 1).Activate

ActiveCell.Offset(1, 0).Activate

Else

ActiveCell.Offset(1, 0).Activate

End If

End Sub

**rory** · Jul 6th 2021

Try something like this:

Code

Sub WithDoWhileAndIf()


Dim rw As Long


For rw = 2 To 1000
If Cells(rw, "Q") = "Group By" Then Cells(rw, "P").Value = Cells(rw, "R").Value
Next


End Sub

Display More

**KjBox** · Jul 6th 2021

Your explanation and code do not match. Do you want to copy the names from Column R and paste in Column P when Column Q is "Group By", leaving the "Group By" still in Column Q?

gm2612 · Jul 7th 2021

KjBox, yes, thats the way I wanted to do it. Thanks

gm2612 · Jul 7th 2021

rory, thank you so much.I am trying with it.

**KjBox** · Jul 7th 2021

Try this

It will work for just the rows in Column Q that contain data, so can extend beyond 1000 rows if your database grows in the future.

It is also an array based code that will run faster than an object based code, especially if the database is large.

Code

Sub Test()
    Dim x, i As Long, lrow As Long
    
    With ActiveSheet
        lrow = .Cells(.Rows.Count, 17).End(xlUp).Row
        x = .Cells(2, 16).Resize(lrow, 3)
        For i = 1 To UBound(x, 1)
            If x(i, 2) = "Group By" Then x(i, 1) = x(i, 3)
        Next
        .Cells(2, 16).Resize(UBound(x, 1), 3) = x
    End With

End Sub

Display More

Cell selection not moving to next row while using if then in excel vba code

Participate now!

Share

Tags