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.
Code:
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!
