[dcat2]

I have a list of numbers that i want inserted into very widely (and not patterned) spaced cells. To clarify: I have a list of 45 different numbers ranging from 90-130 that are in a specific order. Lets say all of these numbers are associated with the Miami Heat on the road. On my main sheet I have a list of 13,000 games where teams are listed in one column and location is listed in other (so for the purposes of an 'if...and...then' statement you would need A="Miami" and B="Away"). I want a loop/script/code that would run through the document and insert these numbers into the spaces in the order that Miami road games occur. Is this possible and if so how difficult would it be, and how could it be done? As always thank you for your time.

[MonkeyF0cker]

Learn VBA.

[dcat2]

okay thanks, just spent a few hours working through a bunch of tutorials

[dcat2]

alright I guess here's my revised question:

I have this code

Sub HeatAway()
For x = 1 To 43
If Cells(x, 1) = "Heat" And Cells(x, 2) = "Away" Then
Cells(x, 4).Value = Cells(1, 7)

End If
Next x

End Sub

This code identifies when the away team is the Heat and then pastes a specific number from cell (1,7) into the 4th column (in the same row that Heat appears in column 1 and away appears in column 2). How can I tweak this script to begin running down the values of the 7th column and have them subsequently inserted into each Heat away game as they occur (i.e. the first value goes into the first heat road game and the second value goes into the second Heat road game etc.)

[thom321]

I want to reiterate what Monkey said. If you like to do your on customized analysis in Excel (which I definitely encourage), you are better off learning VBA now rather than later. When dealing with datasets as large as 13,000 rows, speed becomes an issue unless you optimize your code a bit. In any Sub you create, I suggest including Application.ScreenUpdating = False and Application.Calculation = xlManual at the beginning of the code (after you have declared any variables you might use). At the end, you don't have to turn screen updating back on, it happens automatically. Assuming you do want Excels calculation setting to be automatic you have to include Application.Calculation = xlAutomatic at the end of your code. If you for whatever reason need Excel formulas to recalculate while your code is running, just include the command Calculate in your code to make Excel recalculate all formulas.

Also make sure you declare all your variables or else you will eventually run into trouble. It is a good policy to have Excel require all variable to be declared. You would do this by going to the editor, select Tools, Options and check the box that says "Require Variable Declaration". Now the words "Option Explicit" will be inserted at the top of each new module you create, which in turn means that in order to use a variable, you must first declare it using
a Dim statements. Delaring a variable named "x" would look like:

Dim x

It is also a good idea to specify which type of data the variable will hold but I am not going to go into details about that.

The other thing that will make a huge difference when it comes to the speed of your code is using arrays. Rather than reading the value from each cell one at a time and writing it back to the sheet one cell at a time, you should read all the data into one or more arrays, do your calculations and store the result(s) in one or more arrays and at the end of the code write the entire array(s) with your results back to the sheet. It would work something like this:

'Declare your arrays. You can call them whatever you want. You can also specify what data type they are made up of but I am skipping that part.
Dim aColumnA() 'array that will hold all the values in column A
Dim aColumnB() 'Array that will hold all the values in column B
Dim aResult() 'Array that will hold the result of your calculations

'Read your data to the arrays

aColumnA = Range("A1:A10000").Value 'Will fill the aColumnA array with the values from the specified range
aColumnB = Range("B1:B10000").Value 'Will fill the aColumnB array with the values from the specified range

Each array will now be a two dimensional array with 10,000 "rows" of data and 1 column.

You would access each data point the same way you do with Cells(x,y) where x is the row and y is the column

So if you wanted to loop through your arrays and do calculations on them the code would look somthing like this:

Sub Whatever()

Dim aColumnA() 'array that will hold all the values in column A
Dim aColumnB() 'Array that will hold all the values in column B
Dim aResult() 'Array that will hold the result of your calculations
Dim i 'Counter for loop

Dim ResultValue 'the value you want to store as the result if condition is met

Dim ColA_Value
Dim ColB_Value

Application.ScreenUpdating = False
Application.Calculation = xlManual



'Read data to arrays

aColumnA = Range("A1:A10000").Value 'Will fill the aColumnA array with the values from the specified range
aColumnB = Range("B1:B10000").Value 'Will fill the aColumnB array with the values from the specified range

'Get the value to store in the result array
ResultValue = Cells(1,7).Value


ReDim aResult(1 to Ubound(aColumnA),1 to 1) 'resizes array aResult to have the same number of rows as array aColumnA and one column.

'Do calculations and store results in array

For i = 1 To Ubound(aColumnA)
'Ubound(aColumnA) is the number of "rows" in array aColumnA. You can set the loop to repeat for whatever number you wan but you will 'get an error if you try to access an array row that is greater than the number of rows in the array

