Macro excel matching and writing files.

  • Thread starter Deletedmember255403
  • Start date
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.
D

Deletedmember255403

Thread Starter
Hey all,

Im currently working on a macro of rather large size and im a little stuck at the moment.
The endgoal of the macro is to have a clean file out of all the mess and that can be mailed to other pple of my staff. Now here it comes:
In my example there are 3 tabs: Raw file, Database and Endfile. The point of the whole macro is to look up some things between Raw file and Database and then to write it to the Endfile. The Raw file and the Database are allready macromade and all three sheets have a layout that cant be changed.
--> First thing is that i need to check if the Rapprt N° in column L of the Raw file is somewhere in the Database file at column M (CINCUS).
--> IF the number is there I need to check the type wich can be found at column S (CINSTA)
--> IF THEN that type is 7 then it has to be written away to Endfile, what has to be written away is colored blue in my example.
--> IF THEN that type is 8 it has to be written away to the sheet "Type 8" for further investigation, the data is the same (blue colored)
--> IF the number is not found it has to be written away to the sheet "Not Found" with also the same data.

I think thats it, its much i know. But im stuck in finding all the formulas and stuff. So some help is greatly appreciated. Thx in advance.
 

Attachments

Joined
Jul 1, 2005
Messages
8,546
First things first (IMO), you'll be analysing each value in Raw_File!L:L. For this you could use the MATCH function, but the thing to be careful of is that it'll crap out if no match (bearing in mind you're looking for exact matches).

So try the code below. Run it first with Raw_File!L2 selected -- it should return 2, the row number of column M on Database! where 01517587 is located. Then run it with L11 selected -- it should return "No match.", because 01533457 doesn't exist in Database!M:M.

That's all I have time for now, but it should give you a start point.

rgds,
bomb

Thanks as ever to Dave Peterson MVP for the "assign MATCH result to a variable to trap no match error" method.

Sub test()
Res = Application.Match(ActiveCell, Sheets("Database").Columns(13), 0)
If IsError(Res) Then
MsgBox "No match."
Else
MsgBox Res
End If
End Sub
 
Joined
Jul 1, 2005
Messages
8,546
OK, here's an update. I can't figure out what exactly should be "written away" & where to, so all it does is display a prompt for which action should occur. Therefore the code lines starting MsgBox are the ones you need to modify.

Oh yeah, IMPORTANT NOTE: the sheet name for Endfile is not spelt correctly. ;)

hth,
bomb

Sub Main()

If ActiveSheet.Name <> "Raw file" Then Exit Sub

For Each Cell In Range("L2").Resize(WorksheetFunction.CountA(Columns(12)) - 1, 1)

Res = Application.Match(Cell, Sheets("Database").Columns(13), 0)

If IsError(Res) Then
MsgBox "Write to 'Not found.'"
ElseIf Sheets("Database").Cells(Res, 19) = 7 Then
MsgBox "Write to 'Endfile.'"
ElseIf Sheets("Database").Cells(Res, 19) = 8 Then
MsgBox "Write to 'Type 8.'"
End If

Next Cell

End Sub
 
D

Deletedmember255403

Thread Starter
Hey bomb,

Funny to see that cause i was just typing a post wich contained almost the same :)
Glad to see we were thinking in the same line.

Now im trying to add a counter for the data that needs to be written to Not Found and Type 8. Here the data colored in blue (found on the Database sheet) just needs to be written away starting from A1 and just going down when new data needs to be added. Same with the Type 8 sheet. The whole row could also be copyd but then i have data i dont need in the sheets (but copy-pasting the whole row is maybe easier to test it)

Then the End File sheet is a little more difficult. There the data colored in blue (found in database sheet) must be put in the according cells on the End file sheet (dont mind the dates there, not important) and just go one row down when new data needs to be entered.

Grtz,
 
D

Deletedmember255403

Thread Starter
Little update: I added some very simple code concerning the pasting of the cells. But I cant seem to grab the cells i need from the Raw file. The code in green above your sub is what i had before and then it could copy the cells but when i run the code from you with the few lines i added it runs completly without errors but nothing is pasted, when i select my cell L2 before running the macro it only copy - pastes that cell and nothing else and it pastes it in all sheets except databse.

Any idea's? I added the example in attachment.
 

Attachments

Joined
Jul 1, 2005
Messages
8,546
Code that is very simple to you is not to me. ;) Let's try another tack. :)

Re: "copying" -- just as there's no need to select a range, then copy, then select the target range, then paste ... there's no need to actually copy -- you can simply use if a = b then x = y.

Take your original data. The only Rapprt N° that should go to Not Found is 01533457, because it's not in Database!M:M (correct?). So where I had:

MsgBox "Write to 'Not found.'"

an alternative might be:

Sheets("Not Found").Range("A65536").End(xlUp).Offset(1, 0) = Cell.Value

Now as long as you have a label in Not Found!A1 (e.g. "Rapprt N°") then the code will set the value of Not Found!A2 to 01533457 rather than actually copy it (once it gets to 01533457 in the loop.

Do you follow? :)
 
Joined
Jul 1, 2005
Messages
8,546
OK, I'm still stuck with what should map to where, but try this.

EndFile has 8 labels, B3:I3. Say you wanted to map some values from row 2 on Database to Endfile. The line:

Res = Application.Match(Cell, Sheets("Database").Columns(13), 0)

gives you the row number on Database to map from.

So to map Database!BN1 ("VINCENT MOSTERT") to Endfile!D:D it's:

Sheets("Endfile").Range("D65536").End(xlUp).Offset(1, 0) = Sheets("Database").Cells(Res,66).Value

, 66 being the column # for BN (NB: not tested). So you need one line like that for each value you need to map. Any clearer?
 
D

Deletedmember255403

Thread Starter
Hey bomb,

I followed all the way :p
Most of it worked and I allready adepted the code to fit for Type 8 sheet too. But the only thing not working is when the number is not found, he then debugs on out of range on the line:
Sheets("Not Found").Range("A65536").End(xlUp).Offset(1, 0) = Cell.Value

I added an example how it is now, should be easier for you to see where goes what when you run the macro.

Edit: I was looking for over 2 hours what the hell could be wrong with the code and then suddenly i found it. A stupid space. Thats something that gets you on your nerves. :D
So bomb, just adept the line so that its Sheets("NotFound")
Normally its done now but i have to merge the parts now so ill leave this open if I encounter any new errors.

Thx allready alot. :D
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top