Results 1 to 3 of 3

Thread: Source and target cells specification in a macro

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Regular
    Joined
    Aug 2017
    Posts
    57
    Location

    Arrow Source and target cells specification in a macro

    I have the following macro which works fine. I've been trying to specify the source cells (rows and columns) and the target cells(rows and columns) in the code but so far I was unsuccessful.

    Sub mainx()
        Dim r As Range, c As Range
        With Cells(1).CurrentRegion
            With .Offset(1).Resize(.Rows.Count - 1)
                For Each r In .Cells
                    Set c = .Find(r.Value, r, , 1, , , 2)
                    If (c.Address <> r.Address) Then   
                        If c.Row > r.Row Then           
                            r.Offset(, 40) = c.Row - r.Row - 1
                        Else
                            Do
                                Set c = .FindNext(c)
                            Loop While c.Row = r.Row And c.Address <> r.Address
                            If c.Row > r.Row Then
                                r.Offset(, 40) = c.Row - r.Row - 1
                            Else
                                r.Offset(, 40) = "na"
                            End If
                        End If
                    Else
                        r.Offset(, 40) = "na"
                    End If
                Next
            End With
        End With
    End Sub

    The current code generates the results in the second row and after 40th column, and the code works on all the values until 40th column.
    I need to make the code work on the data between A15:N100 (source cells), and output the results in AO15:BB100 (target cells). So that the values in between won't be affected by the code.

    How can I achieve this, please? Can I get a little help? Many thanks!
    Last edited by Mati44; 06-23-2019 at 10:15 AM.

Tags for this Thread

Posting Permissions

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