Ok, so show me the code that takes over immediately. I assume Excel
opens and reads from Access?
Since you have to do it by code, all possible. Slightly swamped right
now, but if you send the code, I can look tomorrow, else someone else
will chip in.
Basically it will look as follows:
Dim i as integer
Dim RowEnd as integer
Dim MasterWB as string
Dim CompareWB as string
'Get workbook names for master workbook and comparing workbook
'MasterWB = (insert code here)
'Switch workbooks
'CompareWB = (insert code here)
'Switch wbs again so master is active
'Determine rows in MasterWB
'Determine rows in CompareWB
'Select Column A, for correct rows
'Create named range of all data to compare with as DataRange
'Activate MasterWB
'Determine number of rows in MasterWB as RowEnd
For i = RowEnd to 1 step -1
If
application.worksheetfunctions.vlookup(Range("A"&i).value,DataRange,Fals
e,1) = ... Then
...
Else
...
End If
Next i
Sorry, I have run out of time for today.
Regards
Paul
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On
Behalf Of SKR
Sent: Monday, 26 September 2011 3:26 PM
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] Re: Comparing Data across Workbooks !
Paul, the problem is that the data comes from Ms Access directly, and
another excel macro takes it over immediately, so i want to chip in
something in between, so not much scope for manual activities. So I am
in a fix.
--- In ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com> ,
"Paul Vermeulen" <paul.vermeulen@...> wrote:
>
> SKR, start by pre-processing data in Excel before doing macros for
this one. In your first workbook, use a VLookup function in column B to
test whether column A value exists in second workbook. An If statement
around the lookup can give you a zero/one result.
>
> You can then filter for only non-matching values, and delete rows.
>
> Let us know how you get on.
>
> Paul V
>
> Sent from my iPhone
>
> On 25/09/2011, at 6:45, "SKR" <svkroy@...> wrote:
>
> > I understand that I have been too short in describing the
requirement. Sorry for that. I have two workbooks, X & Y, both have
comparable data in Sheet 1 column A, for each cell in Workbook X,Sheet
1, Col A, if cell value (integer) is not matching with Workbook Y,Sheet
1, Col A (for any of the cells in Column A), then delete the row
containing that data in Workbook X only.
> >
> > --- In ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
, "David Grugeon" <yahoo@> wrote:
> > >
> > > We need some more information about what you are trying to
achieve.
> > >
> > > To simplify my explanation of what we want I shall refer to the
two sheets
> > > as X and Y
> > >
> > > Do you want to test each cell in col A of X against each cell of A
in Y or
> > > just against the cell in the same row in Y?
> > >
> > > Do you want to delete the complete row in X or in Y or in Both?
> > >
> > > There could be other questions depending on your reply but that
will do for
> > > now.
> > >
> > > Best Regards
> > > David Grugeon
> > > Excel VBA Group Moderator
> > >
> > >
> > > -----Original Message-----
> > > From: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
[mailto:ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com> ]
On Behalf
> > > Of SKR
> > > Sent: Friday, 23 September 2011 9:27 PM
> > > To: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
> > > Subject: [ExcelVBA] Comparing Data across Workbooks !
> > >
> > > Im trying to compare integers in Workbook1,sheet1, Column A data
with
> > > Workbook2,Sheet1,Column A Data.If not matching then delete
row.Please help.
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > ----------------------------------
> > > Be sure to check out TechTrax Ezine for many, free Excel VBA
articles! Go
> > > here: http://www.mousetrax.com/techtrax to enter the ezine, then
search the
> > > ARCHIVES for EXCEL VBA.
> > >
> > > ----------------------------------
> > > Visit our ExcelVBA group home page for more info and support
files:
> > > http://groups.yahoo.com/group/ExcelVBA
> > >
> > > ----------------------------------
> > > More free tutorials and resources available at:
> > > http://www.mousetrax.com
> > >
> > > ----------------------------------Yahoo! Groups Links
> > >
> >
> >
>
>
> [Non-text portions of this message have been removed]
>
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar