[B][color=Royalblue]Sub[/color][/B] a1077651a()
[i][color=seagreen]'https://www.mrexcel.com/forum/excel-questions/1077651-finding-matching-positive-negative-numbers.htmlDim i As Long, z As Long, ch As Long, f As Long[/color][/i]
[B][color=Royalblue]Dim[/color][/B] rng [B][color=Royalblue]As[/color][/B] Range
[B][color=Royalblue]Dim[/color][/B] m [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]String[/color][/B]
[B][color=Royalblue]Dim[/color][/B] va [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Variant[/color][/B], vx [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Variant[/color][/B], s [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Variant[/color][/B]
[B][color=Royalblue]Dim[/color][/B] d [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Object[/color][/B]
[B][color=Royalblue]Dim[/color][/B] rr [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Long[/color][/B], f [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Long[/color][/B], g [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Long[/color][/B], i [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Long[/color][/B], z [B][color=Royalblue]As[/color][/B] [B][color=Royalblue]Long[/color][/B]
Application.ScreenUpdating = [B][color=Royalblue]False[/color][/B]
rr = Range([color=brown]"A"[/color] & Rows.count).[B][color=Royalblue]End[/color][/B](xlUp).row
vx = Range([color=brown]"A1:A"[/color] & rr + [color=crimson]1[/color])
[B][color=Royalblue]For[/color][/B] f = [color=crimson]2[/color] [B][color=Royalblue]To[/color][/B] UBound(vx, [color=crimson]1[/color]) - [color=crimson]1[/color]
g = f
[B][color=Royalblue]Do[/color][/B] [B][color=Royalblue]While[/color][/B] vx(f, [color=crimson]1[/color]) = vx(f + [color=crimson]1[/color], [color=crimson]1[/color])
f = f + [color=crimson]1[/color]
[B][color=Royalblue]Loop[/color][/B]
[B][color=Royalblue]If[/color][/B] f = g [B][color=Royalblue]Then[/color][/B] [B][color=Royalblue]GoTo[/color][/B] [B][color=Royalblue]skip[/color][/B]:
[B][color=Royalblue]Set[/color][/B] rng = Range(Cells(g, [color=brown]"C"[/color]), Cells(f, [color=brown]"C"[/color]))
va = rng
[B][color=Royalblue]Set[/color][/B] d = CreateObject([color=brown]"scripting.dictionary"[/color])
[B][color=Royalblue]For[/color][/B] i = [color=crimson]1[/color] [B][color=Royalblue]To[/color][/B] f - g + [color=crimson]1[/color]
z = va(i, [color=crimson]1[/color])
[B][color=Royalblue]If[/color][/B] d.Exists(z) [B][color=Royalblue]Then[/color][/B]
d(z) = d(z) & [color=brown]","[/color] & i
[B][color=Royalblue]ElseIf[/color][/B] d.Exists(-z) [B][color=Royalblue]Then[/color][/B]
s = Split(d(-z), [color=brown]","[/color])
m = s(UBound(s))
va(i, [color=crimson]1[/color]) = va(i, [color=crimson]1[/color]) & [color=brown]"#"[/color]
va(m, [color=crimson]1[/color]) = va(m, [color=crimson]1[/color]) & [color=brown]"#"[/color]
[B][color=Royalblue]If[/color][/B] UBound(s) = [color=crimson]0[/color] [B][color=Royalblue]Then[/color][/B]
d.Remove -z
[B][color=Royalblue]Else[/color][/B]
d(-z) = Left(d(-z), Len(d(-z)) - Len(m) - [color=crimson]1[/color])
[B][color=Royalblue]End[/color][/B] [B][color=Royalblue]If[/color][/B]
[B][color=Royalblue]Else[/color][/B]
d(z) = i
[B][color=Royalblue]End[/color][/B] [B][color=Royalblue]If[/color][/B]
[B][color=Royalblue]Next[/color][/B] i
rng = va
[B][color=Royalblue]skip[/color][/B]:
[B][color=Royalblue]Next[/color][/B] f
[B][color=Royalblue]Set[/color][/B] rng = Range([color=brown]"C1"[/color], Cells(Rows.count, [color=brown]"C"[/color]).[B][color=Royalblue]End[/color][/B](xlUp))
Application.ReplaceFormat.Interior.Color = vbYellow
rng.Replace What:=[color=brown]"#"[/color], Replacement:=[color=brown]""[/color], LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=[B][color=Royalblue]True[/color][/B], _
SearchFormat:=[B][color=Royalblue]False[/color][/B], ReplaceFormat:=[B][color=Royalblue]True[/color][/B]
rng.Replace What:=[color=brown]"#"[/color], Replacement:=[color=brown]""[/color], LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=[B][color=Royalblue]True[/color][/B], _
SearchFormat:=[B][color=Royalblue]False[/color][/B], ReplaceFormat:=[B][color=Royalblue]False[/color][/B]
Application.ReplaceFormat.Clear
Application.ScreenUpdating = [B][color=Royalblue]True[/color][/B]
[B][color=Royalblue]End[/color][/B] [B][color=Royalblue]Sub[/color][/B]