File automation problem (VBA)

YounesB3 · #1 09-21-2012, 02:38 PM

Hello I'm stuck in the automation of a file (kind of new in VBA). Here is the file information:

- Columns A-B + D-L + Q all have formulas based on column C and another external excel file.
- Columns M-P have manual data entered by several different people.
- In order to update column C without affecting the manual data entered in columns M-P, I've made 3 columns U-V-W:
- I paste in column U the new data which needs to be replaced in column C.
- Column V is a vlookup of cell C??? in column U. If I obtain an error, then it means the data in column C should be deleted.
- Column W is a vlookup of cell U??? in column C. If I obtain an error, then it means the data in column U needs to be added to the end of column C.

With filters in place, I can easily update the file, but it takes about 30 minutes each morning (including some other sheets).

I started to automate the file and this is where I'm stuck (I replaced some names below):

Code:

    Windows("A.xlsm").Activate
    Sheets("Unique ID#").Select
    Range("D3:D9000").Select
    Selection.Copy
    Windows("B.xlsm").Activate
    Sheets("TLP").Select
    Range("U52").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    LastRow = Range("U" & Rows.Count).End(xlUp).Row
    Range("W52").Select
    Selection.AutoFill Destination:=Range("W52:W" & LastRow)
    LastRow2 = Range("Q" & Rows.Count).End(xlUp).Row
    Range("V52").Select
    Selection.AutoFill Destination:=Range("V52:V" & LastRow2)
    
    ActiveSheet.Range("$A$51:$W$9000").AutoFilter Field:=23, Criteria1:= _
        "0ADD ID"
    Range("U51").Select

What I want to do from here is select all filtered results that appear in column U and paste them at the end of column C.

After that, I'll filter field 22 by "0DELETE ID". Another thing I'd like to do from here is to select all the rows from that filter and delete them.

These are my first 2 roadblocks, I don't think I'll get anymore if I get past those. Also, an example of what my file and formulas look like can be found attached.

Thanks for any help you can provide, it's greatly appreciated.

Kind Regards,
Younes

macropod · #2 09-26-2012, 10:21 PM

I think you're going to have to post a workbook that actually contains some data, including examples showing what the problem data look like before and after processing. All that's in your present attachment is a bunch of formulae and nothing to relate them to.

Re:

Quote:

In order to update column C without affecting the manual data entered in columns M-P, I've made 3 columns U-V-W

You also need to explain how changing anything in column C could affect columns M-P, since the data in those columns are manual inputs.

YounesB3 · #3 09-28-2012, 05:28 AM

Quote:

Originally Posted by macropod

I think you're going to have to post a workbook that actually contains some data, including examples showing what the problem data look like before and after processing. All that's in your present attachment is a bunch of formulae and nothing to relate them to.

Re:

You also need to explain how changing anything in column C could affect columns M-P, since the data in those columns are manual inputs.

I can't really put any data. All the data that is added in column C is added at the end of it so there's no impact on column M-P.

Also, I've pretty much automated the whole thing already. I'm only at one point that there is something which doesn't work.

Quote:

Originally Posted by tigeravatar

YounesB3,
If those values started V52 and went to V61 and you wanted the row number 58 which is the row "g" is in, you could use the following:

Code:

LastRow3 = Range("V52:V" & Rows.Count).Find("blank").Row - 1
MsgBox LastRow3

I guess it would have worked, but I haven't had the opportunity to try it because my plan didn't work lol...
Let me explain: I apply a filter in column W. I need to copy data from column U to column C (at the end where the first blank cell appears.
This is the code I use so far (i replaced with "?" all characters which could be confidential:

Code:

    ActiveSheet.Range("$A$51:$W$9000").AutoFilter Field:=23, Criteria1:= _
        "0ADD ???"
    Range("W51").Select
    Cells.Find(What:="0add", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, -2).Activate
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("C51").Select
    Cells.Find(What:="blank", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
      :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
      False, SearchFormat:=False).Activate    
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Range("$A$51:$W$412").AutoFilter Field:=23

I highlighted in yellow the thing which needs to be changed..

At first, I tried to find a blank cell, but because of the filter, it didn't work. For instance, if I had lines 167, 289, 384 & 487 showing (because of the filter) and the last filled line was 550, when I did the find "", it didn't go to line 551, but instead changed column and went to the first empty cell.

Then, I added the formula : =if(isblank(A??), "blank", "") from line 550 to something like 1000 or 1250. That didn't work either because when I apply the filter, the last line shows as 1000 (or 1250) instead of 550.

I'm not sure if I'm being clear or not. If you need more explaination, please advise.

Any ideas on how I could do the copying from U to C?

Cross posts:
http://www.excelforum.com/excel-prog...n-problem.html
http://www.mrexcel.com/forum/excel-q...n-problem.html

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
ckBox automation	coconutt	Word VBA	6	09-11-2012 04:22 PM
Document automation	coconutt	Word	1	05-02-2012 01:45 PM
Urgent help regarding automation.	aligahk06	Excel	1	01-14-2010 01:55 PM
What is Application Automation and How can I use it in VBA	KramerJ	Excel	0	03-30-2009 12:59 PM
COM Automation Errors	ivanm	Word	0	03-23-2009 07:02 PM