Offset and Copying ranges

[biotechguy]

I am very new to VBA, but as a function of my job I have become the person that is learning it.

So to my question:

What I need is a macro which will search a column (AI) in excel looking for the text "complete". Upon finding this word, I need the macro to select a cell offset by 35 columns, and 7 rows. And then select a range from the offset cell. Cut the range selected, and paste into another worksheet. There could be multiple selections that could say complete, and the same method needs to be applied to all.

Example: "complete" is in cell AI8 of worksheet titled "Submissions in Progress", and I want the macro to select A1:AB8, cut it and paste it into another worksheet titled "Completed Submissions". And then continue to search in column AI for more "complete" text

Thanks

 

[lenze]

WOW! That's a big chunk for someone new to VBA!!! Fortunately, it's doable.

Let's go slow as you implied you wanted to learn. The first step is looking for the value "COMPLETE" in Column AI.

Sub FindComplete()
Dim cl as Range
Range("$AI:$AI").Select
For each cl in Selection
If cl.Value = "COMPLETE" Then MsgBox ("Found one in " & cl.Address)
Next cl
End Sub

You don't really have to select the cells to work with them, but this way is a little easier to see when first learning VBA.
Place this code in a regular module and Run it.
When you get it working, post again and the next step will be to select the cells to be copied.

lenze

 

[biotechguy]

Thank you so much for going slow and helping me out. That worked great as expected. Since you are being so nice as to do it step by step, could you add comments to the sections to better help me understand. Thanks again

 

[lenze]

Thank you so much for going slow and helping me out. That worked great as expected. Since you are being so nice as to do it step by step, could you add comments to the sections to better help me understand. Thanks again

Yes, I will, but I am probably going to be offline for the rest of the day. Maybe some one else will pick it up, or I will follow-up when I have the chance.

The next part would be something like

If cl.Value = "COMPLETE" Then 
       MsgBox ("Found one in " & cl.Address) 
       cl.Offset(-7,-34).Resize(8,28).Copy  'OR cut
       ' Then select the range to paste to and paste. You can get the syntex by recording a macro while doing it.     
End If
Next cl

Good luck
lenze

 

[biotechguy]

thanks lenze. I will try it all out. If you could check next time you are online, it would be much appreciated. Thanks again

 

[biotechguy]

thanks lenze. I will try it all out. If you could check next time you are online, it would be much appreciated. Thanks again

 

[lenze]

Hi Bio" Give this one a shot

Sub FindAndCopy()
Dim cl As Range
Application.ScreenUpdating = False 'Turn off screen updating to eliminate flashing
For Each cl In Range("$AI:$AI") 'Tell Excel what range to check
If UCASE(cl.Value) = "COMPLETE" Then
    MsgBox ("Found one in " & cl.Address) 'Identifies cells with "COMPLETE" Not neccesary to the code
    cl.Offset(-7, -34).Resize(8, 28).Cut ' Selects Range to cut Offset identifies 1st cell in range Resize adjust the range accordingly
    Sheet3.Activate 'Activates the sheet to paste to. Change as needed.
    Dim lastRow As Integer
    'Identify and select the first blank cell in Column A on Sheet3 beginning with row 2
    lastRow = ActiveSheet.Range("$A65536").End(xlUp).Row
    Cells(lastRow + 1, 1).Select
    'Paste the results from Sheet1
    ActiveSheet.Paste
End If
Next cl
'Return to Sheet1
'Change if needed
Sheet1.Activate
Range("$A$1").Select
Application.ScreenUpdating = True 'Reset ScreenUpdating
End Sub

You can remove the MsgBox line if not needed. Also, this assumes "COMPLETE" will be in every 8th Row beginning with AI8. Adjust if needed. I also made the code non case sensitive for "COMPLETE"

HTH

lenze

 

[biotechguy]

That did the trick, thanks. This message board is sooo good. I will be asking more questions I bet.