Excel VBA help... little frustrated

[OSUguy98]

Loaded question, so I'll try to give you as much info as I can about my problem....


I've been tinkering around with Excel and VBA to set up the next [H]ordebowl in the DC forum.... This past year, the stats were put in manually, and since you guys helped me figure out how to grab prices from Newegg (and in turn 5 other websites), I figured I could adapt that to grab the stats to make the [H]bowl a little easier to run....

We’re assuming that there will be 64 participants, which means tracking the number of points and number of work units turned in every day for basically the length of the NFL season (modeling this after the NFL…. Just having more teams)…. So 14 items for 64 people, for 4 preseason weeks, 17 regular season, and however many postseason games we have……… which is A LOT of stuff to calculate……

So here's where I run into a little bit of a problem..... I figured out how to grab stats from here... we're grabbing stats from the "Last 8 Days" section.... So I modified a function to grab the points, and another to grab the number of WUs sent it..... Those both work well..... BUT... it's dynamic... This time next week, you won't see any of those scores, and my function will still be trying to grab them..... So, question #1: Is there any way to have a function(not a sub) copy and paste the value?

I figured out a few ways to set up the calculation….

1) Each cell has a formula for the macros I made (Getpoints() and GetWUs())
2) Create an array function and have an array for each participant each week
3) Write a function that would calc the points and WUs for each of the participants and return the values to a range

I’ve run into problems with all 3 of these…. And none of them are really solutions as far as I’ve found……….

The first option is time consuming for one user, let alone 64, calculates everything all the time and is constantly accessing the web/etc…. And, on top of that, it would only work for the first week….

The second option I had the most luck with, but it would only grab one date and I couldn’t step through dates….

The 3rd option I don’t even know how to tinker with…… Is there a way to make a function write to a range in another part of the worksheet??

So I guess what I’m asking is... What’s the best way to do this? Is there some way I’m not thinking of? Or am I going about this completely wrong? Any ideas? Suggestions? Comments?

Let me know if you guys need any more info or clarification


Keep on Folding!! For the [H]orde!!

 

[Hartlove]

I don't have time to get into more detail until tonight, but from what you've said so far, option 3 seems to be the best. It's pretty easy to tell a function where to return a value. Here is some sample code:

 	For counter = 1 To 100
 
 		Set curCell = Worksheets("Sheet1").Cells(counter, 1)
 		curCell.Value = ColA(43)
 
 		Set curCell = Worksheets("Sheet1").Cells(counter, 2)
 		curCell.Value = ColB(56)
 		
 	Next counter

This came from something else I was working on, so ignore the ColA and ColB functions; pretend they are returning values that you need.


The loop is going counting off rows, and ColA and ColB are filling in column A and column B, respectively.

 

[OSUguy98]

Okay... I haven't had any luck with making a function that writes values to a range... I took your code and made a module... added what I thought was needed to make it work, but no dice.....

Function score2(curCell As Range)
    
    Dim counter As Integer
    
    Worksheets("PointTest").Activate

    
    For counter = 1 To 7
    
        Set curCell = Worksheets("PointTest").Cells(counter, 1)
        curCell.Value = 5 'test value
 
        Set curCell = Worksheets("PointTest").Cells(counter, 2)
        curCell.Value = 5 'test value
        
    Next counter

End Function

it errors out if I don't comment out the "curCell.Value = 5".... Am I missing something??


Keep on Folding!! For the [H]orde!!

 

[Hartlove]

Did you want to pass the range into the function? What error are you getting, and how are you calling the function? I tried out the code you posted, and it works fine.

 

[OSUguy98]

I want to be able to type "=score2(whatever range I want)" into a cell and have it fill out the range...... I just keep getting a #VALUE....

does it have to be a sub?


Keep on Folding!! For the [H]orde!!

 

[Hartlove]

I think that's not working, because it's trying to put the whole range inside of a single cell. I think you'd be better off using a macro button or a form button to call the function. (I used a form button when I tested your code the first time, since I had one open at the time)

 

[OSUguy98]

form button? macro button?
(I'm new to this whole VBA thing...)


Keep on Folding!! For the [H]orde!!

 

[Hartlove]

If you would like, I can take a look at your excel file for you. I don't mind putting in some time for the horde. let me know, and i'll PM my e-mail address.

 

[OSUguy98]

sure... I'll take any help I can get....
I appreciate the help so far... hopefully we can get this thing set upso it doesn't autocalc 20k+ formulas at the same time (wouldn't be a problem, except it's getting the info from the web, so it's S L O W)


Keep on Folding!! For the [H]orde!!