VBA/Macro Match

[Moitel]

Hi all. I'm writing a macro for a spreadsheet for billing my customers' calls. As I've found out today procedures can only be 64kb.. I'm writing my '=IFS' statement but it's taking forever, here's a part of it:

Please Login or Register  to view this content.

That's just 15 of the area codes.. there are 477 in total

What I'd like to do in H is say, for example is: 'look at column J, if it matches anything in column O, take the value in the cell to the right of the matched cell in O and multiply it by the appropriate row in F'. I'm hoping that's possible instead of having to type out everything. Here's a pic of what a sheet looks like (With sensitive bits removed):
Billing.PNG

The original code, before I started tweaking it for this large customer (I've typed everything out for one of our smaller customers and it works great):

Please Login or Register  to view this content.

Hope this makes sense, I've managed to stumble my way through it so far but am now a bit stuck! I'll happily buy anyone who can help a beer!

[CK76]

Can you upload sample workbook that represent your data set? With sensitive data sanitized.

[Moitel]

https://app.box.com/s/b9sau8s3ci2wf47r0wd5n335k46aej82 That should be the workbook with all sensitive data removed.

It's the code for the small customer but the principle of needing to check cells in a column against another is the same. Cheers.

[Arkadi]

In H3:

Please Login or Register  to view this content.

then copy down?

or if you only want 3 decimals:

Please Login or Register  to view this content.

[CK76]

If you want to do it in VBA and only put values into the range.

Please Login or Register  to view this content.

[Moitel]

Thanks guys. I'll give it a go next week and report back.

[Moitel]

Arkadi, I tried the lookup but it threw an error back at me, I'm sure I'm just putting it in the wrong section.

CK76, when I added that sub it filled H with the same value all the way down. H needs to change depending on the code matched in J and the value of F. The code needs to match the value of J to O, which takes the value of the corresponding P cell and then multiplies it by the correct F value for the correct row.

Hope that makes sense.

[CK76]

Hmm? It worked fine on my end.

See sample attached.

[Moitel]

Oh right, thanks. Where in my code should i insert the new sub? Below is the code (I would attach but the button doesn't seem to work for me).

It does a bunch of sorting and inserting reference cells etc.

Please Login or Register  to view this content.

[CK76]

Just stick at the bottom or in separate module. It really does not matter.

[Moitel]

Still can't get it to work, sorry.

Here's what I have:

https://app.box.com/s/4o7th3f8yy3dg5py62omirlaggp8d3ht

In case the Macro isn't on the sheet then this is what it looks like:

https://app.box.com/s/r9sumdl61t1xrklipm5f247mb45rmr5x

The Call function just has CK76's code in it with the sub name changed to Calc444, no other changes. Not sure what I'm missing..

[CK76]

Which sheet is active when you run the code?

[Moitel]

Sheet 15, from there it should take the columns I need, paste them into a new sheet (16), insert the reference cells in columns O & P, do a few bits with decimals etc. then carry out the calculation in H, which was previously an =IFS statement, looked like this:

Please Login or Register  to view this content.

Copy that statement for 500 or so rows in H, this tells us how much we need to bill the customer for the call in that row. The macro then copies the columns I need and pastes them into the final new sheet (17) and tidies it up a bit. We then export that into a word document.

[CK76]

Ok, give me bit of time to rewrite your code. It's bit inefficient and and bloats file size.

Your file in itself should not be any more than couple of hundred Kb, but due to unnecessary format etc, bloating to 9mb in size.

[Moitel]

Thanks CK. I don't know how to write in VB really, so I originally recorded the Macro by using the record action and actually typing the stuff out/carrying out the actions, then later edited it to tweak it.

Let me know if you have one of those 'buy me a beer' links!

[CK76]

Found the culprit that replicated same number in column. Your Proc3 had following.

Please Login or Register  to view this content.

This would overwrite the values in range with value in "H3".

By the way, why do you use code to write values to O:P range? It's far better to maintain the list in separate sheet and read from there.

I'll have something for you in few hours (I'm headed into meeting now).

[CK76]

Here you go. Only part I wasn't sure about was your last sort operation.

Which column(s) should it be sorted on?

Notes:
1. Raw is the original sheet
2. List is the list that contains list of Charge Code and corresponding cost (if you need to add more, add to bottom of the list, code will auto adjust)
3. Output is where your intermediate result goes. Then add another sheet for your final out put.
4. Ranges are all dynamic now to avoid formatting entire column range unnecessarily.

[Moitel]

Wow, that's amazing. Thank you CK! That's leagues better than what I was able to do. Many thanks, I've added rep.

[CK76]

You are welcome and thanks for the rep