#1
|
|||
|
|||
File automation problem (VBA)
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 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 |
#2
|
||||
|
||||
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:
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Quote:
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:
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 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 |
|
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 |