0

I made macro that find cell which include text in other cell. I needed to list them to other place by using space of other two row this is example

enter image description here

IF text of A1 is included in B1 paste A1 to C1 and B1 to D1 like this patern

compare A1 to B row and paste based cells to "C" row and including text cell to "D" row

    Sub killerb()
    
 dim kinder as range
dim lookfor as range
set kinder = sheets(11).Range("C1:C29995")
set lookfor = sheets(11).Range("B1:B29995")

for i = 1 to 29995
if not kinder(i) =""then
foir c =1 to 29995
if instr(1, lookfor(c).value, kinder(i).value) >0 then
sheets(11).cells(i+t,4)= kinder(i).value
sheets(11).cells(i+t,5) =lookfor(c).value
t = t+1
end if
next
end if
next
    End Sub

it works but there are too many empty cells between cells that contain text. and its keep on lagging

please help

낙하산
  • 11
  • 1
  • `=IF(NOT(ISERROR(SEARCH(A1,B1,1))),A1,"")` would be a formula way. Search or Find can be used. What is happening with the code? It is lagging? Why do you not check lookfor(c) for "" ? – Nathan_Sav Apr 16 '21 at 07:15
  • range "B" don't have "" so i didn't check. and thank you for your reply. Do you know make this code to using [for each] ? its lagging because it took so long. I think the problem is that i used [for]. – 낙하산 Apr 16 '21 at 08:49
  • Your code will not be very fast because you are interacting with the worksheet cells so many times. This would run faster if you first create arrays instead of ranges, loop through them in memory, and then print the results in the correct column. I’m not sure if I know enough about why you have the ‘t+i’ part of your code to help. – Evan Friedland Apr 16 '21 at 09:12
  • Why is there two for loops? Aren’t you comparing A1 and B1, not A1 and B28000? You could remove one loop, and remove the “t” variable and see what happens too. Array should do this in less than a second. – Evan Friedland Apr 16 '21 at 09:15
  • I need to compare "A:A" and "B:B" whole row. I need to find and list cells that matching whith "A1"... in "B:B" – 낙하산 Apr 19 '21 at 00:37

0 Answers0