Do Until LOOP Help

[rockchalk33]

Geniuses,

I am having a bit of trouble getting a piece of code to loop. I have a button that once clicked will do exactly this:




If Duplicate_Find Is Nothing Then
ActiveCell.Copy
ActiveCell.Offset(0, 2).Select
ActiveCell.PasteSpecial
ActiveCell.Offset(1, -2).Select
Else: ActiveCell.Offset(1, 0).Select
End If




I need this to start on Range ("A6") and loop until the first cell that is empty.

This is the code I tried:




Public Button1_Click()

Range ("A5").Select
Do Until IsEmpty (ActiveCell)
ActiveCell.Offset (1,0)
If Duplicate_Find Is Nothing Then
ActiveCell.Copy
ActiveCell.Offset(0, 2).Select
ActiveCell.PasteSpecial
ActiveCell.Offset(1, -2).Select
Else: ActiveCell.Offset(1, 0).Select
End If
Loop

End Sub






Every time I click Button_1 it automatically jumps down to the first empty cell and never copied any data over.

Any help would be appreciated, should be an easy fix.

Thanks all!

Devin

 

[rockchalk33]

It's still not entirely clear what you want, but as a guess, try:

Dim SrchMacro()

    Dim x       As Long
    Dim rng     As range
    Dim rngF    As range
    Dim rngSrc  As range
    Dim rngArea As range
    
    Application.ScreenUpdating = False
    
    Set rngArea = range("C:DL")
    
    x = Cells(rows.count, 1).End(xlUp).row
    
    Set rngSrc = range("A6").Resize(x-5)
    
    For Each rng In rngSrc
        With rng
            Set rngF = rngArea.find(what:=.value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)
            
            If Not rngF Is Nothing Then
                .Offset(, 1).value = .value
                Set rngF = Nothing
            End If
        End With
    Next rng
    
    Application.ScreenUpdating = True
    
    Set rngSrc = Nothing
    Set rngArea = Nothing
    
End Sub

I won't have an opportunity to test this code out until late afternoon today. Below I will try my best to show exactly what I am attempting to do with a before code is ran description and an after code is ran description. Thanks for continued input!


Before code is ran, A-DL are columns:

A B C D E...................DL
23 33 09
56 10 14 23
89 56
09
12

After code is ran, code will go down column A and if the value in column A is anywhere within C:DL, it will copy and paste the exact value in Column B of the same row in which the value is found in Column A:

A B C D E...................DL
23 23 33 45 09
56 56 10 14 23
89 56 38
09 09 175
12

Hope this is not confusing as it looks correct on my computer screen

 

[JackDanIce]

I'd avoid using the entire range C:DL, that's 119,537,664 cells the code has to search, each time it loops.

If you can define the last used row (based on a specific column), it would make the code run a little faster.

 

[rockchalk33]

I won't have an opportunity to test this code out until late afternoon today. Below I will try my best to show exactly what I am attempting to do with a before code is ran description and an after code is ran description. Thanks for continued input!


Before code is ran, A-DL are columns:

A B C D E...................DL
23 33 09
56 10 14 23
89 56
09
12

After code is ran, code will go down column A and if the value in column A is anywhere within C:DL, it will copy and paste the exact value in Column B of the same row in which the value is found in Column A:

A B C D E...................DL
23 23 33 45 09
56 56 10 14 23
89 56 38
09 09 175
12

Hope this is not confusing as it looks correct on my computer screen

Now I'm replying to my own comments lol but in the above description theres a few errors I made and I can't figure out how to go in an edit my post:

-In this example, if the numbers are bolded, they are part of column B, all the other values are to the right of column A and found in C-DL

-column B is empty until the code is ran, once the code is ran column B is populated

-This is what the code should do: Going down column A, if the value in column A is found anywhere in the range from C-DL, it is copied from column A and then put into Column B of the same row as it is in Column A

Hope this is clear as mud

 

[rockchalk33]

I'd avoid using the entire range C:DL, that's 119,537,664 cells the code has to search, each time it loops.

If you can define the last used row (based on a specific column), it would make the code run a little faster.

Jack, I have to go from C:DL with that I'm given, pretty gross I know. Each column represents a separate entity of which has a populated list of unique values. I'm simply looking for specific values that both column A and the rest of the range have in common and then making a separate column storing that data.

 

[JackDanIce]

Code I suggested should achieve what you require, it assumes the data together with column A values are all on the same sheet, which is active when you run the macro.

 

[rockchalk33]

Code I suggested should achieve what you require, it assumes the data together with column A values are all on the same sheet, which is active when you run the macro.

JackDanIce,

Your code worked flawlessly above earlier in the thread! I need some more help if you'd be willing, I know it's been a while since you've commented on here, but this could really help me if it is not too much trouble!

I need to make two small changes to my program and thus your code above.... I'm not quite sure how.

1.) Instead of column B containing everything in Column A that is ALSO found in Column C:DL, I need it to rather contain everything in A that is NOT found in C:DL

2.) I feel like this is the harder part of the two...... I need the entire cell's contents to be viewed in Column B and not just the cell's values, i.e there are certain cells with background colors and those should be represented in Column B as well as the value.

I've tried going in doing this myself and am running out of ideas of how to do so. I feel as it shouldn't be but a few small changes here and there but I can't seem to pick them out.

Thanks again for the awesome help,

Devin