VBA HELP

[96gtsmoker]

What I need is to take the data from Data_1 page and seperate on the first three pages.
Date IS shows only the date issued. Date PD only shows those with date paid. And Date STP shows only those from
Date stop. Only when that is complete I want to take the data_1 and erase it. Then take Data_2 and match date, check #, and amount with a vlookup. If matches paste the address and ADR from DATA_2 onto the first three sheets that match the three categories and delete the information. Then when I go to add my next check book information in there it pastes the new stuff from DATA_1 below the previous. Can any one help on this. It is to help balance my bills.

 

[Bong]

What have you tried so far?

As a first step, turn on the macro recorder and do a few passes manually. See if you can understand what the recorder produces, and how you might modify it. Ask specific questions.



_________________
Bob Rashkin

 

[96gtsmoker]

I have but my problem is I have the VLOOKUP but how do I tell a macro to copy paste if is correct then delete. I have my import completed and I hae the vlookup, but if the check # is used more than once with different amounts or names it only records the first one which may be wrong. And I want to make it run from vba because the vlookup takes about 5 minutes to run with all the last 3 years of records.

 

[Gavona]

5 minutes to run... Have the vlookup formula in a single cell. Copy it. Paste to the destination cells. Copy, paste as values.
This code applies that principle but only limits calculation to the range containing the formulae. Also make sure that if the lookups are to another file that file is open.

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
With Range("myrange")
    .Cells(-2, 1).Copy  'copy the cell containing the formula
    .Cells.PasteSpecial (xlPasteAll)
    .Calculate
    .Cells.Copy
    .Cells.PasteSpecial (xlPasteValues)
End With
Next c
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

how do I tell a macro to copy paste if is correct then delete

Solve the problem without VBA then look to automate it - using the VBA recorder to start with.
Probably use Autofilter to show just the correct ones then copy the visible cells.

Post a sample of your data and the formulae and code you are using so far.

Gavin

 

[96gtsmoker]

Thanks I will try this and see how it runs. I have not figured out the storage thing so I will post a copy of my code so far. tonight.

 

[96gtsmoker]

It did not work. Any one else with a good way to VLOOKUP with a paste.

 

[96gtsmoker]

I have this VLOOKUP in Row H === =IF(IF(ISERROR(VLOOKUP(A1912,Data_2!$B$1:$L$57570,9,FALSE)),"None",VLOOKUP(A1912,Data_2!$B$1:$L$57570,9,FALSE))=0,"None",IF(ISERROR(VLOOKUP(A1912,Data_2!$B$1:$L$57570,9,FALSE)),"None",VLOOKUP(A1912,Data_2!$B$1:$L$57570,9,FALSE))).

 

[Gavona]

Tell us more. What is not working. What is the error message? Post the code you are using. On what line is the macro failing?

Before you do that let me ask some questions.
1. Can you do this manually, without macros? If not then where are you stuck?
2. Can you give us an example of the Content of Data_1?
3. Are Date Is, Date PD, DAte STP in the same format but just subsets of the records in DAta_1?
4. What do you want the results to look like?







By the way: The formula you posted seems ok. Could be simplified by using OR and a named range.
=IF(OR(ISERROR(VLOOKUP(A1912,myLookup,9,FALSE)),VLOOKUP(A1912,myLookup,9,FALSE)=0),"None",VLOOKUP(A1912,myLookup,9,FALSE))



Gavin

 

[96gtsmoker]

It is a check registry sort of. Data 1 is like this:
Date CK# AMOUNT Name DT PD DT IS DT STP

DATA2 is like this:

DATE CK# AMOUNT NAME ADDRESS

On one tab the I want the date issued the other the DT PD and the date STP, but I nees to add the address from data 2 to data_1. But the check # and Name can be used more than once.So I need to verify Date CK# AMount to put address after Name. I use the Vlookup in Row H but it takes like 5 minutes to update and every move it reloads. So a VBA Code could speed it up.

 

[ettienne]

I would dump the data in Access and run some queries, Excel is not a database. Just my 2c...

 

[96gtsmoker]

Oh I want to use access, but I can not it is not useable in my domain.

 

[ettienne]

What do you mean not usable in your domain?

 

[96gtsmoker]

I use a computer at work and they have it blocked.

 

[Gavona]

You have not given any sample data.
1. For any record will there be data in only one of the three DT fields?
2. For every "Date CK# AMount" in Data 1 is there an entry in Data2. If so then you don't need the iserror and can lose another vlookup. That should help.
3.Is there a good reason for separating the info into different sheets? You can report on it from a single database.

but it takes like 5 minutes to update [red]and every move it reloads[/red]. So a VBA Code could speed it up.

I don't understand what you mean by the red bit.
I posted code that you could adapt that would speed things up. You have not asked any questions about it or posted your attempt - merely said "it did not work"

Your formula involved 4 vlookups - they are always going to be slow. I suggested a formula with only three.

I don't see how your vlookup is doing the job you have specified. It appears to only match the Date (column A).
Create a new column in both Data1 and Data2 containing a concatenation of the three fields. Use that for your vlookup.
In order to concatenate you should be able to just use =A2&B2&C2 but if you are using proper dates then it might be clearer if you use the text function.
Having combined the info copy the cells to themselves as values. Once you have this working without VBA record yourself doing it and examine the code. Try re-working it into the sort of code I posted.
Hint: .formula=

With VBA you could actually speed the vlookup process quite a bit. Do a simple vlookup. Then deal with the #n/a or 0 values (Edit,Replace or Autofilter). Again all packaged within the sort of code I posted.

[With Excel, as far as I know, the only real choices you have are match/vlookup or Query (Data,ImportExternalData). The latter lets you create relationships.]



Gavin