Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: updating values macro problem

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am trying to update the value of a cell if it matches the value in another cell:

    Sub update()
    Range("J18,J20,J22").Copy
    Do Until ActiveCell.Value = ""
    If ActiveCell.Value = Range("J18") Then
    ActiveCell.PasteSpecial _
    Transpose:=True
    Else
    ActiveCell.Offset(1, 0).Activate
    End If
    Loop
    End Sub

    It works if I dont use the "Do until" loop but without the loop it isn't much use.
    If I use the loop then it carries on repeating itself, its probably a simple problem but which part of the code needs adjustment?
    Thanks

  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-28 15:00, P5X wrote:
    I am trying to update the value of a cell if it matches the value in another cell:

    Sub update()
    Range("J18,J20,J22").Copy
    Do Until ActiveCell.Value = ""
    If ActiveCell.Value = Range("J18") Then
    ActiveCell.PasteSpecial _
    Transpose:=True
    Else
    ActiveCell.Offset(1, 0).Activate
    End If
    Loop
    End Sub

    It works if I dont use the "Do until" loop but without the loop it isn't much use.
    If I use the loop then it carries on repeating itself, its probably a simple problem but which part of the code needs adjustment?
    Thanks
    You have to either make sure that you have the correct cell selected before you start your macro, or select it w/in the code itself, something like this:

    Cells(1,1).Select
    ' or
    Range("A1").Select

    Hope this helps,

    Russell

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yeah, I haven't put that into the code yet as i'm just testing it so i'm manually selecting the start cell but it doesn't seem to work, once it finds the matching cell it pastes the required data into it again and again...and again

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Chippenham, UK
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sub update()
    Range("J18,J20,J22").Copy
    Do Until ActiveCell.Value = ""
    If ActiveCell.Value = Range("J18") Then
    ActiveCell.PasteSpecial _
    Transpose:=True
    Else
    ActiveCell.Offset(1, 0).Activate
    End If
    Loop
    End Sub

    Where you have ActiveCell.Offset(1,0).Activate this moves the cell down one if the cell doesn't match your requirements.

    You don't have this in the part if it does match your requirements, so I guess when it gets to a cell that is true then it will keep pasting over the same cell, try:

    Sub update()
    Range("J18,J20,J22").Copy
    Do Until ActiveCell.Value = ""
    If ActiveCell.Value = Range("J18") Then
    ActiveCell.PasteSpecial _
    Transpose:=True
    ActiveCell.Offset(1, 0).Activate ' This moves down one row.
    Else
    ActiveCell.Offset(1, 0).Activate
    End If
    Loop
    End Sub

    Make sure that after pasting the offset is putting you back into an Activecell in the right place, i.e. column and row.


    Regards,

    Gary Hewitt-Long

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, that was what was wrong with it

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •