Posts by gm2612

    I have this set of code for removing spaces before numbers in a range of cells. This set of codes work fine when I am testing with pressing f8 button. But, it is throwing error when testing with pressing f5 button. Error thrown in this particular line - cell.Value = Right(tmp, Len(tmp) - 2)


    Experts please help to debug this problem


    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

    Next

    Loop

    End Sub

    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

    Next

    Loop

    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