PDA

View Full Version : [SOLVED] Source and target cells specification in a macro



Mati44
06-23-2019, 05:34 AM
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!

mana
06-24-2019, 01:43 AM
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

Mati44
06-24-2019, 02:45 AM
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!