Inserting a for loop into Excel using VBA
I need to perform a series of calcuations a number of times depending on the value in cell A1. I think a for loop will suffice but I'm new to VBA and I'm having trouble understanding how to 'put the VBA loop' into a cell, how to reference other cells from within (I would use the OFFSET function in excel) and how to display the results in the cell.
This is what I have so far.
Dim i, iArray, Total As Integer
For i = 1 To Range("A1")
Total = Total + iArray(i)
' Cells(i, 1).Value = iFib
Next
The end result should be that if I enter this bit of VBA into a cell in my spreadsheet, it will look at the value in cell $A$1, and iteratively add (1/i * the value in the cell up one row and 'i' cells to the right.
I worked with C and Unix many years ago so I have a basic understanding of code and Excel but I need to get this done ASAP and obviously there are huge if not gaping black holes in my knowledge of VBA.
Help greatly appreciated. Karma will be kind to you!
More Info needed
I will gladly help you with your code, but I think I need a little bit more info to help you out.
What I will need for a loop like this is:
1. The row you would like to start evaluating cells in (which it looks like row 1 from what you have provided).
2. The last row you would like to start evaluating cells. This can be a specific number row, or the last row in a particular data set (which changes over time). For now lets stick with just the last row you have (a specific number).
3. What exactly you would like evaluated for each cell. I'm a little unclear of what you are looking for here, so maybe an example with a screenshot can help!
For the Loop you are trying to run, here is the typical format:
Dim i as integer 'Start number
For i = 1 to (EndValue)
'Code goes here
Next i
So If I wanted to take the value of A1, add the value of B2 and divide it by i (Which increases by 1 every instance the loop is ran), then place the result in cell C1 I would use this code:
I hope this helps you to understand the functionality behind this. Send me over the specifics on what you are trying to accomplish and I will try and provide code to assit you.
Sincerely,
-Max
Basics
Hello VeryLuck,
Unfortunately with VBA, you cannot enter the contents into the cell and have it execute your code, it requires a little bit of extra work to get the code to actually execute.I'll try and quickly go over the basics for you and then we can address what kind of coding you need!
First step is to activate the developer tab on your ribbon within excel. To do this follow these steps:
1. File -> Options -> Customize Ribbon
2. on the right side of the screen, check the Developer box
3. Click ok.
Now you should have the developer tab on your ribbon and you can start working with Macros and VBA.
Now once this is done you will need to create a subroutine to execute your code. So in the developer tab, click the Visual Basic button (first button on the ribbon). This will bring up a seperate window with the VBA interface. In this window on the left sideof the screen, right click "ThisWorkbook" -> Insert -> Module. You should now have Module 1, double click on it and it will bring up a blank note pad looking space which will allow you to insert your code:
Now, anytime you want to create a macro to run some code, you should start by writing "sub" then the name you would like to call your Macro (This is the case for most actions in VBA, there are more advanced titles for actions within VBA, but as a beginner you should stick with these). Once you do this, the words "end sub" will autmoatically populate under what you just wrote. Now inbetween these two text, you can write your code:
Once your code is in place, you can press F5 within the VBA interface in order to run the code, or you can go to the developer tab within excel, click on "Macros" and then run your code there.
Thats a quick crash corse in VBA. I'll jump into your specific code in a seperate post.