File automation problem (VBA)

macropod · #1 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 · #2 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

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