PDA

View Full Version : Sleeper: INDEX MATCH with a twist , not sure if possible in VBA



mercmannick
07-19-2006, 11:32 AM
Hi enclosed is sheets , what i am trying to do is below.....

(Not sure if this is possible) :think:

MATCH COL D OF THIS SHEET, WITH COL G OF SHEET 2,.
IF SAME ROW HAS BLANK IN COL I AND A "REL" IN COL J IN SHEET 2, HIGHLIGHT ON HERE
BY COLOUR AND BOLD TEXT, AND GIVE USER OPORTUNITY TO DELETE ALL BUT THE MATCHED ROWS ON THIS SHEET…

ALSO IS THERE A WAY OF IF THE SHEET NAMES WERE SETUP LIKE THIS ("M50" AND "M55" AND "M74")

TO MAKE THE CODE RUN ON EACH SHEET APART FROM "KTAB" SHEET

Many Thanks

Merc

mdmackillop
07-19-2006, 11:44 AM
(Not sure if this is possible) :think:
The possible we do today, the impossible takes a little longer....

mercmannick
07-19-2006, 11:51 AM
Sub Test_For_REL()
Dim r As Long
Dim vSheets() As Variant
Dim i As Integer

vSheets = Array("M50", "M55", "M55") ' add sheet names to match
For i = LBound(vSheets) To UBound(vSheets) 'switch sheets automatically
Sheets(vSheets(i)).Activate
r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A





Next i

End Sub

HeHe so true mdmackillop

This is as far as i got at moment not sure how to even start on remaining codes

Merc

geekgirlau
07-20-2006, 01:45 AM
I haven't time to look at the code immediately, but I've added some formula columns for you to look at. One approach might be to have your code insert the formulas, filter out the rows that don't match, and delete them. Then repeat for each sheet.

mercmannick
07-20-2006, 09:23 AM
geekgirlau (http://www.vbaexpress.com/forum/member.php?u=450)

thank you , unfortunately if i use formulas then when new sheet is pasted into sheet, it will overwrite the formulas, but maybe can Code to make formulas after pasted new sheet in

Merc

geekgirlau
07-26-2006, 03:11 AM
Yes, that's the general idea - have your code insert the formulas, then filter