'You don't have to do this step of storing the value you retrieve from the array into a variable before doing calculations. However I find
'it makes the code easier to read.
ColA_Value = aColumnA(i,1)
ColB_Value = aColumnB(i,1)

If And(ColA_Value = "Heat", ColB_Value = "Away") Then

aResult(i,1) = ResultValue 'store the result value in array if condition is met

End If



Next i


'Assuming you would like your results to be in column C, in rows 1 to 10,000, you would write the values back to the sheet like

Range("C1:C10000").Value = aResult

'Reset calculation
Application.Calculation = xlAutomatic 'Setting it to xlSemiAutomatic would mean that everything except Excel tables would be automatically recalculated

End Sub

Obviously this does not produce the output you would like but should give you an idea of how it can be done. I didn't test this code so there might be typos or text that should be commented out but isn't.

[sharpcat]

Thanks for taking the time to share that thom321. The time you spent on that post will not go unappreciated.

Nice to see someone share something in the think tank that actually requires a little thinking and not the usual crap you find here lately of lazy people begging for valuable information.

[thom321]

Thanks sharpcat. I am glad to be able to help. When it comes to programming I am 100% self taught and had to rely on the kindness of others (Google mostly) to explain to me how things could/should/shouldn't be done. I still do as a matter of fact since there is a lot to writing code that I don't have a clue about. I'm trying to pay it back by sharing what I know.

When it comes to finding out a solution as it relates to Excel and VBA I find that a Google search using "Excel" "VBA" and then whatever the specific issue is e.g. reading csv file usually leads to a workable solution. To clarify, the full search term in the previous example would be:
"Excel" "VBA" reading csv file

[TomG]

While some may disagree with me, I believe if you have to learn VBA to work with Excel then it's probably time to move away from using Excel for what you're trying to do.

I don't use VBA and I do a lot with Excel. Through clever use of Excel formulas I can do just about everything I need. Limitations that I run into are usually file/table size limitations and not functionality.

OP, it's not entirely clear what you're trying to do, but I'm almost certain you can accomplish it through clever usages of VLOOKUP, INDEX, MATCH, and ISERR functions.

[thom321]

Unlike TomG, I use VBA a lot and so far I have found that as long as I am dealing with data sets of up to a few 100,000 data points, Excel with VBA can do whatever analysis I want, keeping in mind that we are talking about up to college level statistics, nothing more complicated than that.

However, my first approach is ALWAYS to use formulas where it is possible, since it is more flexible and gets you to a working solution faster than writing VBA. While it is fine to have a number of cells with formulas that look for data in a range of 13,000 cells, I would be hesitant to have a complicated formula repeated 13,000 times (once for every row) since Excel quickly becomes slow to recalculate in such as situation.

In addition to the excellent formula suggestions made by TomG, I would add OFFSET as a very useful formula, particularly in conjunction with INDEX and MATCH.

[bztips]

In addition to the above-mentioned functions, another incredibly useful one is sumproduct(), which can be used to extract and tabulate selected records from a database (akin to an SQL query with a WHERE clause). Good description of how to do this is here.

[tukkk]

i agree with TomG, combining different functions can get you just about everything.

[MonkeyF0cker]

Learning VBA is not difficult. Loading your spreadsheet full of convoluted functions might work, but VBA makes those tasks much more concise and easier to modify.

I do agree that trending away from Excel altogether is a good idea - eventually. However, once you know VBA, you can pickup another language MUCH easier. And when you're ready to move away from Excel, most of your work can be ported over rather than essentially starting over from scratch.

[dcat2]

Thank you such more for the thom321 and bztips, i've spent the past week working through VBA and I've started to develop a pretty strong feeling for the language. I would ask monkeyf0cker one question however, which is what medium would you suggest moving to once you leave excel?

[MonkeyF0cker]

You can transition almost seemlessly to VB and the .NET framework from VBA. VB.NET will give you every bit of functionality you would ever need for sports betting purposes.

I prefer to work with Visual C# which is easy to pickup once you've become accustomed to the .NET framework. But at that point, it's an issue of personal preference rather than necessity.

[MonkeyF0cker]

Microsoft offers Express (free) versions of the Visual Studio suite (VB, C#, C++) which should be adequate for you (at least in the beginning).

http://www.microsoft.com/visualstudi...itions/express

One thing that is missing in the Express versions versus the commerical versions of Visual Studio which can come in handy is the XML editor. However, you can use the XSD utility in the .NET framework SDK to create XML schema and classes (so you can use intellisense to traverse and parse nodes in the XML tree).