PDA

View Full Version : Solved: Help, trying to use data from 1 sheet, to find total Cost on another sheet.



cjyogz
08-16-2006, 09:27 PM
Hello everyone. Here is my situation.

I am writing a bit of code to summarize the total cost of a certain product for the total Month. There is a sheet called Summary which has all the products on it, and a sheet called Income, which has the total Income recieved for each item.

What i want to do, is for eg, on Screen1(link below to screen shot) there is a list of products. Lets start with Ball Money. I want the program to collect the value of Ball Money, it needs to store the name as a text, and the cell location.

It will then go to the Income sheet (Screen2(link below)) where it will search column C3 and downwards for the matching word of Ball Money. When it finds the word Ball Money, it will then go to the corresponding cell in Column E, where it will add the value in that Cell to a variable Cost(or whicever you choose). After adding this value to the variable Cost, it will then return to column C, where it will continue to search down Column C for anymore matching cells matching Ball Money, where it will again, add the value to the variable Cost.

After it has gone down the the list and gets to an empty cell, (ActiveCell.Value = "") it will then return to the Summary Sheet (Screen1) and move down one cell (ActiveCell.Offset (1, 0).Select) where it will store the value in this cell over the top of Ball Money, so in this case Bar Sales, it will then do the same process as what was done with Ball Money.

If you can just do this for the Income part of the Summary Sheet(Screen1) that would be great, i will be able to modify the code to go to the expense sheet.

Thank you very much for reading this, and i hope you can help me with this situation. Ask any questions and i will answer asap.

Chris J

http://www.rapidupload.com/d.php?file-dl&filepath=15813
Screen 1 ^
http://www.rapidupload.com/d.php?file=dl&filepath=15815
Screeb 2 ^

mdmackillop
08-17-2006, 12:39 AM
Hi Chris,
You can post your files here using Manage Attachments in the Go Advanced section
Regards
MD

cjyogz
08-17-2006, 01:35 AM
ok, here's a snippet of my book. Since posting this question i have written some code to try and solve the issue myself.

In Module 2 in the 2nd/Bottom Sub you will see what code i have written. At the moment this sort of works, but continously loops.

If you want MD or anyone else, you can go from what i have already written. Its totally upto you.

Chris J

mdmackillop
08-17-2006, 11:42 AM
I would go for a formula solution here
=SUMIF('May Income 2006'!C:C,'May Summary 2006'!A4,'May Income 2006'!E:E) in Summary sheet C4
but I'll have a look at your code.

Edit: There's no code in your workbook and I can't see your screenshots either.

cjyogz
08-17-2006, 05:32 PM
Oh sorry, try these
http://www.rapidupload.com/dl.php?id=15813
http://www.rapidupload.com/dl.php?id=15815

and i inserted the modules into the Snippet file. The updated one is below.

Thanks MD, i'll have a go at that code u gave me and see if it works, but i'd rather it in vb code if i can, thanks.

mdmackillop
08-17-2006, 11:44 PM
Hi Chris,
I see you're getting the hang of VBA, but I would not tackle this with your approach.
The way to get the data into the cells most efficiently is a Vlookup formula. This doesn't change with a VBA solution. Use the code to write the formulae into the cells, then remove the formulae and keep the values. I gave a solution here, http://www.vbaexpress.com/forum/showthread.php?t=9186 working with 5000 lines of formulae. It's 7 to 10 times faster than the looping process. You shoul;d be able to adapt this code to your own situation, but any problems, please let us know.
Regards
MD

cjyogz
08-17-2006, 11:50 PM
Hey thanks for the help mdmackillop. I decided to go with your code that you gave me before:

=SUMIF('May Income 2006'!C:C,'May Summary 2006'!A4,'May Income 2006'!E:E) in Summary sheet C4

what can i say, it done the job exactly as i wanted it. I will just protect the cells which the formula is in so that the user doesn't change it.

Thanks again for the hlep md.

mdmackillop
08-17-2006, 11:52 PM
If the data is fixed, then Copy/PasteSpecial values to get rid of the formulae.
HTH

cjyogz
08-18-2006, 12:00 AM
thats a possability but the data could be changed later on, perhaps because of an error.

mdmackillop
08-18-2006, 12:09 AM
OK, But I would still write the formulae using VBA. More efficient in the long run, and less prone to errors.

cjyogz
08-20-2006, 08:20 PM
Hey md, i tried readjusting that code you provided me in the other topic.

But i don't understand it well enough to modify it. I'm totally lost, are you able to assist me some more.

The SUMIF code does work, but i can't insert it into the new sheet through VBA, it keeps coming up with an Update screen and i can't fix it.

So i'm going to attempt this other code you have provided in the other topic.

mdmackillop
08-21-2006, 12:18 AM
Some formulae are a bit tricky to get right in VBA, so where you can, you let the PC do the work.
Assuming you have the correct formula in cell B4, do the following.
1. Edit B4 by deleting the = sign; this will show the formula as text
2. Start the macro recorder; add back the = sign and stop the recorder.
3. You should now have

Sub Macro2()
ActiveCell.FormulaR1C1 = _
"=SUMIF('May Income 2006'!C[1],'May Summary 2006'!RC[-1],'May Income 2006'!C[3])"
Range("B5").Select

End Sub
4. Change the Range from ActiveCell to Range("B4:B11").FormulaR1C1 = ...

cjyogz
08-21-2006, 12:59 AM
thnx md, that works. but there's one problem that i can't figure out how to solve yet. The project runs by years and months. So for now it will 2006, then it'll change to 2007, 2008 etc. I need to be able to change the years through vba or through a variable such as Dim Year, Year = Sheets("Data").Range("E5") or something like that perhaps.:

=SUMIF('May Income 2006'!(C[1],'May SUmmary 2006'!RC[-1],'May Income 2006'!C[3])

Any ideas.

I can do an If statement for the months in the code, that will easy enough, its just the years that i can't figure out.

mdmackillop
08-21-2006, 01:49 AM
Look up Month and Year and Now in the VBA Help. Don't worry, we've got 4 months to figure this out!

cjyogz
08-21-2006, 05:32 AM
haha well yeah i guess we do have 4 months, but i just officially took over the treaury job tonight, after a few beers that is. i'll see if i can get what you described just then to work, cheers.