Consulting

Results 1 to 3 of 3

Thread: Source and target cells specification in a macro

  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.

  2. #2
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    Sub mainx()
        Dim r As Range, c As Range
    
    
        With Range("A15:N100")
            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

  3. #3
    VBAX Regular
    Joined
    Aug 2017
    Posts
    57
    Location
    Thanks a lot, Mana. I tried to use this Range("A15:N100"), but since I didn't know the proper syntax, I couldn't make it work. thanks again!

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
  •