Results 1 to 4 of 4

Thread: Help with some code.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help with some code.

    Hi all that know more than me. Now I have a question regarding some code and how to make it work. I have written most of the code and am stuck on just 2 lines. I created the following code and the module below is called upon from the main code. The problem is because it is set to look at the same 2 cells it is not running thru the columns. How would I need to write this code so that it changes from the set ''M9'' and ''N9''?

    Sub CopyRange()
    '
    ' This copies the desired range into the Rank% cells


    'Select coloum where numbers are needed to replace A2 and B2 values
    Range("M9:N9").Cells.Select
    Range("N9").Cells.Activate

    'Copies the selection
    Selection.Copy


    'Pastes the copied values to A2 and B2
    Range("A2:B2").Select
    Range("B2").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    End Sub

    Thanks for any help in advance.

  2. #2
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    772
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with some code.

    The first thing is that your code is code which is produced by the macro recorder, this is not very efficient code, the following:
    Range("M9:N9").Cells.SelectRange("N9").Cells.Activate


    'Copies the selection
    Selection.Copy




    'Pastes the copied values to A2 and B2
    Range("A2:B2").Select
    Range("B2").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Can be replaced by a single line :
    Code:
    Range("M9:N9").Copy Destination:=Range("A2:B2")
    However this doesn't do a paste values it does a complete copy including formatting, if you just want the values then these two lines will do it:
    Code:
    inarr = Range("M9:N9")
    Range("A2:B2") = inarr
    your question seems to be how you can change this to change the source and destination of this copy and paste, the way to do this is with indices.
    The following code picks up the value in cells A1 and the row for the source of the copy and the the value in B1 as the row for the destination.
    So put 9 in A1 and 2 in B1 and the code will do the same as you original . change A1 to 10 and it will copy the two cells from row 10 instead.
    Code:
    Sub test2()
    ' this is cell A1 using just number references
    si = Cells(1, 1)
    ' this is cells B1 using number references
    di = Cells(1, 2)
    Range("M" & si & ":N" & si).Copy Destination:=Range("A" & di & ":B" & di)
    End Sub
    Note I have also used an alternative way of addressing cells where instead of using column letters I am using column numbers eg; cells (1,2) instead of B1 , this makes it much easier to write a loop that runs from column A to J which is columns 1 to 10
    the format of this addressing mode is Cells ( row number , column number) I tend to use this all the time in vba
    Last edited by offthelip; Apr 10th, 2018 at 07:18 PM.

  3. #3
    New Member
    Join Date
    Mar 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with some code.

    Thank you for your reply. What I have is 3335 rows and I need to run an advanced copy filter over those rows. I start at row 9 (rows 1 to 8 have other references) with the values of M9 and N9 (created by a macro recording) and then doing a special paste into the range of A1 to B2 being my range. The problem with my code is that when I step thru it to check although the row changes to row 10, 11, 12 and so on I still get the values from M9 and N9 whereas I need it to change to row 10 and extract the values of M10 and N10 and on the next loop M11 and N11 and all the way to row 3335. I have tried searching online with no clear explanation.

    Hope that is clearer.

  4. #4
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    772
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with some code.

    I think I have understood what you want: this should do it. It copies rows sequentially from columns M and N to A2:b2 starting at row 9 and then ending at row 3335
    Code:
    Sub test2()
    For si = 9 To 3335
        di = 2
        Range("M" & si & ":N" & si).Copy Destination:=Range("A" & di & ":B" & di)
     ' place your other code in here
        
    Next si
    
    
    End Sub

Some videos you may like

User Tag List

Tags for this Thread

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
  •