| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA: For Count, when count changes from cell to cell
Hello,
I am trying to write a "simple" code to sum one value in a column containing values of (Q3) and add it to another which is the calculated value that I'm after (I3), in the column adjacent.. How many values of Q3 that I sum depends on what another value, SP/2 is in the last column. A code I found in easy language looks like this: For count= 0 to Int(SP/2) -1 begin I3=I3 + Q3(Count) My Excel 2000 spreadsheet looks like this: Col: AF AG AH (Q3) (I3) (SP/2) .03 .02 .05 .04 .06 X 4 .07 X1 3 . . . . . . .01 Xn 6 I want to be able to calculate I3 for x, x1 and...... xn How do I do this in VBA? Could you please also show me how you would declare the variables as well. I am just starting out in this, and if you could point me in the right direction I'd appreciate it. Thanks LS |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA: For Count, when count changes from cell to cell
I'll try to help some, but I must admit to being confused by how you want the
value in AH to be used to figure out which values of Q3 (In col AF) to add together to get the value for I3 in column AG. So the code may not be doing that properly. First the code, then some explanation. Sub CalculateI3() Dim I3value As Single Dim LastRowOfData as Long Dim LC as Integer ' Loop Counter 'find last row with data in column AH LastRowOfData = Range("AH" & Rows.Count).End(xlUp).Row 'go to first possible data entry in AH 'assumes row 1 has header text Range("AH2").Select 'work down thru all cells to last row used Do While ActiveCell.Row <= LastRowOfData 'assumes if cell in AH is not empty, it is number If Not(IsEmpty(ActiveCell)) Then 'I'm a little confused here, so may not be right For LC = 0 to Int(ActiveCell.Value/2)-1 ActiveCell.Offset(0,-1) = _ ActiveCell.Offset(0, -1) + _ ActiveCell.Offset(-LC,-2) Next ' end of LC loop End If ' test for empty cells 'move to next Row ActiveCell.Offset(1, 0).Activate Loop ' down thru rows End Sub The 'Dim' statements declare a variable for use later, simply reserving room for it to be used. If your code module includes the declaration Option Explicit at the beginning of it, then Dim or Const declarations are required. I recommend it because of several reasons that I won't go into here (end result: better code less prone to failure). Because we are going to be doing the math based on the contents of cells, we don't need variables to hold interim results. But we do need to know how far down the sheet to work at getting information and we need to know how many values of Q3 to add together to get the I3 values. LastRowOfData is set up as type Long so it can hold very large integer numbers, since you may have thousands of rows of data to work through. LC, to be used as a loop counter, is set up as Integer to hold smaller integer numbers, although to be on the safe side it possibly should be set up as type Long also. The first executable line of code looks up from the bottom of column AH until it finds some entry in that column. That tells us how far down the sheet we have to work in examining the information to be used. We save that row number for reference in LastRowOfData. We get into a Do While loop that is simply going to work down the SP2 column row by row looking for numbers until it gets past the LastRowOfData on the sheet. the If Not(IsEmpty()) statement is used to test if there's something in a cell in the SP2 column (AH) and if there is, an assumption that it is a number is made and we then calculate the I3 value. A little about the ActiveCell.Offset() instructions. Offset() takes two arguments, first the number of rows to offset, where negative numbers are "up" the sheet and positive numbers are on "down" the sheet, and zero is same row the active cell is on. Second argument is the number of columns to offset from the location; negative numbers are to the left of the reference cell, positive numbers are to the right of it. Zero is in same column. So an .Offset(0, 0) is actually no offset at all, and would refer to the reference location itself. The LC loop: is based on the value in SP2 (column AH) at the current time. The way this loop is set up it will always add at least one number to get the I3 value - the value for Q3 on the same row. The math is actually done as a single formula, the " _" at the end of a line tells VB that the line continues on the next line also. ActiveCell.Offset(0,-1) will always refer to the cell in column AG (I3) for the current cell in AH (SP2) being examined. This formula literally says "take the value in the cell in column AG and replace that value with the value calculated by taking its current contents and adding another value to it". That "another value" is obtained by using the LC value as an offset pointer over into column AF (Q3) as the row pointer. We put a - symbol in front of the reference to LC in the Offset statement to tell it to look "up" the sheet rather than down farther on the sheet. So when SP2 is 4, we would add 2 values together to come up with I3: 4/2 - 1 = 1, but our loop goes from 0 to (SP/2)-1, so we will be using offset values of 0, and 1, meaning we would add .04 and .06 together to come up with a value of .10 for I3 at that point. I hope this helps you get started on this. If you need more explanation, just ask. There are other ways to do this, but this one is, I think, the most straight-forward and most 'visible' to you at this point. I'll keep an eye here for responses (questions) and I can always be reached at HelpFrom @ jlathamsite.com (remove spaces) if the discussion needs to go in some direction that is best handled off-line, but questions and answers here will eventually serve the community better if possible. "LenS" wrote: Hello, I am trying to write a "simple" code to sum one value in a column containing values of (Q3) and add it to another which is the calculated value that I'm after (I3), in the column adjacent.. How many values of Q3 that I sum depends on what another value, SP/2 is in the last column. A code I found in easy language looks like this: For count= 0 to Int(SP/2) -1 begin I3=I3 + Q3(Count) My Excel 2000 spreadsheet looks like this: Col: AF AG AH (Q3) (I3) (SP/2) .03 .02 .05 .04 .06 X 4 .07 X1 3 . . . . . . .01 Xn 6 I want to be able to calculate I3 for x, x1 and...... xn How do I do this in VBA? Could you please also show me how you would declare the variables as well. I am just starting out in this, and if you could point me in the right direction I'd appreciate it. Thanks LS |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA: For Count, when count changes from cell to cell
JL
I'm sorry for the confusion, I should take more time to detail the problem. As for the value in AH, in my original communication: There are no blanks in this column and how this value is used is as a counting value (as per the Easy language code : For Count= 0 to Int(SP/2)-1). If AH or SP/2 is 4, then it steps through and adds the Q3 value to I3 which is initially =0. I think that's what the Easy Lang. code is saying but I'm not sure. When this counter SP/2 is done, 4 in this part, I will have obtained I3 for the current cell in AG. I then want the routine to drop to the next cell in AG and begin the process again with a new counter (SP/2 is 3 in the next case) and calculate I3 for that cell. So column AH determines how many Q3's are involved at any one run. I apologize again for not being clear. I'm sure you run into alot of that programmer vs non-programmer. I really appreciate the time you put into this, thanks. Keep in mind that column AF and AH have no blanks except at the beginning and end of the data. LenS JLatham (removethis) wrote: I'll try to help some, but I must admit to being confused by how you want the value in AH to be used to figure out which values of Q3 (In col AF) to add together to get the value for I3 in column AG. So the code may not be doing that properly. First the code, then some explanation. Sub CalculateI3() Dim I3value As Single Dim LastRowOfData as Long Dim LC as Integer ' Loop Counter 'find last row with data in column AH LastRowOfData = Range("AH" & Rows.Count).End(xlUp).Row 'go to first possible data entry in AH 'assumes row 1 has header text Range("AH2").Select 'work down thru all cells to last row used Do While ActiveCell.Row <= LastRowOfData 'assumes if cell in AH is not empty, it is number If Not(IsEmpty(ActiveCell)) Then 'I'm a little confused here, so may not be right For LC = 0 to Int(ActiveCell.Value/2)-1 ActiveCell.Offset(0,-1) = _ ActiveCell.Offset(0, -1) + _ ActiveCell.Offset(-LC,-2) Next ' end of LC loop End If ' test for empty cells 'move to next Row ActiveCell.Offset(1, 0).Activate Loop ' down thru rows End Sub The 'Dim' statements declare a variable for use later, simply reserving room for it to be used. If your code module includes the declaration Option Explicit at the beginning of it, then Dim or Const declarations are required. I recommend it because of several reasons that I won't go into here (end result: better code less prone to failure). Because we are going to be doing the math based on the contents of cells, we don't need variables to hold interim results. But we do need to know how far down the sheet to work at getting information and we need to know how many values of Q3 to add together to get the I3 values. LastRowOfData is set up as type Long so it can hold very large integer numbers, since you may have thousands of rows of data to work through. LC, to be used as a loop counter, is set up as Integer to hold smaller integer numbers, although to be on the safe side it possibly should be set up as type Long also. The first executable line of code looks up from the bottom of column AH until it finds some entry in that column. That tells us how far down the sheet we have to work in examining the information to be used. We save that row number for reference in LastRowOfData. We get into a Do While loop that is simply going to work down the SP2 column row by row looking for numbers until it gets past the LastRowOfData on the sheet. the If Not(IsEmpty()) statement is used to test if there's something in a cell in the SP2 column (AH) and if there is, an assumption that it is a number is made and we then calculate the I3 value. A little about the ActiveCell.Offset() instructions. Offset() takes two arguments, first the number of rows to offset, where negative numbers are "up" the sheet and positive numbers are on "down" the sheet, and zero is same row the active cell is on. Second argument is the number of columns to offset from the location; negative numbers are to the left of the reference cell, positive numbers are to the right of it. Zero is in same column. So an .Offset(0, 0) is actually no offset at all, and would refer to the reference location itself. The LC loop: is based on the value in SP2 (column AH) at the current time. The way this loop is set up it will always add at least one number to get the I3 value - the value for Q3 on the same row. The math is actually done as a single formula, the " _" at the end of a line tells VB that the line continues on the next line also. ActiveCell.Offset(0,-1) will always refer to the cell in column AG (I3) for the current cell in AH (SP2) being examined. This formula literally says "take the value in the cell in column AG and replace that value with the value calculated by taking its current contents and adding another value to it". That "another value" is obtained by using the LC value as an offset pointer over into column AF (Q3) as the row pointer. We put a - symbol in front of the reference to LC in the Offset statement to tell it to look "up" the sheet rather than down farther on the sheet. So when SP2 is 4, we would add 2 values together to come up with I3: 4/2 - 1 = 1, but our loop goes from 0 to (SP/2)-1, so we will be using offset values of 0, and 1, meaning we would add .04 and .06 together to come up with a value of .10 for I3 at that point. I hope this helps you get started on this. If you need more explanation, just ask. There are other ways to do this, but this one is, I think, the most straight-forward and most 'visible' to you at this point. I'll keep an eye here for responses (questions) and I can always be reached at HelpFrom @ jlathamsite.com (remove spaces) if the discussion needs to go in some direction that is best handled off-line, but questions and answers here will eventually serve the community better if possible. "LenS" wrote: Hello, I am trying to write a "simple" code to sum one value in a column containing values of (Q3) and add it to another which is the calculated value that I'm after (I3), in the column adjacent.. How many values of Q3 that I sum depends on what another value, SP/2 is in the last column. A code I found in easy language looks like this: For count= 0 to Int(SP/2) -1 begin I3=I3 + Q3(Count) My Excel 2000 spreadsheet looks like this: Col: AF AG AH (Q3) (I3) (SP/2) .03 .02 .05 .04 .06 X 4 .07 X1 3 . . . . . . .01 Xn 6 I want to be able to calculate I3 for x, x1 and...... xn How do I do this in VBA? Could you please also show me how you would declare the variables as well. I am just starting out in this, and if you could point me in the right direction I'd appreciate it. Thanks LS |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA: For Count, when count changes from cell to cell
So SP/2 is a descriptive title, not a math operation. That is, it does not
mean take the value in the column and divide that by 2, correct? To further confirm this, in your initial example, where we have .06 x and 4, what would you expect to get as the value for x in column AG? Which numbers would it use from AF, same for the next line where we have .07, x1 and 3? I think with that information we can fine tune the For LC = loop, which I think is where we are probably hung up right now. As it is written now, for the first value of 4, the code takes the 4 and divides it by 2, giving 2 and subtracts 1 from that, with a result of 1. So the loop counts from 0 to 1 and would get the .06 and .04 values. But if we are to simply take the value 4 and subtract 1 from that, then the loop would go from 0 to 3 and get values .06, .04, .05 and .02 to give a result of .17 for I3 at that point. As for the cells not containing blanks from start to end, that's great - but the code can stand as is just in case. Depending on the number of data points to be evaluated, it might add a little time to the processing, but doesn't affect things otherwise. But if you are absolutely certain of no empty cells in AH from start of data (AH2) until the end, then you could remove the If Not(IsEmpty(ActiveCell)) Then and End If lines of code from the example, leaving the rest in place. The code is/will do as you desired as far as moving one by one down through the entries in AH (SP/2) and examining each one of them and doing some math based on the value found there. The only question is exactly what math is to be done, and that's what goes on inside of the For LC = loop. "LenS" wrote: JL I'm sorry for the confusion, I should take more time to detail the problem. As for the value in AH, in my original communication: There are no blanks in this column and how this value is used is as a counting value (as per the Easy language code : For Count= 0 to Int(SP/2)-1). If AH or SP/2 is 4, then it steps through and adds the Q3 value to I3 which is initially =0. I think that's what the Easy Lang. code is saying but I'm not sure. When this counter SP/2 is done, 4 in this part, I will have obtained I3 for the current cell in AG. I then want the routine to drop to the next cell in AG and begin the process again with a new counter (SP/2 is 3 in the next case) and calculate I3 for that cell. So column AH determines how many Q3's are involved at any one run. I apologize again for not being clear. I'm sure you run into alot of that programmer vs non-programmer. I really appreciate the time you put into this, thanks. Keep in mind that column AF and AH have no blanks except at the beginning and end of the data. LenS JLatham (removethis) wrote: I'll try to help some, but I must admit to being confused by how you want the value in AH to be used to figure out which values of Q3 (In col AF) to add together to get the value for I3 in column AG. So the code may not be doing that properly. First the code, then some explanation. Sub CalculateI3() Dim I3value As Single Dim LastRowOfData as Long Dim LC as Integer ' Loop Counter 'find last row with data in column AH LastRowOfData = Range("AH" & Rows.Count).End(xlUp).Row 'go to first possible data entry in AH 'assumes row 1 has header text Range("AH2").Select 'work down thru all cells to last row used Do While ActiveCell.Row <= LastRowOfData 'assumes if cell in AH is not empty, it is number If Not(IsEmpty(ActiveCell)) Then 'I'm a little confused here, so may not be right For LC = 0 to Int(ActiveCell.Value/2)-1 ActiveCell.Offset(0,-1) = _ ActiveCell.Offset(0, -1) + _ ActiveCell.Offset(-LC,-2) Next ' end of LC loop End If ' test for empty cells 'move to next Row ActiveCell.Offset(1, 0).Activate Loop ' down thru rows End Sub The 'Dim' statements declare a variable for use later, simply reserving room for it to be used. If your code module includes the declaration Option Explicit at the beginning of it, then Dim or Const declarations are required. I recommend it because of several reasons that I won't go into here (end result: better code less prone to failure). Because we are going to be doing the math based on the contents of cells, we don't need variables to hold interim results. But we do need to know how far down the sheet to work at getting information and we need to know how many values of Q3 to add together to get the I3 values. LastRowOfData is set up as type Long so it can hold very large integer numbers, since you may have thousands of rows of data to work through. LC, to be used as a loop counter, is set up as Integer to hold smaller integer numbers, although to be on the safe side it possibly should be set up as type Long also. The first executable line of code looks up from the bottom of column AH until it finds some entry in that column. That tells us how far down the sheet we have to work in examining the information to be used. We save that row number for reference in LastRowOfData. We get into a Do While loop that is simply going to work down the SP2 column row by row looking for numbers until it gets past the LastRowOfData on the sheet. the If Not(IsEmpty()) statement is used to test if there's something in a cell in the SP2 column (AH) and if there is, an assumption that it is a number is made and we then calculate the I3 value. A little about the ActiveCell.Offset() instructions. Offset() takes two arguments, first the number of rows to offset, where negative numbers are "up" the sheet and positive numbers are on "down" the sheet, and zero is same row the active cell is on. Second argument is the number of columns to offset from the location; negative numbers are to the left of the reference cell, positive numbers are to the right of it. Zero is in same column. So an .Offset(0, 0) is actually no offset at all, and would refer to the reference location itself. The LC loop: is based on the value in SP2 (column AH) at the current time. The way this loop is set up it will always add at least one number to get the I3 value - the value for Q3 on the same row. The math is actually done as a single formula, the " _" at the end of a line tells VB that the line continues on the next line also. ActiveCell.Offset(0,-1) will always refer to the cell in column AG (I3) for the current cell in AH (SP2) being examined. This formula literally says "take the value in the cell in column AG and replace that value with the value calculated by taking its current contents and adding another value to it". That "another value" is obtained by using the LC value as an offset pointer over into column AF (Q3) as the row pointer. We put a - symbol in front of the reference to LC in the Offset statement to tell it to look "up" the sheet rather than down farther on the sheet. So when SP2 is 4, we would add 2 values together to come up with I3: 4/2 - 1 = 1, but our loop goes from 0 to (SP/2)-1, so we will be using offset values of 0, and 1, meaning we would add .04 and .06 together to come up with a value of .10 for I3 at that point. I hope this helps you get started on this. If you need more explanation, just ask. There are other ways to do this, but this one is, I think, the most straight-forward and most 'visible' to you at this point. I'll keep an eye here for responses (questions) and I can always be reached at HelpFrom @ jlathamsite.com (remove spaces) if the discussion needs to go in some direction that is best handled off-line, but questions and answers here will eventually serve the community better if possible. "LenS" wrote: Hello, I am trying to write a "simple" code to sum one value in a column containing values of (Q3) and add it to another which is the calculated value that I'm after (I3), in the column adjacent.. How many values of Q3 that I sum depends on what another value, SP/2 is in the last column. A code I found in easy language looks like this: For count= 0 to Int(SP/2) -1 begin I3=I3 + Q3(Count) My Excel 2000 spreadsheet looks like this: Col: AF AG AH (Q3) (I3) (SP/2) .03 .02 .05 .04 .06 X 4 .07 X1 3 . . . . . . .01 Xn 6 I want to be able to calculate I3 for x, x1 and...... xn How do I do this in VBA? Could you please also show me how you would declare the variables as well. I am just starting out in this, and if you could point me in the right direction I'd appreciate it. Thanks LS |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA: For Count, when count changes from cell to cell
JL,
SP/2 is already divided by 2 and its integer taken and used in the count (your correct, SP/2 is a title, the 4 is the count value in search of x, 3 is the count value in search of x1 etc etc). As far as what values of Q3 are used I'm speculating as to what the Easy Language calls for in its For Count statement: For Count= 0 to Int(SP/2)-1 I think I need 0 to 4-1 =3; 0 to 3-1=2; 0 to 2-1=1 and 0 to 1-1=0. I think the author of this procedure intends for us to use all 4 positions or counts of Q3: Position (count) 0 where Q3=.06, Position (count) 1 where Q3=.04, Position (count) 2 where Q3=.05, Position (count) 3 where Q3=.02. Position 0 always refers to current days data, position 1 refers to the previous days data and so on. If I3=0 at the beginning of the procedure, then after 1 iteration I3 = I3 (0) + Q3 (.02) =.02. I use Q3 =.02 because (4-1 = 3 count or position), Q3 at position 3 = .02. The next iteration I3 = I3 (.02) + Q3 (.05) =.07. I use Q3 =.05 because (3-1 = 2 count or position), Q3 at position 2 =.05. The third iteration I3 = I3 (.07) + Q3 (.04)=.11. I use Q3=.04 because (2-1 =1 count or position), Q3 at position 1 =.04 The last iteration for this cell (x) is I3 = I3 (.11) + Q3 (.06) =.17. I use Q3 =.06 because (1-1=0 count or position), Q3 at position 0=.06. When For Count ends, we have I3 =.17 at the X position in column AG. I would like the next cell down calculated for I3 and so on until the whole data base is calculated for I3. I hope that clears the ambiguity. Let me know if I can clarify further. Thanks for making me think more about what I want!! It seems to be an essential prerequisite to programming. Thanks again for your time, LenS LenS wrote: JL I'm sorry for the confusion, I should take more time to detail the problem. As for the value in AH, in my original communication: There are no blanks in this column and how this value is used is as a counting value (as per the Easy language code : For Count= 0 to Int(SP/2)-1). If AH or SP/2 is 4, then it steps through and adds the Q3 value to I3 which is initially =0. I think that's what the Easy Lang. code is saying but I'm not sure. When this counter SP/2 is done, 4 in this part, I will have obtained I3 for the current cell in AG. I then want the routine to drop to the next cell in AG and begin the process again with a new counter (SP/2 is 3 in the next case) and calculate I3 for that cell. So column AH determines how many Q3's are involved at any one run. I apologize again for not being clear. I'm sure you run into alot of that programmer vs non-programmer. I really appreciate the time you put into this, thanks. Keep in mind that column AF and AH have no blanks except at the beginning and end of the data. LenS JLatham wrote: I'll try to help some, but I must admit to being confused by how you want the value in AH to be used to figure out which values of Q3 (In col AF) to add together to get the value for I3 in column AG. So the code may not be doing that properly. First the code, then some explanation. Sub CalculateI3() Dim I3value As Single Dim LastRowOfData as Long Dim LC as Integer ' Loop Counter 'find last row with data in column AH LastRowOfData = Range("AH" & Rows.Count).End(xlUp).Row 'go to first possible data entry in AH 'assumes row 1 has header text Range("AH2").Select 'work down thru all cells to last row used Do While ActiveCell.Row <= LastRowOfData 'assumes if cell in AH is not empty, it is number If Not(IsEmpty(ActiveCell)) Then 'I'm a little confused here, so may not be right For LC = 0 to Int(ActiveCell.Value/2)-1 ActiveCell.Offset(0,-1) = _ ActiveCell.Offset(0, -1) + _ ActiveCell.Offset(-LC,-2) Next ' end of LC loop End If ' test for empty cells 'move to next Row ActiveCell.Offset(1, 0).Activate Loop ' down thru rows End Sub The 'Dim' statements declare a variable for use later, simply reserving room for it to be used. If your code module includes the declaration Option Explicit at the beginning of it, then Dim or Const declarations are required. I recommend it because of several reasons that I won't go into here (end result: better code less prone to failure). Because we are going to be doing the math based on the contents of cells, we don't need variables to hold interim results. But we do need to know how far down the sheet to work at getting information and we need to know how many values of Q3 to add together to get the I3 values. LastRowOfData is set up as type Long so it can hold very large integer numbers, since you may have thousands of rows of data to work through. LC, to be used as a loop counter, is set up as Integer to hold smaller integer numbers, although to be on the safe side it possibly should be set up as type Long also. The first executable line of code looks up from the bottom of column AH until it finds some entry in that column. That tells us how far down the sheet we have to work in examining the information to be used. We save that row number for reference in LastRowOfData. We get into a Do While loop that is simply going to work down the SP2 column row by row looking for numbers until it gets past the LastRowOfData on the sheet. the If Not(IsEmpty()) statement is used to test if there's something in a cell in the SP2 column (AH) and if there is, an assumption that it is a number is made and we then calculate the I3 value. A little about the ActiveCell.Offset() instructions. Offset() takes two arguments, first the number of rows to offset, where negative numbers are "up" the sheet and positive numbers are on "down" the sheet, and zero is same row the active cell is on. Second argument is the number of columns to offset from the location; negative numbers are to the left of the reference cell, positive numbers are to the right of it. Zero is in same column. So an .Offset(0, 0) is actually no offset at all, and would refer to the reference location itself. The LC loop: is based on the value in SP2 (column AH) at the current time. The way this loop is set up it will always add at least one number to get the I3 value - the value for Q3 on the same row. The math is actually done as a single formula, the " _" at the end of a line tells VB that the line continues on the next line also. ActiveCell.Offset(0,-1) will always refer to the cell in column AG (I3) for the current cell in AH (SP2) being examined. This formula literally says "take the value in the cell in column AG and replace that value with the value calculated by taking its current contents and adding another value to it". That "another value" is obtained by using the LC value as an offset pointer over into column AF (Q3) as the row pointer. We put a - symbol in front of the reference to LC in the Offset statement to tell it to look "up" the sheet rather than down farther on the sheet. So when SP2 is 4, we would add 2 values together to come up with I3: 4/2 - 1 = 1, but our loop goes from 0 to (SP/2)-1, so we will be using offset values of 0, and 1, meaning we would add .04 and .06 together to come up with a value of .10 for I3 at that point. I hope this helps you get started on this. If you need more explanation, just ask. There are other ways to do this, but this one is, I think, the most straight-forward and most 'visible' to you at this point. I'll keep an eye here for responses (questions) and I can always be reached at HelpFrom @ jlathamsite.com (remove spaces) if the discussion needs to go in some direction that is best handled off-line, but questions and answers here will eventually serve the community better if possible. "LenS" wrote: Hello, I am trying to write a "simple" code to sum one value in a column containing values of (Q3) and add it to another which is the calculated value that I'm after (I3), in the column adjacent.. How many values of Q3 that I sum depends on what another value, SP/2 is in the last column. A code I found in easy language looks like this: For count= 0 to Int(SP/2) -1 begin I3=I3 + Q3(Count) My Excel 2000 spreadsheet looks like this: Col: AF AG AH (Q3) (I3) (SP/2) .03 .02 .05 .04 .06 X 4 .07 X1 3 . . . . . . .01 Xn 6 I want to be able to calculate I3 for x, x1 and...... xn How do I do this in VBA? Could you please also show me how you would declare the variables as well. I am just starting out in this, and if you could point me in the right direction I'd appreciate it. Thanks LS |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA: For Count, when count changes from cell to cell
JL,
One more thing to add to my previous communication. As the procedure drops down to the next cell and begins calc for the next I3 at X1 : For Count 0 to 3-1: Position (count) 0 = .07 Position (count) 1= .06 Position (count) 2 = .04 I3=.17 A coincidence with the previous I3 In addition the only math that I am interested in is the sum of the Q3's over the varying counts. The math for column AH is already performed and its intger taken. The Q3's automatically change as the spreadsheet is updated. In that event I would have to run the count procedure to update the I3's. LS JLatham wrote: So SP/2 is a descriptive title, not a math operation. That is, it does not mean take the value in the column and divide that by 2, correct? To further confirm this, in your initial example, where we have .06 x and 4, what would you expect to get as the value for x in column AG? Which numbers would it use from AF, same for the next line where we have .07, x1 and 3? I think with that information we can fine tune the For LC = loop, which I think is where we are probably hung up right now. As it is written now, for the first value of 4, the code takes the 4 and divides it by 2, giving 2 and subtracts 1 from that, with a result of 1. So the loop counts from 0 to 1 and would get the .06 and .04 values. But if we are to simply take the value 4 and subtract 1 from that, then the loop would go from 0 to 3 and get values .06, .04, .05 and .02 to give a result of .17 for I3 at that point. As for the cells not containing blanks from start to end, that's great - but the code can stand as is just in case. Depending on the number of data points to be evaluated, it might add a little time to the processing, but doesn't affect things otherwise. But if you are absolutely certain of no empty cells in AH from start of data (AH2) until the end, then you could remove the If Not(IsEmpty(ActiveCell)) Then and End If lines of code from the example, leaving the rest in place. The code is/will do as you desired as far as moving one by one down through the entries in AH (SP/2) and examining each one of them and doing some math based on the value found there. The only question is exactly what math is to be done, and that's what goes on inside of the For LC = loop. "LenS" wrote: JL I'm sorry for the confusion, I should take more time to detail the problem. As for the value in AH, in my original communication: There are no blanks in this column and how this value is used is as a counting value (as per the Easy language code : For Count= 0 to Int(SP/2)-1). If AH or SP/2 is 4, then it steps through and adds the Q3 value to I3 which is initially =0. I think that's what the Easy Lang. code is saying but I'm not sure. When this counter SP/2 is done, 4 in this part, I will have obtained I3 for the current cell in AG. I then want the routine to drop to the next cell in AG and begin the process again with a new counter (SP/2 is 3 in the next case) and calculate I3 for that cell. So column AH determines how many Q3's are involved at any one run. I apologize again for not being clear. I'm sure you run into alot of that programmer vs non-programmer. I really appreciate the time you put into this, thanks. Keep in mind that column AF and AH have no blanks except at the beginning and end of the data. LenS JLatham (removethis) wrote: I'll try to help some, but I must admit to being confused by how you want the value in AH to be used to figure out which values of Q3 (In col AF) to add together to get the value for I3 in column AG. So the code may not be doing that properly. First the code, then some explanation. Sub CalculateI3() Dim I3value As Single Dim LastRowOfData as Long Dim LC as Integer ' Loop Counter 'find last row with data in column AH LastRowOfData = Range("AH" & Rows.Count).End(xlUp).Row 'go to first possible data entry in AH 'assumes row 1 has header text Range("AH2").Select 'work down thru all cells to last row used Do While ActiveCell.Row <= LastRowOfData 'assumes if cell in AH is not empty, it is number If Not(IsEmpty(ActiveCell)) Then 'I'm a little confused here, so may not be right For LC = 0 to Int(ActiveCell.Value/2)-1 ActiveCell.Offset(0,-1) = _ ActiveCell.Offset(0, -1) + _ ActiveCell.Offset(-LC,-2) Next ' end of LC loop End If ' test for empty cells 'move to next Row ActiveCell.Offset(1, 0).Activate Loop ' down thru rows End Sub The 'Dim' statements declare a variable for use later, simply reserving room for it to be used. If your code module includes the declaration Option Explicit at the beginning of it, then Dim or Const declarations are required. I recommend it because of several reasons that I won't go into here (end result: better code less prone to failure). Because we are going to be doing the math based on the contents of cells, we don't need variables to hold interim results. But we do need to know how far down the sheet to work at getting information and we need to know how many values of Q3 to add together to get the I3 values. LastRowOfData is set up as type Long so it can hold very large integer numbers, since you may have thousands of rows of data to work through. LC, to be used as a loop counter, is set up as Integer to hold smaller integer numbers, although to be on the safe side it possibly should be set up as type Long also. The first executable line of code looks up from the bottom of column AH until it finds some entry in that column. That tells us how far down the sheet we have to work in examining the information to be used. We save that row number for reference in LastRowOfData. We get into a Do While loop that is simply going to work down the SP2 column row by row looking for numbers until it gets past the LastRowOfData on the sheet. the If Not(IsEmpty()) statement is used to test if there's something in a cell in the SP2 column (AH) and if there is, an assumption that it is a number is made and we then calculate the I3 value. A little about the ActiveCell.Offset() instructions. Offset() takes two arguments, first the number of rows to offset, where negative numbers are "up" the sheet and positive numbers are on "down" the sheet, and zero is same row the active cell is on. Second argument is the number of columns to offset from the location; negative numbers are to the left of the reference cell, positive numbers are to the right of it. Zero is in same column. So an .Offset(0, 0) is actually no offset at all, and would refer to the reference location itself. The LC loop: is based on the value in SP2 (column AH) at the current time. The way this loop is set up it will always add at least one number to get the I3 value - the value for Q3 on the same row. The math is actually done as a single formula, the " _" at the end of a line tells VB that the line continues on the next line also. ActiveCell.Offset(0,-1) will always refer to the cell in column AG (I3) for the current cell in AH (SP2) being examined. This formula literally says "take the value in the cell in column AG and replace that value with the value calculated by taking its current contents and adding another value to it". That "another value" is obtained by using the LC value as an offset pointer over into column AF (Q3) as the row pointer. We put a - symbol in front of the reference to LC in the Offset statement to tell it to look "up" the sheet rather than down farther on the sheet. So when SP2 is 4, we would add 2 values together to come up with I3: 4/2 - 1 = 1, but our loop goes from 0 to (SP/2)-1, so we will be using offset values of 0, and 1, meaning we would add .04 and .06 together to come up with a value of .10 for I3 at that point. I hope this helps you get started on this. If you need more explanation, just ask. There are other ways to do this, but this one is, I think, the most straight-forward and most 'visible' to you at this point. I'll keep an eye here for responses (questions) and I can always be reached at HelpFrom @ jlathamsite.com (remove spaces) if the discussion needs to go in some direction that is best handled off-line, but questions and answers here will eventually serve the community better if possible. "LenS" wrote: Hello, I am trying to write a "simple" code to sum one value in a column containing values of (Q3) and add it to another which is the calculated value that I'm after (I3), in the column adjacent.. How many values of Q3 that I sum depends on what another value, SP/2 is in the last column. A code I found in easy language looks like this: For count= 0 to Int(SP/2) -1 begin I3=I3 + Q3(Count) My Excel 2000 spreadsheet looks like this: Col: AF AG AH (Q3) (I3) (SP/2) .03 .02 .05 .04 .06 X 4 .07 X1 3 . . . . . . .01 Xn 6 I want to be able to calculate I3 for x, x1 and...... xn How do I do this in VBA? Could you please also show me how you would declare the variables as well. I am just starting out in this, and if you could point me in the right direction I'd appreciate it. Thanks LS |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA: For Count, when count changes from cell to cell
That's quite true - one of the first things I was taught in a rather rigorous
programming school was to "define the problem". Anyhow, the initial code will work as you want with one minor change: change For LC = 0 to Int(ActiveCell.Value/2)-1 to read For LC = 0 to ActiveCell.Value-1 and that will do it. I think you can probably remove the "I'm a little confused..." comment also, if you want to <g. I'll make similar change here and do a bit of testing, but I believe we're home now. If you don't hear back from me, you should be good to go. "LenS" wrote: JL, SP/2 is already divided by 2 and its integer taken and used in the count (your correct, SP/2 is a title, the 4 is the count value in search of x, 3 is the count value in search of x1 etc etc). As far as what values of Q3 are used I'm speculating as to what the Easy Language calls for in its For Count statement: For Count= 0 to Int(SP/2)-1 I think I need 0 to 4-1 =3; 0 to 3-1=2; 0 to 2-1=1 and 0 to 1-1=0. I think the author of this procedure intends for us to use all 4 positions or counts of Q3: Position (count) 0 where Q3=.06, Position (count) 1 where Q3=.04, Position (count) 2 where Q3=.05, Position (count) 3 where Q3=.02. Position 0 always refers to current days data, position 1 refers to the previous days data and so on. If I3=0 at the beginning of the procedure, then after 1 iteration I3 = I3 (0) + Q3 (.02) =.02. I use Q3 =.02 because (4-1 = 3 count or position), Q3 at position 3 = .02. The next iteration I3 = I3 (.02) + Q3 (.05) =.07. I use Q3 =.05 because (3-1 = 2 count or position), Q3 at position 2 =.05. The third iteration I3 = I3 (.07) + Q3 (.04)=.11. I use Q3=.04 because (2-1 =1 count or position), Q3 at position 1 =.04 The last iteration for this cell (x) is I3 = I3 (.11) + Q3 (.06) =.17. I use Q3 =.06 because (1-1=0 count or position), Q3 at position 0=.06. When For Count ends, we have I3 =.17 at the X position in column AG. I would like the next cell down calculated for I3 and so on until the whole data base is calculated for I3. I hope that clears the ambiguity. Let me know if I can clarify further. Thanks for making me think more about what I want!! It seems to be an essential prerequisite to programming. Thanks again for your time, LenS LenS wrote: JL I'm sorry for the confusion, I should take more time to detail the problem. As for the value in AH, in my original communication: There are no blanks in this column and how this value is used is as a counting value (as per the Easy language code : For Count= 0 to Int(SP/2)-1). If AH or SP/2 is 4, then it steps through and adds the Q3 value to I3 which is initially =0. I think that's what the Easy Lang. code is saying but I'm not sure. When this counter SP/2 is done, 4 in this part, I will have obtained I3 for the current cell in AG. I then want the routine to drop to the next cell in AG and begin the process again with a new counter (SP/2 is 3 in the next case) and calculate I3 for that cell. So column AH determines how many Q3's are involved at any one run. I apologize again for not being clear. I'm sure you run into alot of that programmer vs non-programmer. I really appreciate the time you put into this, thanks. Keep in mind that column AF and AH have no blanks except at the beginning and end of the data. LenS JLatham wrote: I'll try to help some, but I must admit to being confused by how you want the value in AH to be used to figure out which values of Q3 (In col AF) to add together to get the value for I3 in column AG. So the code may not be doing that properly. First the code, then some explanation. Sub CalculateI3() Dim I3value As Single Dim LastRowOfData as Long Dim LC as Integer ' Loop Counter 'find last row with data in column AH LastRowOfData = Range("AH" & Rows.Count).End(xlUp).Row 'go to first possible data entry in AH 'assumes row 1 has header text Range("AH2").Select 'work down thru all cells to last row used Do While ActiveCell.Row <= LastRowOfData 'assumes if cell in AH is not empty, it is number If Not(IsEmpty(ActiveCell)) Then 'I'm a little confused here, so may not be right For LC = 0 to Int(ActiveCell.Value/2)-1 ActiveCell.Offset(0,-1) = _ ActiveCell.Offset(0, -1) + _ ActiveCell.Offset(-LC,-2) Next ' end of LC loop End If ' test for empty cells 'move to next Row ActiveCell.Offset(1, 0).Activate Loop ' down thru rows End Sub The 'Dim' statements declare a variable for use later, simply reserving room for it to be used. If your code module includes the declaration Option Explicit at the beginning of it, then Dim or Const declarations are required. I recommend it because of several reasons that I won't go into here (end result: better code less prone to failure). Because we are going to be doing the math based on the contents of cells, we don't need variables to hold interim results. But we do need to know how far down the sheet to work at getting information and we need to know how many values of Q3 to add together to get the I3 values. LastRowOfData is set up as type Long so it can hold very large integer numbers, since you may have thousands of rows of data to work through. LC, to be used as a loop counter, is set up as Integer to hold smaller integer numbers, although to be on the safe side it possibly should be set up as type Long also. The first executable line of code looks up from the bottom of column AH until it finds some entry in that column. That tells us how far down the sheet we have to work in examining the information to be used. We save that row number for reference in LastRowOfData. We get into a Do While loop that is simply going to work down the SP2 column row by row looking for numbers until it gets past the LastRowOfData on the sheet. the If Not(IsEmpty()) statement is used to test if there's something in a cell in the SP2 column (AH) and if there is, an assumption that it is a number is made and we then calculate the I3 value. A little about the ActiveCell.Offset() instructions. Offset() takes two arguments, first the number of rows to offset, where negative numbers are "up" the sheet and positive numbers are on "down" the sheet, and zero is same row the active cell is on. Second argument is the number of columns to offset from the location; negative numbers are to the left of the reference cell, positive numbers are to the right of it. Zero is in same column. So an .Offset(0, 0) is actually no offset at all, and would refer to the reference location itself. The LC loop: is based on the value in SP2 (column AH) at the current time. The way this loop is set up it will always add at least one number to get the I3 value - the value for Q3 on the same row. The math is actually done as a single formula, the " _" at the end of a line tells VB that the line continues on the next line also. ActiveCell.Offset(0,-1) will always refer to the cell in column AG (I3) for the current cell in AH (SP2) being examined. This formula literally says "take the value in the cell in column AG and replace that value with the value calculated by taking its current contents and adding another value to it". That "another value" is obtained by using the LC value as an offset pointer over into column AF (Q3) as the row pointer. We put a - symbol in front of the reference to LC in the Offset statement to tell it to look "up" the sheet rather than down farther on the sheet. So when SP2 is 4, we would add 2 values together to come up with I3: 4/2 - 1 = 1, but our loop goes from 0 to (SP/2)-1, so we will be using offset values of 0, and 1, meaning we would add .04 and .06 together to come up with a value of .10 for I3 at that point. I hope this helps you get started on this. If you need more explanation, just ask. There are other ways to do this, but this one is, I think, the most straight-forward and most 'visible' to you at this point. I'll keep an eye here for responses (questions) and I can always be reached at HelpFrom @ jlathamsite.com (remove spaces) if the discussion needs to go in some direction that is best handled off-line, but questions and answers here will eventually serve the community better if possible. "LenS" wrote: Hello, I am trying to write a "simple" code to sum one value in a column containing values of (Q3) and add it to another which is the calculated value that I'm after (I3), in the column adjacent.. How many values of Q3 that I sum depends on what another value, SP/2 is in the last column. A code I found in easy language looks like this: For count= 0 to Int(SP/2) -1 begin I3=I3 + Q3(Count) My Excel 2000 spreadsheet looks like this: Col: AF AG AH (Q3) (I3) (SP/2) .03 .02 .05 .04 .06 X 4 .07 X1 3 . . . . . . .01 Xn 6 I want to be able to calculate I3 for x, x1 and...... xn How do I do this in VBA? Could you please also show me how you would declare the variables as well. I am just starting out in this, and if you could point me in the right direction I'd appreciate it. Thanks LS |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA: For Count, when count changes from cell to cell
JL Thanks
LenS JLatham wrote: That's quite true - one of the first things I was taught in a rather rigorous programming school was to "define the problem". Anyhow, the initial code will work as you want with one minor change: change For LC = 0 to Int(ActiveCell.Value/2)-1 to read For LC = 0 to ActiveCell.Value-1 and that will do it. I think you can probably remove the "I'm a little confused..." comment also, if you want to <g. I'll make similar change here and do a bit of testing, but I believe we're home now. If you don't hear back from me, you should be good to go. "LenS" wrote: JL, SP/2 is already divided by 2 and its integer taken and used in the count (your correct, SP/2 is a title, the 4 is the count value in search of x, 3 is the count value in search of x1 etc etc). As far as what values of Q3 are used I'm speculating as to what the Easy Language calls for in its For Count statement: For Count= 0 to Int(SP/2)-1 I think I need 0 to 4-1 =3; 0 to 3-1=2; 0 to 2-1=1 and 0 to 1-1=0. I think the author of this procedure intends for us to use all 4 positions or counts of Q3: Position (count) 0 where Q3=.06, Position (count) 1 where Q3=.04, Position (count) 2 where Q3=.05, Position (count) 3 where Q3=.02. Position 0 always refers to current days data, position 1 refers to the previous days data and so on. If I3=0 at the beginning of the procedure, then after 1 iteration I3 = I3 (0) + Q3 (.02) =.02. I use Q3 =.02 because (4-1 = 3 count or position), Q3 at position 3 = .02. The next iteration I3 = I3 (.02) + Q3 (.05) =.07. I use Q3 =.05 because (3-1 = 2 count or position), Q3 at position 2 =.05. The third iteration I3 = I3 (.07) + Q3 (.04)=.11. I use Q3=.04 because (2-1 =1 count or position), Q3 at position 1 =.04 The last iteration for this cell (x) is I3 = I3 (.11) + Q3 (.06) =.17. I use Q3 =.06 because (1-1=0 count or position), Q3 at position 0=.06. When For Count ends, we have I3 =.17 at the X position in column AG. I would like the next cell down calculated for I3 and so on until the whole data base is calculated for I3. I hope that clears the ambiguity. Let me know if I can clarify further. Thanks for making me think more about what I want!! It seems to be an essential prerequisite to programming. Thanks again for your time, LenS LenS wrote: JL I'm sorry for the confusion, I should take more time to detail the problem. As for the value in AH, in my original communication: There are no blanks in this column and how this value is used is as a counting value (as per the Easy language code : For Count= 0 to Int(SP/2)-1). If AH or SP/2 is 4, then it steps through and adds the Q3 value to I3 which is initially =0. I think that's what the Easy Lang. code is saying but I'm not sure. When this counter SP/2 is done, 4 in this part, I will have obtained I3 for the current cell in AG. I then want the routine to drop to the next cell in AG and begin the process again with a new counter (SP/2 is 3 in the next case) and calculate I3 for that cell. So column AH determines how many Q3's are involved at any one run. I apologize again for not being clear. I'm sure you run into alot of that programmer vs non-programmer. I really appreciate the time you put into this, thanks. Keep in mind that column AF and AH have no blanks except at the beginning and end of the data. LenS JLatham wrote: I'll try to help some, but I must admit to being confused by how you want the value in AH to be used to figure out which values of Q3 (In col AF) to add together to get the value for I3 in column AG. So the code may not be doing that properly. First the code, then some explanation. Sub CalculateI3() Dim I3value As Single Dim LastRowOfData as Long Dim LC as Integer ' Loop Counter 'find last row with data in column AH LastRowOfData = Range("AH" & Rows.Count).End(xlUp).Row 'go to first possible data entry in AH 'assumes row 1 has header text Range("AH2").Select 'work down thru all cells to last row used Do While ActiveCell.Row <= LastRowOfData 'assumes if cell in AH is not empty, it is number If Not(IsEmpty(ActiveCell)) Then 'I'm a little confused here, so may not be right For LC = 0 to Int(ActiveCell.Value/2)-1 ActiveCell.Offset(0,-1) = _ ActiveCell.Offset(0, -1) + _ ActiveCell.Offset(-LC,-2) Next ' end of LC loop End If ' test for empty cells 'move to next Row ActiveCell.Offset(1, 0).Activate Loop ' down thru rows End Sub The 'Dim' statements declare a variable for use later, simply reserving room for it to be used. If your code module includes the declaration Option Explicit at the beginning of it, then Dim or Const declarations are required. I recommend it because of several reasons that I won't go into here (end result: better code less prone to failure). Because we are going to be doing the math based on the contents of cells, we don't need variables to hold interim results. But we do need to know how far down the sheet to work at getting information and we need to know how many values of Q3 to add together to get the I3 values. LastRowOfData is set up as type Long so it can hold very large integer numbers, since you may have thousands of rows of data to work through. LC, to be used as a loop counter, is set up as Integer to hold smaller integer numbers, although to be on the safe side it possibly should be set up as type Long also. The first executable line of code looks up from the bottom of column AH until it finds some entry in that column. That tells us how far down the sheet we have to work in examining the information to be used. We save that row number for reference in LastRowOfData. We get into a Do While loop that is simply going to work down the SP2 column row by row looking for numbers until it gets past the LastRowOfData on the sheet. the If Not(IsEmpty()) statement is used to test if there's something in a cell in the SP2 column (AH) and if there is, an assumption that it is a number is made and we then calculate the I3 value. A little about the ActiveCell.Offset() instructions. Offset() takes two arguments, first the number of rows to offset, where negative numbers are "up" the sheet and positive numbers are on "down" the sheet, and zero is same row the active cell is on. Second argument is the number of columns to offset from the location; negative numbers are to the left of the reference cell, positive numbers are to the right of it. Zero is in same column. So an .Offset(0, 0) is actually no offset at all, and would refer to the reference location itself. The LC loop: is based on the value in SP2 (column AH) at the current time. The way this loop is set up it will always add at least one number to get the I3 value - the value for Q3 on the same row. The math is actually done as a single formula, the " _" at the end of a line tells VB that the line continues on the next line also. ActiveCell.Offset(0,-1) will always refer to the cell in column AG (I3) for the current cell in AH (SP2) being examined. This formula literally says "take the value in the cell in column AG and replace that value with the value calculated by taking its current contents and adding another value to it". That "another value" is obtained by using the LC value as an offset pointer over into column AF (Q3) as the row pointer. We put a - symbol in front of the reference to LC in the Offset statement to tell it to look "up" the sheet rather than down farther on the sheet. So when SP2 is 4, we would add 2 values together to come up with I3: 4/2 - 1 = 1, but our loop goes from 0 to (SP/2)-1, so we will be using offset values of 0, and 1, meaning we would add .04 and .06 together to come up with a value of .10 for I3 at that point. I hope this helps you get started on this. If you need more explanation, just ask. There are other ways to do this, but this one is, I think, the most straight-forward and most 'visible' to you at this point. I'll keep an eye here for responses (questions) and I can always be reached at HelpFrom @ jlathamsite.com (remove spaces) if the discussion needs to go in some direction that is best handled off-line, but questions and answers here will eventually serve the community better if possible. "LenS" wrote: Hello, I am trying to write a "simple" code to sum one value in a column containing values of (Q3) and add it to another which is the calculated value that I'm after (I3), in the column adjacent.. How many values of Q3 that I sum depends on what another value, SP/2 is in the last column. A code I found in easy language looks like this: For count= 0 to Int(SP/2) -1 begin I3=I3 + Q3(Count) My Excel 2000 spreadsheet looks like this: Col: AF AG AH (Q3) (I3) (SP/2) .03 .02 .05 .04 .06 X 4 .07 X1 3 . . . . . . .01 Xn 6 I want to be able to calculate I3 for x, x1 and...... xn How do I do this in VBA? Could you please also show me how you would declare the variables as well. I am just starting out in this, and if you could point me in the right direction I'd appreciate it. Thanks LS |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA: For Count, when count changes from cell to cell
The code works perfectly - except there seems to be a bug in it! Kind of.
If you have previously calculated the I3 values using it, then add more data down the sheet and run the macro to get the I3 values for the new data, then the previously calculated values become incorrect because they don't start out as zero, they start at whatever they were calculated to be the first time out. So, if you run it once, you get .17 for an I3 value, run it again, that goes to .34, run it again and that goes to .51, etc. This can be fixed easily by putting this line of code just ahead of the For LC = instruction: ActiveCell=0 to clear out any previous results. With that in mind, I've rewritten it as a User Defined Function (UDF). This means that you can refer to it in a cell just like a built-in Excel function. This method will do away with having to call the macro to calculate the I3 values and will calculate them on the fly. Replace the previous code with this code: Function ComputeI3(myLocation As Range) As Single Application.Volatile Dim LC As Integer If IsEmpty(myLocation.Offset(0, 1)) Then Exit Function ElseIf myLocation.Offset(0, 1) < 1 Then Exit Function End If 'initialize ComputeI3 = 0 For LC = 0 To myLocation.Offset(0, 1) - 1 ComputeI3 = ComputeI3 + myLocation.Offset(-LC, -1) Next End Function Then in your I3 entries in column AG, put formula like this, this example would go into AG2: =ComputeI3(AG2) You can then fill the formula on down the sheet with the AG2 automatically changing to AG3, AG4, etc as it is filled, and it will work for you. This also relieves you from having to have these calculated from columns AF, AG and AH - the function is always relative to the current cell no matter what column it is in, so it will use the value one column to the right as the PS/2 value, and always use the values from the column immediately to it's left to get the Q3 values from. The 'Application.Volatile' statement in it makes it update with any change made on the sheet, so if you change a PS/2 or even a Q3 value, I3 will be recalculated. If the needed PS/2 value has not been entered yet, or if a value of less than one (which would cause the loop to try to go from zero to a negative value, which it cannot do), then the value 0 is returned as the I3 value. "LenS" wrote: JL Thanks LenS JLatham wrote: That's quite true - one of the first things I was taught in a rather rigorous programming school was to "define the problem". Anyhow, the initial code will work as you want with one minor change: change For LC = 0 to Int(ActiveCell.Value/2)-1 to read For LC = 0 to ActiveCell.Value-1 and that will do it. I think you can probably remove the "I'm a little confused..." comment also, if you want to <g. I'll make similar change here and do a bit of testing, but I believe we're home now. If you don't hear back from me, you should be good to go. "LenS" wrote: JL, SP/2 is already divided by 2 and its integer taken and used in the count (your correct, SP/2 is a title, the 4 is the count value in search of x, 3 is the count value in search of x1 etc etc). As far as what values of Q3 are used I'm speculating as to what the Easy Language calls for in its For Count statement: For Count= 0 to Int(SP/2)-1 I think I need 0 to 4-1 =3; 0 to 3-1=2; 0 to 2-1=1 and 0 to 1-1=0. I think the author of this procedure intends for us to use all 4 positions or counts of Q3: Position (count) 0 where Q3=.06, Position (count) 1 where Q3=.04, Position (count) 2 where Q3=.05, Position (count) 3 where Q3=.02. Position 0 always refers to current days data, position 1 refers to the previous days data and so on. If I3=0 at the beginning of the procedure, then after 1 iteration I3 = I3 (0) + Q3 (.02) =.02. I use Q3 =.02 because (4-1 = 3 count or position), Q3 at position 3 = .02. The next iteration I3 = I3 (.02) + Q3 (.05) =.07. I use Q3 =.05 because (3-1 = 2 count or position), Q3 at position 2 =.05. The third iteration I3 = I3 (.07) + Q3 (.04)=.11. I use Q3=.04 because (2-1 =1 count or position), Q3 at position 1 =.04 The last iteration for this cell (x) is I3 = I3 (.11) + Q3 (.06) =.17. I use Q3 =.06 because (1-1=0 count or position), Q3 at position 0=.06. When For Count ends, we have I3 =.17 at the X position in column AG. I would like the next cell down calculated for I3 and so on until the whole data base is calculated for I3. I hope that clears the ambiguity. Let me know if I can clarify further. Thanks for making me think more about what I want!! It seems to be an essential prerequisite to programming. Thanks again for your time, LenS LenS wrote: JL I'm sorry for the confusion, I should take more time to detail the problem. As for the value in AH, in my original communication: There are no blanks in this column and how this value is used is as a counting value (as per the Easy language code : For Count= 0 to Int(SP/2)-1). If AH or SP/2 is 4, then it steps through and adds the Q3 value to I3 which is initially =0. I think that's what the Easy Lang. code is saying but I'm not sure. When this counter SP/2 is done, 4 in this part, I will have obtained I3 for the current cell in AG. I then want the routine to drop to the next cell in AG and begin the process again with a new counter (SP/2 is 3 in the next case) and calculate I3 for that cell. So column AH determines how many Q3's are involved at any one run. I apologize again for not being clear. I'm sure you run into alot of that programmer vs non-programmer. I really appreciate the time you put into this, thanks. Keep in mind that column AF and AH have no blanks except at the beginning and end of the data. LenS JLatham wrote: I'll try to help some, but I must admit to being confused by how you want the value in AH to be used to figure out which values of Q3 (In col AF) to add together to get the value for I3 in column AG. So the code may not be doing that properly. First the code, then some explanation. Sub CalculateI3() Dim I3value As Single Dim LastRowOfData as Long Dim LC as Integer ' Loop Counter 'find last row with data in column AH LastRowOfData = Range("AH" & Rows.Count).End(xlUp).Row 'go to first possible data entry in AH 'assumes row 1 has header text Range("AH2").Select 'work down thru all cells to last row used Do While ActiveCell.Row <= LastRowOfData 'assumes if cell in AH is not empty, it is number If Not(IsEmpty(ActiveCell)) Then 'I'm a little confused here, so may not be right For LC = 0 to Int(ActiveCell.Value/2)-1 ActiveCell.Offset(0,-1) = _ ActiveCell.Offset(0, -1) + _ ActiveCell.Offset(-LC,-2) Next ' end of LC loop End If ' test for empty cells 'move to next Row ActiveCell.Offset(1, 0).Activate Loop ' down thru rows End Sub The 'Dim' statements declare a variable for use later, simply reserving room for it to be used. If your code module includes the declaration Option Explicit at the beginning of it, then Dim or Const declarations are required. I recommend it because of several reasons that I won't go into here (end result: better code less prone to failure). Because we are going to be doing the math based on the contents of cells, we don't need variables to hold interim results. But we do need to know how far down the sheet to work at getting information and we need to know how many values of Q3 to add together to get the I3 values. LastRowOfData is set up as type Long so it can hold very large integer numbers, since you may have thousands of rows of data to work through. LC, to be used as a loop counter, is set up as Integer to hold smaller integer numbers, although to be on the safe side it possibly should be set up as type Long also. The first executable line of code looks up from the bottom of column AH until it finds some entry in that column. That tells us how far down the sheet we have to work in examining the information to be used. We save that row number for reference in LastRowOfData. We get into a Do While loop that is simply going to work down the SP2 column row by row looking for numbers until it gets past the LastRowOfData on the sheet. the If Not(IsEmpty()) statement is used to test if there's something in a cell in the SP2 column (AH) and if there is, an assumption that it is a number is made and we then calculate the I3 value. A little about the ActiveCell.Offset() instructions. Offset() takes two arguments, first the number of rows to offset, where negative numbers are "up" the sheet and positive numbers are on "down" the sheet, and zero is same row the active cell is on. Second argument is the number of columns to offset from the location; negative numbers are to the left of the reference cell, positive numbers are to the right of it. Zero is in same column. So an .Offset(0, 0) is actually no offset at all, and would refer to the reference location itself. The LC loop: is based on the value in SP2 (column AH) at the current time. The way this loop is set up it will always add at least one number to get the I3 value - the value for Q3 on the same row. The math is actually done as a single formula, the " _" at the end of a line tells VB that the line continues on the next line also. ActiveCell.Offset(0,-1) will always refer to the cell in column AG (I3) for the current cell in AH (SP2) being examined. This formula literally says "take the value in the cell in column AG and replace that value with the value calculated by taking its current contents and adding another value to it". That "another value" is obtained by using the LC value as an offset pointer over into column AF (Q3) as the row pointer. We put a - symbol in front of the reference to LC in the Offset statement to tell it to look "up" the sheet rather than down farther on the sheet. So when SP2 is 4, we would add 2 values together to come up with I3: 4/2 - 1 = 1, but our loop goes from 0 to (SP/2)-1, so we will be using offset values of 0, and 1, meaning we would add .04 and .06 together to come up with a value of .10 for I3 at that point. I hope this helps you get started on this. If you need more explanation, just ask. There are other ways to do this, but this one is, I think, the most straight-forward and most 'visible' to you at this point. I'll keep an eye here for responses (questions) and I can always be reached at HelpFrom @ jlathamsite.com (remove spaces) if the discussion needs to go in some direction that is best handled off-line, but questions and answers here will eventually serve the community better if possible. "LenS" wrote: Hello, I am trying to write a "simple" code to sum one value in a column containing values of (Q3) and add it to another which is the calculated value that I'm after (I3), in the column adjacent.. How many values of Q3 that I sum depends on what another value, SP/2 is in the last column. A code I found in easy language looks like this: For count= 0 to Int(SP/2) -1 begin I3=I3 + Q3(Count) My Excel 2000 spreadsheet looks like this: Col: AF AG AH (Q3) (I3) (SP/2) .03 .02 .05 .04 .06 X 4 .07 X1 3 . . . . . . .01 Xn 6 I want to be able to calculate I3 for x, x1 and...... xn How do I do this in VBA? Could you please also show me how you would declare the variables as well. I am just starting out in this, and if you could point me in the right direction I'd appreciate it. Thanks LS |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA: For Count, when count changes from cell to cell
My VERY bad!
I forgot where I was earlier. The correction for the original code to keep the I3 value from accumulating should have read like this: This can be fixed easily by putting this line of code just ahead of the For LC = instruction: ActiveCell.Offset(0, -1) =0 to clear out any previous results. My apologies for any inconvenience that little boo-boo may have caused. "JLatham" wrote: The code works perfectly - except there seems to be a bug in it! Kind of. If you have previously calculated the I3 values using it, then add more data down the sheet and run the macro to get the I3 values for the new data, then the previously calculated values become incorrect because they don't start out as zero, they start at whatever they were calculated to be the first time out. So, if you run it once, you get .17 for an I3 value, run it again, that goes to .34, run it again and that goes to .51, etc. This can be fixed easily by putting this line of code just ahead of the For LC = instruction: ActiveCell=0 to clear out any previous results. With that in mind, I've rewritten it as a User Defined Function (UDF). This means that you can refer to it in a cell just like a built-in Excel function. This method will do away with having to call the macro to calculate the I3 values and will calculate them on the fly. Replace the previous code with this code: Function ComputeI3(myLocation As Range) As Single Application.Volatile Dim LC As Integer If IsEmpty(myLocation.Offset(0, 1)) Then Exit Function ElseIf myLocation.Offset(0, 1) < 1 Then Exit Function End If 'initialize ComputeI3 = 0 For LC = 0 To myLocation.Offset(0, 1) - 1 ComputeI3 = ComputeI3 + myLocation.Offset(-LC, -1) Next End Function Then in your I3 entries in column AG, put formula like this, this example would go into AG2: =ComputeI3(AG2) You can then fill the formula on down the sheet with the AG2 automatically changing to AG3, AG4, etc as it is filled, and it will work for you. This also relieves you from having to have these calculated from columns AF, AG and AH - the function is always relative to the current cell no matter what column it is in, so it will use the value one column to the right as the PS/2 value, and always use the values from the column immediately to it's left to get the Q3 values from. The 'Application.Volatile' statement in it makes it update with any change made on the sheet, so if you change a PS/2 or even a Q3 value, I3 will be recalculated. If the needed PS/2 value has not been entered yet, or if a value of less than one (which would cause the loop to try to go from zero to a negative value, which it cannot do), then the value 0 is returned as the I3 value. "LenS" wrote: JL Thanks LenS JLatham wrote: That's quite true - one of the first things I was taught in a rather rigorous programming school was to "define the problem". Anyhow, the initial code will work as you want with one minor change: change For LC = 0 to Int(ActiveCell.Value/2)-1 to read For LC = 0 to ActiveCell.Value-1 and that will do it. I think you can probably remove the "I'm a little confused..." comment also, if you want to <g. I'll make similar change here and do a bit of testing, but I believe we're home now. If you don't hear back from me, you should be good to go. "LenS" wrote: JL, SP/2 is already divided by 2 and its integer taken and used in the count (your correct, SP/2 is a title, the 4 is the count value in search of x, 3 is the count value in search of x1 etc etc). As far as what values of Q3 are used I'm speculating as to what the Easy Language calls for in its For Count statement: For Count= 0 to Int(SP/2)-1 I think I need 0 to 4-1 =3; 0 to 3-1=2; 0 to 2-1=1 and 0 to 1-1=0. I think the author of this procedure intends for us to use all 4 positions or counts of Q3: Position (count) 0 where Q3=.06, Position (count) 1 where Q3=.04, Position (count) 2 where Q3=.05, Position (count) 3 where Q3=.02. Position 0 always refers to current days data, position 1 refers to the previous days data and so on. If I3=0 at the beginning of the procedure, then after 1 iteration I3 = I3 (0) + Q3 (.02) =.02. I use Q3 =.02 because (4-1 = 3 count or position), Q3 at position 3 = .02. The next iteration I3 = I3 (.02) + Q3 (.05) =.07. I use Q3 =.05 because (3-1 = 2 count or position), Q3 at position 2 =.05. The third iteration I3 = I3 (.07) + Q3 (.04)=.11. I use Q3=.04 because (2-1 =1 count or position), Q3 at position 1 =.04 The last iteration for this cell (x) is I3 = I3 (.11) + Q3 (.06) =.17. I use Q3 =.06 because (1-1=0 count or position), Q3 at position 0=.06. When For Count ends, we have I3 =.17 at the X position in column AG. I would like the next cell down calculated for I3 and so on until the whole data base is calculated for I3. I hope that clears the ambiguity. Let me know if I can clarify further. Thanks for making me think more about what I want!! It seems to be an essential prerequisite to programming. Thanks again for your time, LenS LenS wrote: JL I'm sorry for the confusion, I should take more time to detail the problem. As for the value in AH, in my original communication: There are no blanks in this column and how this value is used is as a counting value (as per the Easy language code : For Count= 0 to Int(SP/2)-1). If AH or SP/2 is 4, then it steps through and adds the Q3 value to I3 which is initially =0. I think that's what the Easy Lang. code is saying but I'm not sure. When this counter SP/2 is done, 4 in this part, I will have obtained I3 for the current cell in AG. I then want the routine to drop to the next cell in AG and begin the process again with a new counter (SP/2 is 3 in the next case) and calculate I3 for that cell. So column AH determines how many Q3's are involved at any one run. I apologize again for not being clear. I'm sure you run into alot of that programmer vs non-programmer. I really appreciate the time you put into this, thanks. Keep in mind that column AF and AH have no blanks except at the beginning and end of the data. LenS JLatham wrote: I'll try to help some, but I must admit to being confused by how you want the value in AH to be used to figure out which values of Q3 (In col AF) to add together to get the value for I3 in column AG. So the code may not be doing that properly. First the code, then some explanation. Sub CalculateI3() Dim I3value As Single Dim LastRowOfData as Long Dim LC as Integer ' Loop Counter 'find last row with data in column AH LastRowOfData = Range("AH" & Rows.Count).End(xlUp).Row 'go to first possible data entry in AH 'assumes row 1 has header text Range("AH2").Select 'work down thru all cells to last row used Do While ActiveCell.Row <= LastRowOfData 'assumes if cell in AH is not empty, it is number If Not(IsEmpty(ActiveCell)) Then 'I'm a little confused here, so may not be right For LC = 0 to Int(ActiveCell.Value/2)-1 ActiveCell.Offset(0,-1) = _ ActiveCell.Offset(0, -1) + _ ActiveCell.Offset(-LC,-2) Next ' end of LC loop End If ' test for empty cells 'move to next Row ActiveCell.Offset(1, 0).Activate Loop ' down thru rows End Sub The 'Dim' statements declare a variable for use later, simply reserving room for it to be used. If your code module includes the declaration Option Explicit at the beginning of it, then Dim or Const declarations are required. I recommend it because of several reasons that I won't go into here (end result: better code less prone to failure). Because we are going to be doing the math based on the contents of cells, we don't need variables to hold interim results. But we do need to know how far down the sheet to work at getting information and we need to know how many values of Q3 to add together to get the I3 values. LastRowOfData is set up as type Long so it can hold very large integer numbers, since you may have thousands of rows of data to work through. LC, to be used as a loop counter, is set up as Integer to hold smaller integer numbers, although to be on the safe side it possibly should be set up as type Long also. The first executable line of code looks up from the bottom of column AH until it finds some entry in that column. That tells us how far down the sheet we have to work in examining the information to be used. We save that row number for reference in LastRowOfData. We get into a Do While loop that is simply going to work down the SP2 column row by row looking for numbers until it gets past the LastRowOfData on the sheet. the If Not(IsEmpty()) statement is used to test if there's something in a cell in the SP2 column (AH) and if there is, an assumption that it is a number is made and we then calculate the I3 value. A little about the ActiveCell.Offset() instructions. Offset() takes two arguments, first the number of rows to offset, where negative numbers are "up" the sheet and positive numbers are on "down" the sheet, and zero is same row the active cell is on. Second argument is the number of columns to offset from the location; negative numbers are to the left of the reference cell, positive numbers are to the right of it. Zero is in same column. So an .Offset(0, 0) is actually no offset at all, and would refer to the reference location itself. The LC loop: is based on the value in SP2 (column AH) at the current time. The way this loop is set up it will always add at least one number to get the I3 value - the value for Q3 on the same row. The math is actually done as a single formula, the " _" at the end of a line tells VB that the line continues on the next line also. ActiveCell.Offset(0,-1) will always refer to the cell in column AG (I3) for the current cell in AH (SP2) being examined. This formula literally says "take the value in the cell in column AG and replace that value with the value calculated by taking its current contents and adding another value to it". That "another value" is obtained by using the LC value as an offset pointer over into column AF (Q3) as the row pointer. We put a - symbol in front of the reference to LC in the Offset statement to tell it to look "up" the sheet rather than down farther on the sheet. So when SP2 is 4, we would add 2 values together to come up with I3: 4/2 - 1 = 1, but our loop goes from 0 to (SP/2)-1, so we will be using offset values of 0, and 1, meaning we would add .04 and .06 together to come up with a value of .10 for I3 at that point. I hope this helps you get started on this. If you need more explanation, just ask. There are other ways to do this, but this one is, I think, the most straight-forward and most 'visible' to you at this point. I'll keep an eye here for responses (questions) and I can always be reached at HelpFrom @ jlathamsite.com (remove spaces) if the discussion needs to go in some direction that is best handled off-line, but questions and answers here will eventually serve the community better if possible. "LenS" wrote: Hello, I am trying to write a "simple" code to sum one value in a column containing values of (Q3) and add it to another which is the calculated value that I'm after (I3), in the column adjacent.. How many values of Q3 that I sum depends on what another value, SP/2 is in the last column. A code I found in easy language looks like this: For count= 0 to Int(SP/2) -1 begin I3=I3 + Q3(Count) My Excel 2000 spreadsheet looks like this: Col: AF AG AH (Q3) (I3) (SP/2) .03 .02 .05 .04 .06 X 4 .07 X1 3 . . . . . . .01 Xn 6 I want to be able to calculate I3 for x, x1 and...... xn How do I do this in VBA? Could you please also show me how you would declare the variables as well. I am just starting out in this, and if you could point me in the right direction I'd appreciate it. Thanks LS |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA: For Count, when count changes from cell to cell
Hello JL,
Thanks for the code and your time. I have a question about UDF. 1) Can I step into a UDF and watch the process unfold in the code? I can not figure out why I keep getting Value# or Name# when I run the UDF. Here's the code below I entered into VB: Function ComputeI3(mylocation As Range) As Single Application.Volatile Dim LC As Integer If IsEmpty(mylocation.Offset(0, 1)) Then Exit Function ElseIf mylocation.Offset(0, 1) < 1 Then Exit Function End If 'initialize ComputeI3 = 0 ActiveCell.Offset(0, -1) = 0 For LC = 0 To mylocation.Offset(0, 1) - 1 ComputeI3 = ComputeI3 + mylocation.Offset(-LC, -1) Next End Function Thanks, LenS JLatham wrote: The code works perfectly - except there seems to be a bug in it! Kind of. If you have previously calculated the I3 values using it, then add more data down the sheet and run the macro to get the I3 values for the new data, then the previously calculated values become incorrect because they don't start out as zero, they start at whatever they were calculated to be the first time out. So, if you run it once, you get .17 for an I3 value, run it again, that goes to .34, run it again and that goes to .51, etc. This can be fixed easily by putting this line of code just ahead of the For LC = instruction: ActiveCell=0 to clear out any previous results. With that in mind, I've rewritten it as a User Defined Function (UDF). This means that you can refer to it in a cell just like a built-in Excel function. This method will do away with having to call the macro to calculate the I3 values and will calculate them on the fly. Replace the previous code with this code: Function ComputeI3(myLocation As Range) As Single Application.Volatile Dim LC As Integer If IsEmpty(myLocation.Offset(0, 1)) Then Exit Function ElseIf myLocation.Offset(0, 1) < 1 Then Exit Function End If 'initialize ComputeI3 = 0 For LC = 0 To myLocation.Offset(0, 1) - 1 ComputeI3 = ComputeI3 + myLocation.Offset(-LC, -1) Next End Function Then in your I3 entries in column AG, put formula like this, this example would go into AG2: =ComputeI3(AG2) You can then fill the formula on down the sheet with the AG2 automatically changing to AG3, AG4, etc as it is filled, and it will work for you. This also relieves you from having to have these calculated from columns AF, AG and AH - the function is always relative to the current cell no matter what column it is in, so it will use the value one column to the right as the PS/2 value, and always use the values from the column immediately to it's left to get the Q3 values from. The 'Application.Volatile' statement in it makes it update with any change made on the sheet, so if you change a PS/2 or even a Q3 value, I3 will be recalculated. If the needed PS/2 value has not been entered yet, or if a value of less than one (which would cause the loop to try to go from zero to a negative value, which it cannot do), then the value 0 is returned as the I3 value. "LenS" wrote: JL Thanks LenS JLatham wrote: That's quite true - one of the first things I was taught in a rather rigorous programming school was to "define the problem". Anyhow, the initial code will work as you want with one minor change: change For LC = 0 to Int(ActiveCell.Value/2)-1 to read For LC = 0 to ActiveCell.Value-1 and that will do it. I think you can probably remove the "I'm a little confused..." comment also, if you want to <g. I'll make similar change here and do a bit of testing, but I believe we're home now. If you don't hear back from me, you should be good to go. "LenS" wrote: JL, SP/2 is already divided by 2 and its integer taken and used in the count (your correct, SP/2 is a title, the 4 is the count value in search of x, 3 is the count value in search of x1 etc etc). As far as what values of Q3 are used I'm speculating as to what the Easy Language calls for in its For Count statement: For Count= 0 to Int(SP/2)-1 I think I need 0 to 4-1 =3; 0 to 3-1=2; 0 to 2-1=1 and 0 to 1-1=0. I think the author of this procedure intends for us to use all 4 positions or counts of Q3: Position (count) 0 where Q3=.06, Position (count) 1 where Q3=.04, Position (count) 2 where Q3=.05, Position (count) 3 where Q3=.02. Position 0 always refers to current days data, position 1 refers to the previous days data and so on. If I3=0 at the beginning of the procedure, then after 1 iteration I3 = I3 (0) + Q3 (.02) =.02. I use Q3 =.02 because (4-1 = 3 count or position), Q3 at position 3 = .02. The next iteration I3 = I3 (.02) + Q3 (.05) =.07. I use Q3 =.05 because (3-1 = 2 count or position), Q3 at position 2 =.05. The third iteration I3 = I3 (.07) + Q3 (.04)=.11. I use Q3=.04 because (2-1 =1 count or position), Q3 at position 1 =.04 The last iteration for this cell (x) is I3 = I3 (.11) + Q3 (.06) =.17. I use Q3 =.06 because (1-1=0 count or position), Q3 at position 0=.06. When For Count ends, we have I3 =.17 at the X position in column AG. I would like the next cell down calculated for I3 and so on until the whole data base is calculated for I3. I hope that clears the ambiguity. Let me know if I can clarify further. Thanks for making me think more about what I want!! It seems to be an essential prerequisite to programming. Thanks again for your time, LenS LenS wrote: JL I'm sorry for the confusion, I should take more time to detail the problem. As for the value in AH, in my original communication: There are no blanks in this column and how this value is used is as a counting value (as per the Easy language code : For Count= 0 to Int(SP/2)-1). If AH or SP/2 is 4, then it steps through and adds the Q3 value to I3 which is initially =0. I think that's what the Easy Lang. code is saying but I'm not sure. When this counter SP/2 is done, 4 in this part, I will have obtained I3 for the current cell in AG. I then want the routine to drop to the next cell in AG and begin the process again with a new counter (SP/2 is 3 in the next case) and calculate I3 for that cell. So column AH determines how many Q3's are involved at any one run. I apologize again for not being clear. I'm sure you run into alot of that programmer vs non-programmer. I really appreciate the time you put into this, thanks. Keep in mind that column AF and AH have no blanks except at the beginning and end of the data. LenS JLatham wrote: I'll try to help some, but I must admit to being confused by how you want the value in AH to be used to figure out which values of Q3 (In col AF) to add together to get the value for I3 in column AG. So the code may not be doing that properly. First the code, then some explanation. Sub CalculateI3() Dim I3value As Single Dim LastRowOfData as Long Dim LC as Integer ' Loop Counter 'find last row with data in column AH LastRowOfData = Range("AH" & Rows.Count).End(xlUp).Row 'go to first possible data entry in AH 'assumes row 1 has header text Range("AH2").Select 'work down thru all cells to last row used Do While ActiveCell.Row <= LastRowOfData 'assumes if cell in AH is not empty, it is number If Not(IsEmpty(ActiveCell)) Then 'I'm a little confused here, so may not be right For LC = 0 to Int(ActiveCell.Value/2)-1 ActiveCell.Offset(0,-1) = _ ActiveCell.Offset(0, -1) + _ ActiveCell.Offset(-LC,-2) Next ' end of LC loop End If ' test for empty cells 'move to next Row ActiveCell.Offset(1, 0).Activate Loop ' down thru rows End Sub The 'Dim' statements declare a variable for use later, simply reserving room for it to be used. If your code module includes the declaration Option Explicit at the beginning of it, then Dim or Const declarations are required. I recommend it because of several reasons that I won't go into here (end result: better code less prone to failure). Because we are going to be doing the math based on the contents of cells, we don't need variables to hold interim results. But we do need to know how far down the sheet to work at getting information and we need to know how many values of Q3 to add together to get the I3 values. LastRowOfData is set up as type Long so it can hold very large integer numbers, since you may have thousands of rows of data to work through. LC, to be used as a loop counter, is set up as Integer to hold smaller integer numbers, although to be on the safe side it possibly should be set up as type Long also. The first executable line of code looks up from the bottom of column AH until it finds some entry in that column. That tells us how far down the sheet we have to work in examining the information to be used. We save that row number for reference in LastRowOfData. We get into a Do While loop that is simply going to work down the SP2 column row by row looking for numbers until it gets past the LastRowOfData on the sheet. the If Not(IsEmpty()) statement is used to test if there's something in a cell in the SP2 column (AH) and if there is, an assumption that it is a number is made and we then calculate the I3 value. A little about the ActiveCell.Offset() instructions. Offset() takes two arguments, first the number of rows to offset, where negative numbers are "up" the sheet and positive numbers are on "down" the sheet, and zero is same row the active cell is on. Second argument is the number of columns to offset from the location; negative numbers are to the left of the reference cell, positive numbers are to the right of it. Zero is in same column. So an .Offset(0, 0) is actually no offset at all, and would refer to the reference location itself. The LC loop: is based on the value in SP2 (column AH) at the current time. The way this loop is set up it will always add at least one number to get the I3 value - the value for Q3 on the same row. The math is actually done as a single formula, the " _" at the end of a line tells VB that the line continues on the next line also. ActiveCell.Offset(0,-1) will always refer to the cell in column AG (I3) for the current cell in AH (SP2) being examined. This formula literally says "take the value in the cell in column AG and replace that value with the value calculated by taking its current contents and adding another value to it". That "another value" is obtained by using the LC value as an offset pointer over into column AF (Q3) as the row pointer. We put a - symbol in front of the reference to LC in the Offset statement to tell it to look "up" the sheet rather than down farther on the sheet. So when SP2 is 4, we would add 2 values together to come up with I3: 4/2 - 1 = 1, but our loop goes from 0 to (SP/2)-1, so we will be using offset values of 0, and 1, meaning we would add .04 and .06 together to come up with a value of .10 for I3 at that point. I hope this helps you get started on this. If you need more explanation, just ask. There are other ways to do this, but this one is, I think, the most straight-forward and most 'visible' to you at this point. I'll keep an eye here for responses (questions) and I can always be reached at HelpFrom @ jlathamsite.com (remove spaces) if the discussion needs to go in some direction that is best handled off-line, but questions and answers here will eventually serve the community better if possible. "LenS" wrote: Hello, I am trying to write a "simple" code to sum one value in a column containing values of (Q3) and add it to another which is the calculated value that I'm after (I3), in the column adjacent.. How many values of Q3 that I sum depends on what another value, SP/2 is in the last column. A code I found in easy language looks like this: For count= 0 to Int(SP/2) -1 begin I3=I3 + Q3(Count) My Excel 2000 spreadsheet looks like this: Col: AF AG AH (Q3) (I3) (SP/2) .03 .02 .05 .04 .06 X 4 .07 X1 3 . . . . . . .01 Xn 6 I want to be able to calculate I3 for x, x1 and...... xn How do I do this in VBA? Could you please also show me how you would declare the variables as well. I am just starting out in this, and if you could point me in the right direction I'd appreciate it. Thanks LS |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA: For Count, when count changes from cell to cell
You should be able to. Just put a
Stop command just ahead of the If IsEmpty( line. But it will be "noisy" if you've got it called many places. I can see where it might give a problem if mylocation is column A. Down in the For loop: if you give it a myLocation value that is in column A, then the -1 column offset would make things invalid. As I recall, myLocation should be the center column of a 3-column grouping, i.e. it would be column AG in a group composed of columns AF AG and AH. Another potential problem, if you have a huge number of entries to evaluate is LC itself. Try changing that from Dim LC as Integer to Dim LC as Long Could be that if you have a large number of entries to evaluate on a sheet that it is getting beyond the limit of the Integer type. I kind of figured you'd be dealing with adding up groups smaller than 32767 entries to be added together. If there are more, then it would overflow the integer limit. I've uploaded a working .xls file that shows/uses both the UDF and the Sub methods and works for me with the limited data in it. It contains the code as presented here (including the LC as Integer statement for the moment). So maybe seeing it in action will help? http://www.jlathamsite.com/uploads/f...lculations.xls "LenS" wrote: Hello JL, Thanks for the code and your time. I have a question about UDF. 1) Can I step into a UDF and watch the process unfold in the code? I can not figure out why I keep getting Value# or Name# when I run the UDF. Here's the code below I entered into VB: Function ComputeI3(mylocation As Range) As Single Application.Volatile Dim LC As Integer If IsEmpty(mylocation.Offset(0, 1)) Then Exit Function ElseIf mylocation.Offset(0, 1) < 1 Then Exit Function End If 'initialize ComputeI3 = 0 ActiveCell.Offset(0, -1) = 0 For LC = 0 To mylocation.Offset(0, 1) - 1 ComputeI3 = ComputeI3 + mylocation.Offset(-LC, -1) Next End Function Thanks, LenS JLatham wrote: The code works perfectly - except there seems to be a bug in it! Kind of. If you have previously calculated the I3 values using it, then add more data down the sheet and run the macro to get the I3 values for the new data, then the previously calculated values become incorrect because they don't start out as zero, they start at whatever they were calculated to be the first time out. So, if you run it once, you get .17 for an I3 value, run it again, that goes to .34, run it again and that goes to .51, etc. This can be fixed easily by putting this line of code just ahead of the For LC = instruction: ActiveCell=0 to clear out any previous results. With that in mind, I've rewritten it as a User Defined Function (UDF). This means that you can refer to it in a cell just like a built-in Excel function. This method will do away with having to call the macro to calculate the I3 values and will calculate them on the fly. Replace the previous code with this code: Function ComputeI3(myLocation As Range) As Single Application.Volatile Dim LC As Integer If IsEmpty(myLocation.Offset(0, 1)) Then Exit Function ElseIf myLocation.Offset(0, 1) < 1 Then Exit Function End If 'initialize ComputeI3 = 0 For LC = 0 To myLocation.Offset(0, 1) - 1 ComputeI3 = ComputeI3 + myLocation.Offset(-LC, -1) Next End Function Then in your I3 entries in column AG, put formula like this, this example would go into AG2: =ComputeI3(AG2) You can then fill the formula on down the sheet with the AG2 automatically changing to AG3, AG4, etc as it is filled, and it will work for you. This also relieves you from having to have these calculated from columns AF, AG and AH - the function is always relative to the current cell no matter what column it is in, so it will use the value one column to the right as the PS/2 value, and always use the values from the column immediately to it's left to get the Q3 values from. The 'Application.Volatile' statement in it makes it update with any change made on the sheet, so if you change a PS/2 or even a Q3 value, I3 will be recalculated. If the needed PS/2 value has not been entered yet, or if a value of less than one (which would cause the loop to try to go from zero to a negative value, which it cannot do), then the value 0 is returned as the I3 value. "LenS" wrote: JL Thanks LenS JLatham wrote: That's quite true - one of the first things I was taught in a rather rigorous programming school was to "define the problem". Anyhow, the initial code will work as you want with one minor change: change For LC = 0 to Int(ActiveCell.Value/2)-1 to read For LC = 0 to ActiveCell.Value-1 and that will do it. I think you can probably remove the "I'm a little confused..." comment also, if you want to <g. I'll make similar change here and do a bit of testing, but I believe we're home now. If you don't hear back from me, you should be good to go. "LenS" wrote: JL, SP/2 is already divided by 2 and its integer taken and used in the count (your correct, SP/2 is a title, the 4 is the count value in search of x, 3 is the count value in search of x1 etc etc). As far as what values of Q3 are used I'm speculating as to what the Easy Language calls for in its For Count statement: For Count= 0 to Int(SP/2)-1 I think I need 0 to 4-1 =3; 0 to 3-1=2; 0 to 2-1=1 and 0 to 1-1=0. I think the author of this procedure intends for us to use all 4 positions or counts of Q3: Position (count) 0 where Q3=.06, Position (count) 1 where Q3=.04, Position (count) 2 where Q3=.05, Position (count) 3 where Q3=.02. Position 0 always refers to current days data, position 1 refers to the previous days data and so on. If I3=0 at the beginning of the procedure, then after 1 iteration I3 = I3 (0) + Q3 (.02) =.02. I use Q3 =.02 because (4-1 = 3 count or position), Q3 at position 3 = .02. The next iteration I3 = I3 (.02) + Q3 (.05) =.07. I use Q3 =.05 because (3-1 = 2 count or position), Q3 at position 2 =.05. The third iteration I3 = I3 (.07) + Q3 (.04)=.11. I use Q3=.04 because (2-1 =1 count or position), Q3 at position 1 =.04 The last iteration for this cell (x) is I3 = I3 (.11) + Q3 (.06) =.17. I use Q3 =.06 because (1-1=0 count or position), Q3 at position 0=.06. When For Count ends, we have I3 =.17 at the X position in column AG. I would like the next cell down calculated for I3 and so on until the whole data base is calculated for I3. I hope that clears the ambiguity. Let me know if I can clarify further. Thanks for making me think more about what I want!! It seems to be an essential prerequisite to programming. Thanks again for your time, LenS LenS wrote: JL I'm sorry for the confusion, I should take more time to detail the problem. As for the value in AH, in my original communication: There are no blanks in this column and how this value is used is as a counting value (as per the Easy language code : For Count= 0 to Int(SP/2)-1). If AH or SP/2 is 4, then it steps through and adds the Q3 value to I3 which is initially =0. I think that's what the Easy Lang. code is saying but I'm not sure. When this counter SP/2 is done, 4 in this part, I will have obtained I3 for the current cell in AG. I then want the routine to drop to the next cell in AG and begin the process again with a new counter (SP/2 is 3 in the next case) and calculate I3 for that cell. So column AH determines how many Q3's are involved at any one run. I apologize again for not being clear. I'm sure you run into alot of that programmer vs non-programmer. I really appreciate the time you put into this, thanks. Keep in mind that column AF and AH have no blanks except at the beginning and end of the data. LenS JLatham wrote: I'll try to help some, but I must admit to being confused by how you want the value in AH to be used to figure out which values of Q3 (In col AF) to add together to get the value for I3 in column AG. So the code may not be doing that properly. First the code, then some explanation. Sub CalculateI3() Dim I3value As Single Dim LastRowOfData as Long Dim LC as Integer ' Loop Counter 'find last row with data in column AH LastRowOfData = Range("AH" & Rows.Count).End(xlUp).Row 'go to first possible data entry in AH 'assumes row 1 has header text Range("AH2").Select 'work down thru all cells to last row used Do While ActiveCell.Row <= LastRowOfData 'assumes if cell in AH is not empty, it is number If Not(IsEmpty(ActiveCell)) Then 'I'm a little confused here, so may not be right For LC = 0 to Int(ActiveCell.Value/2)-1 ActiveCell.Offset(0,-1) = _ ActiveCell.Offset(0, -1) + _ ActiveCell.Offset(-LC,-2) Next ' end of LC loop End If ' test for empty cells 'move to next Row ActiveCell.Offset(1, 0).Activate Loop ' down thru rows End Sub The 'Dim' statements declare a variable for use later, simply reserving room for it to be used. If your code module includes the declaration Option Explicit at the beginning of it, then Dim or Const declarations are required. I recommend it because of several reasons that I won't go into here (end result: better code less prone to failure). Because we are going to be doing the math based on the contents of cells, we don't need variables to hold interim results. But we do need to know how far down the sheet to work at getting information and we need to know how many values of Q3 to add together to get the I3 values. LastRowOfData is set up as type Long so it can hold very large integer numbers, since you may have thousands of rows of data to work through. LC, to be used as a loop counter, is set up as Integer to hold smaller integer numbers, although to be on the safe side it possibly should be set up as type Long also. The first executable line of code looks up from the bottom of column AH until it finds some entry in that column. That tells us how far down the sheet we have to work in examining the information to be used. We save that row number for reference in LastRowOfData. We get into a Do While loop that is simply going to work down the SP2 column row by row looking for numbers until it gets past the LastRowOfData on the sheet. the If Not(IsEmpty()) statement is used to test if there's something in a cell in the SP2 column (AH) and if there is, an assumption that it is a number is made and we then calculate the I3 value. A little about the ActiveCell.Offset() instructions. Offset() takes two arguments, first the number of rows to offset, where negative numbers are "up" the sheet and positive numbers are on "down" the sheet, and zero is same row the active cell is on. Second argument is the number of columns to offset from the location; negative numbers are to the left of the reference cell, positive numbers are to the right of it. Zero is in same column. So an .Offset(0, 0) is actually no offset at all, and would refer to the reference location itself. The LC loop: is based on the value in SP2 (column AH) at the current time. The way this loop is set up it will always add at least one number to get the I3 value - the value for Q3 on the same row. The math is actually done as a single formula, the " _" at the end of a line tells VB that the line continues on the next line also. ActiveCell.Offset(0,-1) will always refer to the cell in column AG (I3) for the current cell in AH (SP2) being examined. This formula literally says "take the value in the cell in column AG and replace that value with the value calculated by taking its current contents and adding another value to it". That "another value" is obtained by using the LC value as an offset pointer over into column AF (Q3) as the row pointer. We put a - symbol in front of the reference to LC in the Offset statement to tell it to look "up" the sheet rather than down farther on the sheet. So when SP2 is 4, we would add 2 values together to come up with I3: 4/2 - 1 = 1, but our loop goes from 0 to (SP/2)-1, so we will be using offset values of 0, and 1, meaning we would add .04 and .06 together to come up with a value of .10 for I3 at that point. I hope this helps you get started on this. If you need more explanation, just ask. There are other ways to do this, but this one is, I think, the most straight-forward and most 'visible' to you at this point. I'll keep an eye here for responses (questions) and I can always be reached at HelpFrom @ jlathamsite.com (remove spaces) if the discussion needs to go in some direction that is best handled off-line, but questions and answers here will eventually serve the community better if possible. "LenS" wrote: Hello, |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA: For Count, when count changes from cell to cell
Happy New Year,
The Function and Macro codes worked exactly like I wanted in the spreadsheet I down-loaded from your link. I was unable to get the code into my Excel though. The macro option was blanked out on your toolbar. I don't understand why this is? Can you help? If I can get this and duplicate were home free. Thanks, Len LenS wrote: Hello JL, Thanks for the code and your time. I have a question about UDF. 1) Can I step into a UDF and watch the process unfold in the code? I can not figure out why I keep getting Value# or Name# when I run the UDF. Here's the code below I entered into VB: Function ComputeI3(mylocation As Range) As Single Application.Volatile Dim LC As Integer If IsEmpty(mylocation.Offset(0, 1)) Then Exit Function ElseIf mylocation.Offset(0, 1) < 1 Then Exit Function End If 'initialize ComputeI3 = 0 ActiveCell.Offset(0, -1) = 0 For LC = 0 To mylocation.Offset(0, 1) - 1 ComputeI3 = ComputeI3 + mylocation.Offset(-LC, -1) Next End Function Thanks, LenS JLatham wrote: The code works perfectly - except there seems to be a bug in it! Kind of. If you have previously calculated the I3 values using it, then add more data down the sheet and run the macro to get the I3 values for the new data, then the previously calculated values become incorrect because they don't start out as zero, they start at whatever they were calculated to be the first time out. So, if you run it once, you get .17 for an I3 value, run it again, that goes to .34, run it again and that goes to .51, etc. This can be fixed easily by putting this line of code just ahead of the For LC = instruction: ActiveCell=0 to clear out any previous results. With that in mind, I've rewritten it as a User Defined Function (UDF). This means that you can refer to it in a cell just like a built-in Excel function. This method will do away with having to call the macro to calculate the I3 values and will calculate them on the fly. Replace the previous code with this code: Function ComputeI3(myLocation As Range) As Single Application.Volatile Dim LC As Integer If IsEmpty(myLocation.Offset(0, 1)) Then Exit Function ElseIf myLocation.Offset(0, 1) < 1 Then Exit Function End If 'initialize ComputeI3 = 0 For LC = 0 To myLocation.Offset(0, 1) - 1 ComputeI3 = ComputeI3 + myLocation.Offset(-LC, -1) Next End Function Then in your I3 entries in column AG, put formula like this, this example would go into AG2: =ComputeI3(AG2) You can then fill the formula on down the sheet with the AG2 automatically changing to AG3, AG4, etc as it is filled, and it will work for you. This also relieves you from having to have these calculated from columns AF, AG and AH - the function is always relative to the current cell no matter what column it is in, so it will use the value one column to the right as the PS/2 value, and always use the values from the column immediately to it's left to get the Q3 values from. The 'Application.Volatile' statement in it makes it update with any change made on the sheet, so if you change a PS/2 or even a Q3 value, I3 will be recalculated. If the needed PS/2 value has not been entered yet, or if a value of less than one (which would cause the loop to try to go from zero to a negative value, which it cannot do), then the value 0 is returned as the I3 value. "LenS" wrote: JL Thanks LenS JLatham wrote: That's quite true - one of the first things I was taught in a rather rigorous programming school was to "define the problem". Anyhow, the initial code will work as you want with one minor change: change For LC = 0 to Int(ActiveCell.Value/2)-1 to read For LC = 0 to ActiveCell.Value-1 and that will do it. I think you can probably remove the "I'm a little confused..." comment also, if you want to <g. I'll make similar change here and do a bit of testing, but I believe we're home now. If you don't hear back from me, you should be good to go. "LenS" wrote: JL, SP/2 is already divided by 2 and its integer taken and used in the count (your correct, SP/2 is a title, the 4 is the count value in search of x, 3 is the count value in search of x1 etc etc). As far as what values of Q3 are used I'm speculating as to what the Easy Language calls for in its For Count statement: For Count= 0 to Int(SP/2)-1 I think I need 0 to 4-1 =3; 0 to 3-1=2; 0 to 2-1=1 and 0 to 1-1=0. I think the author of this procedure intends for us to use all 4 positions or counts of Q3: Position (count) 0 where Q3=.06, Position (count) 1 where Q3=.04, Position (count) 2 where Q3=.05, Position (count) 3 where Q3=.02. Position 0 always refers to current days data, position 1 refers to the previous days data and so on. If I3=0 at the beginning of the procedure, then after 1 iteration I3 = I3 (0) + Q3 (.02) =.02. I use Q3 =.02 because (4-1 = 3 count or position), Q3 at position 3 = .02. The next iteration I3 = I3 (.02) + Q3 (.05) =.07. I use Q3 =.05 because (3-1 = 2 count or position), Q3 at position 2 =.05. The third iteration I3 = I3 (.07) + Q3 (.04)=.11. I use Q3=.04 because (2-1 =1 count or position), Q3 at position 1 =.04 The last iteration for this cell (x) is I3 = I3 (.11) + Q3 (.06) =.17. I use Q3 =.06 because (1-1=0 count or position), Q3 at position 0=.06. When For Count ends, we have I3 =.17 at the X position in column AG. I would like the next cell down calculated for I3 and so on until the whole data base is calculated for I3. I hope that clears the ambiguity. Let me know if I can clarify further. Thanks for making me think more about what I want!! It seems to be an essential prerequisite to programming. Thanks again for your time, LenS LenS wrote: JL I'm sorry for the confusion, I should take more time to detail the problem. As for the value in AH, in my original communication: There are no blanks in this column and how this value is used is as a counting value (as per the Easy language code : For Count= 0 to Int(SP/2)-1). If AH or SP/2 is 4, then it steps through and adds the Q3 value to I3 which is initially =0. I think that's what the Easy Lang. code is saying but I'm not sure. When this counter SP/2 is done, 4 in this part, I will have obtained I3 for the current cell in AG. I then want the routine to drop to the next cell in AG and begin the process again with a new counter (SP/2 is 3 in the next case) and calculate I3 for that cell. So column AH determines how many Q3's are involved at any one run. I apologize again for not being clear. I'm sure you run into alot of that programmer vs non-programmer. I really appreciate the time you put into this, thanks. Keep in mind that column AF and AH have no blanks except at the beginning and end of the data. LenS JLatham wrote: I'll try to help some, but I must admit to being confused by how you want the value in AH to be used to figure out which values of Q3 (In col AF) to add together to get the value for I3 in column AG. So the code may not be doing that properly. First the code, then some explanation. Sub CalculateI3() Dim I3value As Single Dim LastRowOfData as Long Dim LC as Integer ' Loop Counter 'find last row with data in column AH LastRowOfData = Range("AH" & Rows.Count).End(xlUp).Row 'go to first possible data entry in AH 'assumes row 1 has header text Range("AH2").Select 'work down thru all cells to last row used Do While ActiveCell.Row <= LastRowOfData 'assumes if cell in AH is not empty, it is number If Not(IsEmpty(ActiveCell)) Then 'I'm a little confused here, so may not be right For LC = 0 to Int(ActiveCell.Value/2)-1 ActiveCell.Offset(0,-1) = _ ActiveCell.Offset(0, -1) + _ ActiveCell.Offset(-LC,-2) Next ' end of LC loop End If ' test for empty cells 'move to next Row ActiveCell.Offset(1, 0).Activate Loop ' down thru rows End Sub The 'Dim' statements declare a variable for use later, simply reserving room for it to be used. If your code module includes the declaration Option Explicit at the beginning of it, then Dim or Const declarations are required. I recommend it because of several reasons that I won't go into here (end result: better code less prone to failure). Because we are going to be doing the math based on the contents of cells, we don't need variables to hold interim results. But we do need to know how far down the sheet to work at getting information and we need to know how many values of Q3 to add together to get the I3 values. LastRowOfData is set up as type Long so it can hold very large integer numbers, since you may have thousands of rows of data to work through. LC, to be used as a loop counter, is set up as Integer to hold smaller integer numbers, although to be on the safe side it possibly should be set up as type Long also. The first executable line of code looks up from the bottom of column AH until it finds some entry in that column. That tells us how far down the sheet we have to work in examining the information to be used. We save that row number for reference in LastRowOfData. We get into a Do While loop that is simply going to work down the SP2 column row by row looking for numbers until it gets past the LastRowOfData on the sheet. the If Not(IsEmpty()) statement is used to test if there's something in a cell in the SP2 column (AH) and if there is, an assumption that it is a number is made and we then calculate the I3 value. A little about the ActiveCell.Offset() instructions. Offset() takes two arguments, first the number of rows to offset, where negative numbers are "up" the sheet and positive numbers are on "down" the sheet, and zero is same row the active cell is on. Second argument is the number of columns to offset from the location; negative numbers are to the left of the reference cell, positive numbers are to the right of it. Zero is in same column. So an .Offset(0, 0) is actually no offset at all, and would refer to the reference location itself. The LC loop: is based on the value in SP2 (column AH) at the current time. The way this loop is set up it will always add at least one number to get the I3 value - the value for Q3 on the same row. The math is actually done as a single formula, the " _" at the end of a line tells VB that the line continues on the next line also. ActiveCell.Offset(0,-1) will always refer to the cell in column AG (I3) for the current cell in AH (SP2) being examined. This formula literally says "take the value in the cell in column AG and replace that value with the value calculated by taking its current contents and adding another value to it". That "another value" is obtained by using the LC value as an offset pointer over into column AF (Q3) as the row pointer. We put a - symbol in front of the reference to LC in the Offset statement to tell it to look "up" the sheet rather than down farther on the sheet. So when SP2 is 4, we would add 2 values together to come up with I3: 4/2 - 1 = 1, but our loop goes from 0 to (SP/2)-1, so we will be using offset values of 0, and 1, meaning we would add .04 and .06 together to come up with a value of .10 for I3 at that point. I hope this helps you get started on this. If you need more explanation, just ask. There are other ways to do this, but this one is, I think, the most straight-forward and most 'visible' to you at this point. I'll keep an eye here for responses (questions) and I can always be reached at HelpFrom @ jlathamsite.com (remove spaces) if the discussion needs to go in some direction that is best handled off-line, but questions and answers here will eventually serve the community better if possible. "LenS" wrote: Hello, I am trying to write a "simple" code to sum one value in a column containing values of (Q3) and add it to another which is the calculated value that I'm after (I3), in the column adjacent.. How many values of Q3 that I sum depends on what another value, SP/2 is in the last column. A code I found in easy language looks like this: For count= 0 to Int(SP/2) -1 begin I3=I3 + Q3(Count) My Excel 2000 spreadsheet looks like this: Col: AF AG AH (Q3) (I3) (SP/2) .03 .02 .05 .04 .06 X 4 .07 X1 3 . . . . . . .01 Xn 6 I want to be able to calculate I3 for x, x1 and...... xn How do I do this in VBA? Could you please also show me how you would declare the variables as well. I am just starting out in this, and if you could point me in the right direction I'd appreciate it. Thanks LS |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA: For Count, when count changes from cell to cell
You've actually got me stumped on that one! I don't recall anyone ever
reporting Macro being unavailable. Probably some simple setting somewhere, but I just cannot think of where it would be. I changed my Macro Security to highest level and even then the options were still available. Quickest answer may be to post this as a new question, and someone else who's encountered the problem should answer quickly. I'd ask in the 'Programming' section - I would think that's where most familiarity would be. Tell them what version of Excel you have, and what version of Windows. Tell them the problems you're having: #1 - you used this code and got #Value and #Name errors in your own workbook (that kind of hints at VB problems) #2 - you got copy of working code in a workbook, but Macro button is grayed out (tell which button in the sequence Tools | Macro or Tools | Macro | Macros). My file was created in Excel 2003, and there is no special protection applied to either the VBA project or to the sheets or workbook. It's pretty plain-jane file. I'll leave the file out and available if someone else needs/wants to download and look at it. #3 - do some more quick testing and see what else works/doesn't work - -- can you record a macro and then run it later? #4 - can you use [Alt]+[F11] to get into the VB Editor? Here is the code copied right from the workbook you downloaded. It goes into a regular VBA code module. Maybe you can copy and paste into your own workbook again? Sub CalculateI3() Dim I3value As Single Dim LastRowOfData As Long Dim LC As Integer ' Loop Counter 'find last row with data in column AH LastRowOfData = Range("AH" & Rows.Count).End(xlUp).Row 'go to first possible data entry in AH 'assumes row 1 has header text Range("AH2").Select 'work down thru all cells to last row used Do While ActiveCell.Row <= LastRowOfData 'assumes if cell in AH is not empty, it is number If Not (IsEmpty(ActiveCell)) Then ActiveCell.Offset(0, -1) = 0 ' reset!! For LC = 0 To Int(ActiveCell.Value) - 1 ActiveCell.Offset(0, -1) = _ ActiveCell.Offset(0, -1) + _ ActiveCell.Offset(-LC, -2) Next ' end of LC loop End If ' test for empty cells 'move to next Row ActiveCell.Offset(1, 0).Activate Loop ' down thru rows End Sub Function ComputeI3(myLocation As Range) As Single Application.Volatile Dim LC As Integer If IsEmpty(myLocation.Offset(0, 1)) Then Exit Function ElseIf myLocation.Offset(0, 1) < 1 Then Exit Function End If 'initialize ComputeI3 = 0 For LC = 0 To myLocation.Offset(0, 1) - 1 ComputeI3 = ComputeI3 + myLocation.Offset(-LC, -1) Next End Function "LenS" wrote: Happy New Year, The Function and Macro codes worked exactly like I wanted in the spreadsheet I down-loaded from your link. I was unable to get the code into my Excel though. The macro option was blanked out on your toolbar. I don't understand why this is? Can you help? If I can get this and duplicate were home free. Thanks, Len LenS wrote: Hello JL, Thanks for the code and your time. I have a question about UDF. 1) Can I step into a UDF and watch the process unfold in the code? I can not figure out why I keep getting Value# or Name# when I run the UDF. Here's the code below I entered into VB: Function ComputeI3(mylocation As Range) As Single Application.Volatile Dim LC As Integer If IsEmpty(mylocation.Offset(0, 1)) Then Exit Function ElseIf mylocation.Offset(0, 1) < 1 Then Exit Function End If 'initialize ComputeI3 = 0 ActiveCell.Offset(0, -1) = 0 For LC = 0 To mylocation.Offset(0, 1) - 1 ComputeI3 = ComputeI3 + mylocation.Offset(-LC, -1) Next End Function Thanks, LenS JLatham wrote: The code works perfectly - except there seems to be a bug in it! Kind of. If you have previously calculated the I3 values using it, then add more data down the sheet and run the macro to get the I3 values for the new data, then the previously calculated values become incorrect because they don't start out as zero, they start at whatever they were calculated to be the first time out. So, if you run it once, you get .17 for an I3 value, run it again, that goes to .34, run it again and that goes to .51, etc. This can be fixed easily by putting this line of code just ahead of the For LC = instruction: ActiveCell=0 to clear out any previous results. With that in mind, I've rewritten it as a User Defined Function (UDF). This means that you can refer to it in a cell just like a built-in Excel function. This method will do away with having to call the macro to calculate the I3 values and will calculate them on the fly. Replace the previous code with this code: Function ComputeI3(myLocation As Range) As Single Application.Volatile Dim LC As Integer If IsEmpty(myLocation.Offset(0, 1)) Then Exit Function ElseIf myLocation.Offset(0, 1) < 1 Then Exit Function End If 'initialize ComputeI3 = 0 For LC = 0 To myLocation.Offset(0, 1) - 1 ComputeI3 = ComputeI3 + myLocation.Offset(-LC, -1) Next End Function Then in your I3 entries in column AG, put formula like this, this example would go into AG2: =ComputeI3(AG2) You can then fill the formula on down the sheet with the AG2 automatically changing to AG3, AG4, etc as it is filled, and it will work for you. This also relieves you from having to have these calculated from columns AF, AG and AH - the function is always relative to the current cell no matter what column it is in, so it will use the value one column to the right as the PS/2 value, and always use the values from the column immediately to it's left to get the Q3 values from. The 'Application.Volatile' statement in it makes it update with any change made on the sheet, so if you change a PS/2 or even a Q3 value, I3 will be recalculated. If the needed PS/2 value has not been entered yet, or if a value of less than one (which would cause the loop to try to go from zero to a negative value, which it cannot do), then the value 0 is returned as the I3 value. "LenS" wrote: JL Thanks LenS JLatham wrote: That's quite true - one of the first things I was taught in a rather rigorous programming school was to "define the problem". Anyhow, the initial code will work as you want with one minor change: change For LC = 0 to Int(ActiveCell.Value/2)-1 to read For LC = 0 to ActiveCell.Value-1 and that will do it. I think you can probably remove the "I'm a little confused..." comment also, if you want to <g. I'll make similar change here and do a bit of testing, but I believe we're home now. If you don't hear back from me, you should be good to go. "LenS" wrote: JL, SP/2 is already divided by 2 and its integer taken and used in the count (your correct, SP/2 is a title, the 4 is the count value in search of x, 3 is the count value in search of x1 etc etc). As far as what values of Q3 are used I'm speculating as to what the Easy Language calls for in its For Count statement: For Count= 0 to Int(SP/2)-1 I think I need 0 to 4-1 =3; 0 to 3-1=2; 0 to 2-1=1 and 0 to 1-1=0. I think the author of this procedure intends for us to use all 4 positions or counts of Q3: Position (count) 0 where Q3=.06, Position (count) 1 where Q3=.04, Position (count) 2 where Q3=.05, Position (count) 3 where Q3=.02. Position 0 always refers to current days data, position 1 refers to the previous days data and so on. If I3=0 at the beginning of the procedure, then after 1 iteration I3 = I3 (0) + Q3 (.02) =.02. I use Q3 =.02 because (4-1 = 3 count or position), Q3 at position 3 = .02. The next iteration I3 = I3 (.02) + Q3 (.05) =.07. I use Q3 =.05 because (3-1 = 2 count or position), Q3 at position 2 =.05. The third iteration I3 = I3 (.07) + Q3 (.04)=.11. I use Q3=.04 because (2-1 =1 count or position), Q3 at position 1 =.04 The last iteration for this cell (x) is I3 = I3 (.11) + Q3 (.06) =.17. I use Q3 =.06 because (1-1=0 count or position), Q3 at position 0=.06. When For Count ends, we have I3 =.17 at the X position in column AG. I would like the next cell down calculated for I3 and so on until the whole data base is calculated for I3. I hope that clears the ambiguity. Let me know if I can clarify further. Thanks for making me think more about what I want!! It seems to be an essential prerequisite to programming. Thanks again for your time, LenS LenS wrote: JL I'm sorry for the confusion, I should take more time to detail the problem. As for the value in AH, in my original communication: There are no blanks in this column and how this value is used is as a counting value (as per the Easy language code : For Count= 0 to Int(SP/2)-1). If AH or SP/2 is 4, then it steps through and adds the Q3 value to I3 which is initially =0. I think that's what the Easy Lang. code is saying but I'm not sure. When this counter SP/2 is done, 4 in this part, I will have obtained I3 for the current cell in AG. I then want the routine to drop to the next cell in AG and begin the process again with a new counter (SP/2 is 3 in the next case) and calculate I3 for that cell. So column AH determines how many Q3's are involved at any one run. I apologize again for not being clear. I'm sure you run into alot of that programmer vs non-programmer. I really appreciate the time you put into this, thanks. Keep in mind that column AF and AH have no blanks except at the beginning and end of the data. LenS JLatham wrote: I'll try to help some, but I must admit to being confused by how you want the value in AH to be used to figure out which values of Q3 (In col AF) to add together to get the value for I3 in column AG. So the code may not be doing that properly. First the code, then some explanation. Sub CalculateI3() Dim I3value As Single Dim LastRowOfData as Long Dim LC as Integer ' Loop Counter 'find last row with data in column AH LastRowOfData = Range("AH" & Rows.Count).End(xlUp).Row 'go to first possible data entry in AH 'assumes row 1 has header text Range("AH2").Select 'work down thru all cells to last row used Do While ActiveCell.Row <= LastRowOfData 'assumes if cell in AH is not empty, it is number If Not(IsEmpty(ActiveCell)) Then 'I'm a little confused here, so may not be right For LC = 0 to Int(ActiveCell.Value/2)-1 ActiveCell.Offset(0,-1) = _ ActiveCell.Offset(0, -1) + _ ActiveCell.Offset(-LC,-2) Next ' end of LC loop End If ' test for empty cells 'move to next Row ActiveCell.Offset(1, 0).Activate Loop ' down thru rows End Sub The 'Dim' statements declare a variable for use later, simply reserving room for it to be used. If your code module includes the declaration Option Explicit at the beginning of it, then Dim or Const declarations are required. I recommend it because of several reasons that I won't go into here (end result: better code less prone to failure). Because we are going to be doing the math based on the contents of cells, we don't need variables to hold interim results. But we do need to know how far down the sheet to work at getting information and we need to know how many values of Q3 to add together to get the I3 values. LastRowOfData is set up as type Long so it can hold very large integer numbers, since you may have thousands of rows of data to work through. LC, to be used as a loop counter, is set up as Integer to hold smaller integer numbers, although to be on the safe side it possibly should be set up as type Long also. The first executable line of code looks up from the bottom of column AH until it finds some entry in that column. That tells us how far down the sheet we have to work in examining the information to be used. We save that row number for reference in LastRowOfData. We get into a Do While loop that is simply going to work down the SP2 column row by row looking for numbers until it gets past the LastRowOfData on the sheet. the If Not(IsEmpty()) statement is used to test if there's something in a cell in the SP2 column (AH) and if there is, an assumption that it is a number is made and we then calculate the I3 value. A little about the ActiveCell.Offset() instructions. Offset() takes two arguments, first the number of rows to offset, where negative numbers are "up" the sheet and positive numbers are on "down" the sheet, and zero is same row the active cell is on. Second argument is the number of columns to offset from the location; negative numbers are to the left of the reference cell, positive numbers are to the right of it. Zero is in same column. So an .Offset(0, 0) is actually no offset at all, and would refer to the reference location itself. The LC loop: is based on the value in SP2 (column AH) at the current time. The way this loop is set up it will always add at least one number to get the I3 value - the value for Q3 on the same row. The math is actually done as a single formula, the " _" at the end of a line tells VB that the line continues on the next line also. ActiveCell.Offset(0,-1) will always refer to the cell in column AG (I3) for the current cell in AH (SP2) being examined. This formula literally says "take the value in the cell in column AG and replace that value with the value calculated by taking its current contents and adding another value to it". That "another value" is obtained by using the LC value as an offset pointer over into column AF (Q3) as the row pointer. We put a - symbol in front of the reference to LC in the Offset statement to tell it to look "up" the sheet rather than down farther on the sheet. So when SP2 is 4, we would add 2 values together to come up with I3: 4/2 - 1 = 1, but our loop goes from 0 to (SP/2)-1, so we will be using offset values of 0, and 1, meaning we would add .04 and .06 together to come |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA: For Count, when count changes from cell to cell
One thing more to try: Rename the copy of the file you previously downloaded
then use the same link to download it again. I have changed the way I saved it - it was previously saved as Excel 2003 format only, I resaved and uploaded it in compatible with Excel 97...Excel 2003. Perhaps that is the problem with the Gray macro button - version conflict? "LenS" wrote: Happy New Year, The Function and Macro codes worked exactly like I wanted in the spreadsheet I down-loaded from your link. I was unable to get the code into my Excel though. The macro option was blanked out on your toolbar. I don't understand why this is? Can you help? If I can get this and duplicate were home free. Thanks, Len LenS wrote: Hello JL, Thanks for the code and your time. I have a question about UDF. 1) Can I step into a UDF and watch the process unfold in the code? I can not figure out why I keep getting Value# or Name# when I run the UDF. Here's the code below I entered into VB: Function ComputeI3(mylocation As Range) As Single Application.Volatile Dim LC As Integer If IsEmpty(mylocation.Offset(0, 1)) Then Exit Function ElseIf mylocation.Offset(0, 1) < 1 Then Exit Function End If 'initialize ComputeI3 = 0 ActiveCell.Offset(0, -1) = 0 For LC = 0 To mylocation.Offset(0, 1) - 1 ComputeI3 = ComputeI3 + mylocation.Offset(-LC, -1) Next End Function Thanks, LenS JLatham wrote: The code works perfectly - except there seems to be a bug in it! Kind of. If you have previously calculated the I3 values using it, then add more data down the sheet and run the macro to get the I3 values for the new data, then the previously calculated values become incorrect because they don't start out as zero, they start at whatever they were calculated to be the first time out. So, if you run it once, you get .17 for an I3 value, run it again, that goes to .34, run it again and that goes to .51, etc. This can be fixed easily by putting this line of code just ahead of the For LC = instruction: ActiveCell=0 to clear out any previous results. With that in mind, I've rewritten it as a User Defined Function (UDF). This means that you can refer to it in a cell just like a built-in Excel function. This method will do away with having to call the macro to calculate the I3 values and will calculate them on the fly. Replace the previous code with this code: Function ComputeI3(myLocation As Range) As Single Application.Volatile Dim LC As Integer If IsEmpty(myLocation.Offset(0, 1)) Then Exit Function ElseIf myLocation.Offset(0, 1) < 1 Then Exit Function End If 'initialize ComputeI3 = 0 For LC = 0 To myLocation.Offset(0, 1) - 1 ComputeI3 = ComputeI3 + myLocation.Offset(-LC, -1) Next End Function Then in your I3 entries in column AG, put formula like this, this example would go into AG2: =ComputeI3(AG2) You can then fill the formula on down the sheet with the AG2 automatically changing to AG3, AG4, etc as it is filled, and it will work for you. This also relieves you from having to have these calculated from columns AF, AG and AH - the function is always relative to the current cell no matter what column it is in, so it will use the value one column to the right as the PS/2 value, and always use the values from the column immediately to it's left to get the Q3 values from. The 'Application.Volatile' statement in it makes it update with any change made on the sheet, so if you change a PS/2 or even a Q3 value, I3 will be recalculated. If the needed PS/2 value has not been entered yet, or if a value of less than one (which would cause the loop to try to go from zero to a negative value, which it cannot do), then the value 0 is returned as the I3 value. "LenS" wrote: JL Thanks LenS JLatham wrote: That's quite true - one of the first things I was taught in a rather rigorous programming school was to "define the problem". Anyhow, the initial code will work as you want with one minor change: change For LC = 0 to Int(ActiveCell.Value/2)-1 to read For LC = 0 to ActiveCell.Value-1 and that will do it. I think you can probably remove the "I'm a little confused..." comment also, if you want to <g. I'll make similar change here and do a bit of testing, but I believe we're home now. If you don't hear back from me, you should be good to go. "LenS" wrote: JL, SP/2 is already divided by 2 and its integer taken and used in the count (your correct, SP/2 is a title, the 4 is the count value in search of x, 3 is the count value in search of x1 etc etc). As far as what values of Q3 are used I'm speculating as to what the Easy Language calls for in its For Count statement: For Count= 0 to Int(SP/2)-1 I think I need 0 to 4-1 =3; 0 to 3-1=2; 0 to 2-1=1 and 0 to 1-1=0. I think the author of this procedure intends for us to use all 4 positions or counts of Q3: Position (count) 0 where Q3=.06, Position (count) 1 where Q3=.04, Position (count) 2 where Q3=.05, Position (count) 3 where Q3=.02. Position 0 always refers to current days data, position 1 refers to the previous days data and so on. If I3=0 at the beginning of the procedure, then after 1 iteration I3 = I3 (0) + Q3 (.02) =.02. I use Q3 =.02 because (4-1 = 3 count or position), Q3 at position 3 = .02. The next iteration I3 = I3 (.02) + Q3 (.05) =.07. I use Q3 =.05 because (3-1 = 2 count or position), Q3 at position 2 =.05. The third iteration I3 = I3 (.07) + Q3 (.04)=.11. I use Q3=.04 because (2-1 =1 count or position), Q3 at position 1 =.04 The last iteration for this cell (x) is I3 = I3 (.11) + Q3 (.06) =.17. I use Q3 =.06 because (1-1=0 count or position), Q3 at position 0=.06. When For Count ends, we have I3 =.17 at the X position in column AG. I would like the next cell down calculated for I3 and so on until the whole data base is calculated for I3. I hope that clears the ambiguity. Let me know if I can clarify further. Thanks for making me think more about what I want!! It seems to be an essential prerequisite to programming. Thanks again for your time, LenS LenS wrote: JL I'm sorry for the confusion, I should take more time to detail the problem. As for the value in AH, in my original communication: There are no blanks in this column and how this value is used is as a counting value (as per the Easy language code : For Count= 0 to Int(SP/2)-1). If AH or SP/2 is 4, then it steps through and adds the Q3 value to I3 which is initially =0. I think that's what the Easy Lang. code is saying but I'm not sure. When this counter SP/2 is done, 4 in this part, I will have obtained I3 for the current cell in AG. I then want the routine to drop to the next cell in AG and begin the process again with a new counter (SP/2 is 3 in the next case) and calculate I3 for that cell. So column AH determines how many Q3's are involved at any one run. I apologize again for not being clear. I'm sure you run into alot of that programmer vs non-programmer. I really appreciate the time you put into this, thanks. Keep in mind that column AF and AH have no blanks except at the beginning and end of the data. LenS JLatham wrote: I'll try to help some, but I must admit to being confused by how you want the value in AH to be used to figure out which values of Q3 (In col AF) to add together to get the value for I3 in column AG. So the code may not be doing that properly. First the code, then some explanation. Sub CalculateI3() Dim I3value As Single Dim LastRowOfData as Long Dim LC as Integer ' Loop Counter 'find last row with data in column AH LastRowOfData = Range("AH" & Rows.Count).End(xlUp).Row 'go to first possible data entry in AH 'assumes row 1 has header text Range("AH2").Select 'work down thru all cells to last row used Do While ActiveCell.Row <= LastRowOfData 'assumes if cell in AH is not empty, it is number If Not(IsEmpty(ActiveCell)) Then 'I'm a little confused here, so may not be right For LC = 0 to Int(ActiveCell.Value/2)-1 ActiveCell.Offset(0,-1) = _ ActiveCell.Offset(0, -1) + _ ActiveCell.Offset(-LC,-2) Next ' end of LC loop End If ' test for empty cells 'move to next Row ActiveCell.Offset(1, 0).Activate Loop ' down thru rows End Sub The 'Dim' statements declare a variable for use later, simply reserving room for it to be used. If your code module includes the declaration Option Explicit at the beginning of it, then Dim or Const declarations are required. I recommend it because of several reasons that I won't go into here (end result: better code less prone to failure). Because we are going to be doing the math based on the contents of cells, we don't need variables to hold interim results. But we do need to know how far down the sheet to work at getting information and we need to know how many values of Q3 to add together to get the I3 values. LastRowOfData is set up as type Long so it can hold very large integer numbers, since you may have thousands of rows of data to work through. LC, to be used as a loop counter, is set up as Integer to hold smaller integer numbers, although to be on the safe side it possibly should be set up as type Long also. The first executable line of code looks up from the bottom of column AH until it finds some entry in that column. That tells us how far down the sheet we have to work in examining the information to be used. We save that row number for reference in LastRowOfData. We get into a Do While loop that is simply going to work down the SP2 column row by row looking for numbers until it gets past the LastRowOfData on the sheet. the If Not(IsEmpty()) statement is used to test if there's something in a cell in the SP2 column (AH) and if there is, an assumption that it is a number is made and we then calculate the I3 value. A little about the ActiveCell.Offset() instructions. Offset() takes two arguments, first the number of rows to offset, where negative numbers are "up" the sheet and positive numbers are on "down" the sheet, and zero is same row the active cell is on. Second argument is the number of columns to offset from the location; negative numbers are to the left of the reference cell, positive numbers are to the right of it. Zero is in same column. So an .Offset(0, 0) is actually no offset at all, and would refer to the reference location itself. The LC loop: is based on the value in SP2 (column AH) at the current time. The way this loop is set up it will always add at least one number to get the I3 value - the value for Q3 on the same row. The math is actually done as a single formula, the " _" at the end of a line tells VB that the line continues on the next line also. ActiveCell.Offset(0,-1) will always refer to the cell in column AG (I3) for the current cell in AH (SP2) being examined. This formula literally says "take the value in the cell in column AG and replace that value with the value calculated by taking its current contents and adding another value to it". That "another value" is obtained by using the LC value as an offset pointer over into column AF (Q3) as the row pointer. We put a - symbol in front of the reference to LC in the Offset statement to tell it to look "up" the sheet rather than down farther on the sheet. So when SP2 is 4, we would add 2 values together to come up with I3: 4/2 - 1 = 1, but our loop goes from 0 to (SP/2)-1, so we will be using offset values of 0, and 1, meaning we would add .04 and .06 together to come |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA: For Count, when count changes from cell to cell
JL,
Good news!!!!! I tried the Sub() Macro that you gave me in post #14 but changed the starting point for the routine to where my data is really located and it WORKED!! It is a thing of beauty. Thanks alot. I really appreciate the extra 110% effort. You really throw yourself into these things and we're the better for it. I'll also see if I can incorporate the function that you wrote for my problem and also I want to check out the down-load problem that I have with Excel 2000 (Win98 SE). Thanks again LenS JLatham wrote: You've actually got me stumped on that one! I don't recall anyone ever reporting Macro being unavailable. Probably some simple setting somewhere, but I just cannot think of where it would be. I changed my Macro Security to highest level and even then the options were still available. Quickest answer may be to post this as a new question, and someone else who's encountered the problem should answer quickly. I'd ask in the 'Programming' section - I would think that's where most familiarity would be. Tell them what version of Excel you have, and what version of Windows. Tell them the problems you're having: #1 - you used this code and got #Value and #Name errors in your own workbook (that kind of hints at VB problems) #2 - you got copy of working code in a workbook, but Macro button is grayed out (tell which button in the sequence Tools | Macro or Tools | Macro | Macros). My file was created in Excel 2003, and there is no special protection applied to either the VBA project or to the sheets or workbook. It's pretty plain-jane file. I'll leave the file out and available if someone else needs/wants to download and look at it. #3 - do some more quick testing and see what else works/doesn't work - -- can you record a macro and then run it later? #4 - can you use [Alt]+[F11] to get into the VB Editor? Here is the code copied right from the workbook you downloaded. It goes into a regular VBA code module. Maybe you can copy and paste into your own workbook again? Sub CalculateI3() Dim I3value As Single Dim LastRowOfData As Long Dim LC As Integer ' Loop Counter 'find last row with data in column AH LastRowOfData = Range("AH" & Rows.Count).End(xlUp).Row 'go to first possible data entry in AH 'assumes row 1 has header text Range("AH2").Select 'work down thru all cells to last row used Do While ActiveCell.Row <= LastRowOfData 'assumes if cell in AH is not empty, it is number If Not (IsEmpty(ActiveCell)) Then ActiveCell.Offset(0, -1) = 0 ' reset!! For LC = 0 To Int(ActiveCell.Value) - 1 ActiveCell.Offset(0, -1) = _ ActiveCell.Offset(0, -1) + _ ActiveCell.Offset(-LC, -2) Next ' end of LC loop End If ' test for empty cells 'move to next Row ActiveCell.Offset(1, 0).Activate Loop ' down thru rows End Sub Function ComputeI3(myLocation As Range) As Single Application.Volatile Dim LC As Integer If IsEmpty(myLocation.Offset(0, 1)) Then Exit Function ElseIf myLocation.Offset(0, 1) < 1 Then Exit Function End If 'initialize ComputeI3 = 0 For LC = 0 To myLocation.Offset(0, 1) - 1 ComputeI3 = ComputeI3 + myLocation.Offset(-LC, -1) Next End Function "LenS" wrote: Happy New Year, The Function and Macro codes worked exactly like I wanted in the spreadsheet I down-loaded from your link. I was unable to get the code into my Excel though. The macro option was blanked out on your toolbar. I don't understand why this is? Can you help? If I can get this and duplicate were home free. Thanks, Len LenS wrote: Hello JL, Thanks for the code and your time. I have a question about UDF. 1) Can I step into a UDF and watch the process unfold in the code? I can not figure out why I keep getting Value# or Name# when I run the UDF. Here's the code below I entered into VB: Function ComputeI3(mylocation As Range) As Single Application.Volatile Dim LC As Integer If IsEmpty(mylocation.Offset(0, 1)) Then Exit Function ElseIf mylocation.Offset(0, 1) < 1 Then Exit Function End If 'initialize ComputeI3 = 0 ActiveCell.Offset(0, -1) = 0 For LC = 0 To mylocation.Offset(0, 1) - 1 ComputeI3 = ComputeI3 + mylocation.Offset(-LC, -1) Next End Function Thanks, LenS JLatham wrote: The code works perfectly - except there seems to be a bug in it! Kind of. If you have previously calculated the I3 values using it, then add more data down the sheet and run the macro to get the I3 values for the new data, then the previously calculated values become incorrect because they don't start out as zero, they start at whatever they were calculated to be the first time out. So, if you run it once, you get .17 for an I3 value, run it again, that goes to .34, run it again and that goes to .51, etc. This can be fixed easily by putting this line of code just ahead of the For LC = instruction: ActiveCell=0 to clear out any previous results. With that in mind, I've rewritten it as a User Defined Function (UDF). This means that you can refer to it in a cell just like a built-in Excel function. This method will do away with having to call the macro to calculate the I3 values and will calculate them on the fly. Replace the previous code with this code: Function ComputeI3(myLocation As Range) As Single Application.Volatile Dim LC As Integer If IsEmpty(myLocation.Offset(0, 1)) Then Exit Function ElseIf myLocation.Offset(0, 1) < 1 Then Exit Function End If 'initialize ComputeI3 = 0 For LC = 0 To myLocation.Offset(0, 1) - 1 ComputeI3 = ComputeI3 + myLocation.Offset(-LC, -1) Next End Function Then in your I3 entries in column AG, put formula like this, this example would go into AG2: =ComputeI3(AG2) You can then fill the formula on down the sheet with the AG2 automatically changing to AG3, AG4, etc as it is filled, and it will work for you. This also relieves you from having to have these calculated from columns AF, AG and AH - the function is always relative to the current cell no matter what column it is in, so it will use the value one column to the right as the PS/2 value, and always use the values from the column immediately to it's left to get the Q3 values from. The 'Application.Volatile' statement in it makes it update with any change made on the sheet, so if you change a PS/2 or even a Q3 value, I3 will be recalculated. If the needed PS/2 value has not been entered yet, or if a value of less than one (which would cause the loop to try to go from zero to a negative value, which it cannot do), then the value 0 is returned as the I3 value. "LenS" wrote: JL Thanks LenS JLatham wrote: That's quite true - one of the first things I was taught in a rather rigorous programming school was to "define the problem". Anyhow, the initial code will work as you want with one minor change: change For LC = 0 to Int(ActiveCell.Value/2)-1 to read For LC = 0 to ActiveCell.Value-1 and that will do it. I think you can probably remove the "I'm a little confused..." comment also, if you want to <g. I'll make similar change here and do a bit of testing, but I believe we're home now. If you don't hear back from me, you should be good to go. "LenS" wrote: JL, SP/2 is already divided by 2 and its integer taken and used in the count (your correct, SP/2 is a title, the 4 is the count value in search of x, 3 is the count value in search of x1 etc etc). As far as what values of Q3 are used I'm speculating as to what the Easy Language calls for in its For Count statement: For Count= 0 to Int(SP/2)-1 I think I need 0 to 4-1 =3; 0 to 3-1=2; 0 to 2-1=1 and 0 to 1-1=0. I think the author of this procedure intends for us to use all 4 positions or counts of Q3: Position (count) 0 where Q3=.06, Position (count) 1 where Q3=.04, Position (count) 2 where Q3=.05, Position (count) 3 where Q3=.02. Position 0 always refers to current days data, position 1 refers to the previous days data and so on. If I3=0 at the beginning of the procedure, then after 1 iteration I3 = I3 (0) + Q3 (.02) =.02. I use Q3 =.02 because (4-1 = 3 count or position), Q3 at position 3 = .02. The next iteration I3 = I3 (.02) + Q3 (.05) =.07. I use Q3 =.05 because (3-1 = 2 count or position), Q3 at position 2 =.05. The third iteration I3 = I3 (.07) + Q3 (.04)=.11. I use Q3=.04 because (2-1 =1 count or position), Q3 at position 1 =.04 The last iteration for this cell (x) is I3 = I3 (.11) + Q3 (.06) =.17. I use Q3 =.06 because (1-1=0 count or position), Q3 at position 0=.06. When For Count ends, we have I3 =.17 at the X position in column AG. I would like the next cell down calculated for I3 and so on until the whole data base is calculated for I3. I hope that clears the ambiguity. Let me know if I can clarify further. Thanks for making me think more about what I want!! It seems to be an essential prerequisite to programming. Thanks again for your time, LenS LenS wrote: JL I'm sorry for the confusion, I should take more time to detail the problem. As for the value in AH, in my original communication: There are no blanks in this column and how this value is used is as a counting value (as per the Easy language code : For Count= 0 to Int(SP/2)-1). If AH or SP/2 is 4, then it steps through and adds the Q3 value to I3 which is initially =0. I think that's what the Easy Lang. code is saying but I'm not sure. When this counter SP/2 is done, 4 in this part, I will have obtained I3 for the current cell in AG. I then want the routine to drop to the next cell in AG and begin the process again with a new counter (SP/2 is 3 in the next case) and calculate I3 for that cell. So column AH determines how many Q3's are involved at any one run. I apologize again for not being clear. I'm sure you run into alot of that programmer vs non-programmer. I really appreciate the time you put into this, thanks. Keep in mind that column AF and AH have no blanks except at the beginning and end of the data. LenS JLatham wrote: I'll try to help some, but I must admit to being confused by how you want the value in AH to be used to figure out which values of Q3 (In col AF) to add together to get the value for I3 in column AG. So the code may not be doing that properly. First the code, then some explanation. Sub CalculateI3() Dim I3value As Single Dim LastRowOfData as Long Dim LC as Integer ' Loop Counter 'find last row with data in column AH LastRowOfData = Range("AH" & Rows.Count).End(xlUp).Row 'go to first possible data entry in AH 'assumes row 1 has header text Range("AH2").Select 'work down thru all cells to last row used Do While ActiveCell.Row <= LastRowOfData 'assumes if cell in AH is not empty, it is number If Not(IsEmpty(ActiveCell)) Then 'I'm a little confused here, so may not be right For LC = 0 to Int(ActiveCell.Value/2)-1 ActiveCell.Offset(0,-1) = _ ActiveCell.Offset(0, -1) + _ ActiveCell.Offset(-LC,-2) Next ' end of LC loop End If ' test for empty cells 'move to next Row ActiveCell.Offset(1, 0).Activate Loop ' down thru rows End Sub The 'Dim' statements declare a variable for use later, simply reserving room for it to be used. If your code module includes the declaration Option Explicit at the beginning of it, then Dim or Const declarations are required. I recommend it because of several reasons that I won't go into here (end result: better code less prone to failure). Because we are going to be doing the math based on the contents of cells, we don't need variables to hold interim results. But we do need to know how far down the sheet to work at getting information and we need to know how many values of Q3 to add together to get the I3 values. LastRowOfData is set up as type Long so it can hold very large integer numbers, since you may have thousands of rows of data to work through. LC, to be used as a loop counter, is set up as Integer to hold smaller integer numbers, although to be on the safe side it possibly should be set up as type Long also. The first executable line of code looks up from the bottom of column AH until it finds some entry in that column. That tells us how far down the sheet we have to work in examining the information to be used. We save that row number for reference in LastRowOfData. We get into a Do While loop that is simply going to work down the SP2 column row by row looking for numbers until it gets past the LastRowOfData on the sheet. the If Not(IsEmpty()) statement is used to test if there's something in a cell in the SP2 column (AH) and if there is, an assumption that it is a number is made and we then calculate the I3 value. A little about the ActiveCell.Offset() instructions. Offset() takes two arguments, first the number of rows to offset, where negative numbers are "up" the sheet and positive numbers are on "down" the sheet, and zero is same row the active cell is on. Second argument is the number of columns to offset from the location; negative numbers are to the left of the reference cell, positive numbers are to the right of it. Zero is in same column. So an .Offset(0, 0) is actually no offset at all, and would refer to the reference location itself. The LC loop: is based on the value in SP2 (column AH) at the current time. The way this loop is set up it will always add at least one number to get the I3 value - the value for Q3 on the same row. The math is actually done as a single formula, the " _" at the end of a line tells VB that the line continues on the next line also. ActiveCell.Offset(0,-1) will always refer to the cell in column AG (I3) for the current cell in AH (SP2) being examined. This formula literally says "take the value in the cell in column AG and replace that value with the value calculated by taking its current contents and adding another value to it". That "another value" is obtained by using the LC value as an offset pointer over into column AF (Q3) as the row pointer. We put a - symbol in front of the reference to LC in the Offset statement to tell it to look "up" the sheet rather than down farther on the sheet. So when SP2 is 4, we would add 2 values together to come up with I3: 4/2 - 1 = 1, but our loop goes from 0 to (SP/2)-1, so we will be using offset values of 0, and 1, meaning we would add .04 and .06 together to come |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA: For Count, when count changes from cell to cell
Knowing now that you're on Excel 2000, I can actually take the file and
process it in that version and put it back up on the website later if you want. I've got a virtual machine setup here with Office 2000 in it, although it's on Win XP, but that should not matter in this case. If you missed my almost-same-time post below earlier, I did recreate the file using a format that should be compatible with 2000 also. Yes, the Sub is dependent on where things are on the worksheet - that was one reason I created the UDF - it is independent of location, just requires that the 3 columns be arranged side-by-side with the call from the center column. Obviously that can be changed, but that's the way it's written at the moment. "LenS" wrote: JL, Good news!!!!! I tried the Sub() Macro that you gave me in post #14 but changed the starting point for the routine to where my data is really located and it WORKED!! It is a thing of beauty. Thanks alot. I really appreciate the extra 110% effort. You really throw yourself into these things and we're the better for it. I'll also see if I can incorporate the function that you wrote for my problem and also I want to check out the down-load problem that I have with Excel 2000 (Win98 SE). Thanks again LenS JLatham wrote: You've actually got me stumped on that one! I don't recall anyone ever reporting Macro being unavailable. Probably some simple setting somewhere, but I just cannot think of where it would be. I changed my Macro Security to highest level and even then the options were still available. Quickest answer may be to post this as a new question, and someone else who's encountered the problem should answer quickly. I'd ask in the 'Programming' section - I would think that's where most familiarity would be. Tell them what version of Excel you have, and what version of Windows. Tell them the problems you're having: #1 - you used this code and got #Value and #Name errors in your own workbook (that kind of hints at VB problems) #2 - you got copy of working code in a workbook, but Macro button is grayed out (tell which button in the sequence Tools | Macro or Tools | Macro | Macros). My file was created in Excel 2003, and there is no special protection applied to either the VBA project or to the sheets or workbook. It's pretty plain-jane file. I'll leave the file out and available if someone else needs/wants to download and look at it. #3 - do some more quick testing and see what else works/doesn't work - -- can you record a macro and then run it later? #4 - can you use [Alt]+[F11] to get into the VB Editor? Here is the code copied right from the workbook you downloaded. It goes into a regular VBA code module. Maybe you can copy and paste into your own workbook again? Sub CalculateI3() Dim I3value As Single Dim LastRowOfData As Long Dim LC As Integer ' Loop Counter 'find last row with data in column AH LastRowOfData = Range("AH" & Rows.Count).End(xlUp).Row 'go to first possible data entry in AH 'assumes row 1 has header text Range("AH2").Select 'work down thru all cells to last row used Do While ActiveCell.Row <= LastRowOfData 'assumes if cell in AH is not empty, it is number If Not (IsEmpty(ActiveCell)) Then ActiveCell.Offset(0, -1) = 0 ' reset!! For LC = 0 To Int(ActiveCell.Value) - 1 ActiveCell.Offset(0, -1) = _ ActiveCell.Offset(0, -1) + _ ActiveCell.Offset(-LC, -2) Next ' end of LC loop End If ' test for empty cells 'move to next Row ActiveCell.Offset(1, 0).Activate Loop ' down thru rows End Sub Function ComputeI3(myLocation As Range) As Single Application.Volatile Dim LC As Integer If IsEmpty(myLocation.Offset(0, 1)) Then Exit Function ElseIf myLocation.Offset(0, 1) < 1 Then Exit Function End If 'initialize ComputeI3 = 0 For LC = 0 To myLocation.Offset(0, 1) - 1 ComputeI3 = ComputeI3 + myLocation.Offset(-LC, -1) Next End Function "LenS" wrote: Happy New Year, The Function and Macro codes worked exactly like I wanted in the spreadsheet I down-loaded from your link. I was unable to get the code into my Excel though. The macro option was blanked out on your toolbar. I don't understand why this is? Can you help? If I can get this and duplicate were home free. Thanks, Len LenS wrote: Hello JL, Thanks for the code and your time. I have a question about UDF. 1) Can I step into a UDF and watch the process unfold in the code? I can not figure out why I keep getting Value# or Name# when I run the UDF. Here's the code below I entered into VB: Function ComputeI3(mylocation As Range) As Single Application.Volatile Dim LC As Integer If IsEmpty(mylocation.Offset(0, 1)) Then Exit Function ElseIf mylocation.Offset(0, 1) < 1 Then Exit Function End If 'initialize ComputeI3 = 0 ActiveCell.Offset(0, -1) = 0 For LC = 0 To mylocation.Offset(0, 1) - 1 ComputeI3 = ComputeI3 + mylocation.Offset(-LC, -1) Next End Function Thanks, LenS JLatham wrote: The code works perfectly - except there seems to be a bug in it! Kind of. If you have previously calculated the I3 values using it, then add more data down the sheet and run the macro to get the I3 values for the new data, then the previously calculated values become incorrect because they don't start out as zero, they start at whatever they were calculated to be the first time out. So, if you run it once, you get .17 for an I3 value, run it again, that goes to .34, run it again and that goes to .51, etc. This can be fixed easily by putting this line of code just ahead of the For LC = instruction: ActiveCell=0 to clear out any previous results. With that in mind, I've rewritten it as a User Defined Function (UDF). This means that you can refer to it in a cell just like a built-in Excel function. This method will do away with having to call the macro to calculate the I3 values and will calculate them on the fly. Replace the previous code with this code: Function ComputeI3(myLocation As Range) As Single Application.Volatile Dim LC As Integer If IsEmpty(myLocation.Offset(0, 1)) Then Exit Function ElseIf myLocation.Offset(0, 1) < 1 Then Exit Function End If 'initialize ComputeI3 = 0 For LC = 0 To myLocation.Offset(0, 1) - 1 ComputeI3 = ComputeI3 + myLocation.Offset(-LC, -1) Next End Function Then in your I3 entries in column AG, put formula like this, this example would go into AG2: =ComputeI3(AG2) You can then fill the formula on down the sheet with the AG2 automatically changing to AG3, AG4, etc as it is filled, and it will work for you. This also relieves you from having to have these calculated from columns AF, AG and AH - the function is always relative to the current cell no matter what column it is in, so it will use the value one column to the right as the PS/2 value, and always use the values from the column immediately to it's left to get the Q3 values from. The 'Application.Volatile' statement in it makes it update with any change made on the sheet, so if you change a PS/2 or even a Q3 value, I3 will be recalculated. If the needed PS/2 value has not been entered yet, or if a value of less than one (which would cause the loop to try to go from zero to a negative value, which it cannot do), then the value 0 is returned as the I3 value. "LenS" wrote: JL Thanks LenS JLatham wrote: That's quite true - one of the first things I was taught in a rather rigorous programming school was to "define the problem". Anyhow, the initial code will work as you want with one minor change: change For LC = 0 to Int(ActiveCell.Value/2)-1 to read For LC = 0 to ActiveCell.Value-1 and that will do it. I think you can probably remove the "I'm a little confused..." comment also, if you want to <g. I'll make similar change here and do a bit of testing, but I believe we're home now. If you don't hear back from me, you should be good to go. "LenS" wrote: JL, SP/2 is already divided by 2 and its integer taken and used in the count (your correct, SP/2 is a title, the 4 is the count value in search of x, 3 is the count value in search of x1 etc etc). As far as what values of Q3 are used I'm speculating as to what the Easy Language calls for in its For Count statement: For Count= 0 to Int(SP/2)-1 I think I need 0 to 4-1 =3; 0 to 3-1=2; 0 to 2-1=1 and 0 to 1-1=0. I think the author of this procedure intends for us to use all 4 positions or counts of Q3: Position (count) 0 where Q3=.06, Position (count) 1 where Q3=.04, Position (count) 2 where Q3=.05, Position (count) 3 where Q3=.02. Position 0 always refers to current days data, position 1 refers to the previous days data and so on. If I3=0 at the beginning of the procedure, then after 1 iteration I3 = I3 (0) + Q3 (.02) =.02. I use Q3 =.02 because (4-1 = 3 count or position), Q3 at position 3 = .02. The next iteration I3 = I3 (.02) + Q3 (.05) =.07. I use Q3 =.05 because (3-1 = 2 count or position), Q3 at position 2 =.05. The third iteration I3 = I3 (.07) + Q3 (.04)=.11. I use Q3=.04 because (2-1 =1 count or position), Q3 at position 1 =.04 The last iteration for this cell (x) is I3 = I3 (.11) + Q3 (.06) =.17. I use Q3 =.06 because (1-1=0 count or position), Q3 at position 0=.06. When For Count ends, we have I3 =.17 at the X position in column AG. I would like the next cell down calculated for I3 and so on until the whole data base is calculated for I3. I hope that clears the ambiguity. Let me know if I can clarify further. Thanks for making me think more about what I want!! It seems to be an essential prerequisite to programming. Thanks again for your time, LenS LenS wrote: JL I'm sorry for the confusion, I should take more time to detail the problem. As for the value in AH, in my original communication: There are no blanks in this column and how this value is used is as a counting value (as per the Easy language code : For Count= 0 to Int(SP/2)-1). If AH or SP/2 is 4, then it steps through and adds the Q3 value to I3 which is initially =0. I think that's what the Easy Lang. code is saying but I'm not sure. When this counter SP/2 is done, 4 in this part, I will have obtained I3 for the current cell in AG. I then want the routine to drop to the next cell in AG and begin the process again with a new counter (SP/2 is 3 in the next case) and calculate I3 for that cell. So column AH determines how many Q3's are involved at any one run. I apologize again for not being clear. I'm sure you run into alot of that programmer vs non-programmer. I really appreciate the time you put into this, thanks. Keep in mind that column AF and AH have no blanks except at the beginning and end of the data. LenS |
|
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA: For Count, when count changes from cell to cell
JL,
I tried Alt F11 on your down load and got the VB Editor and copied the function to my Spreadsheet and it WORKED!!! Thanks again LenS JLatham (removethis) wrote: One thing more to try: Rename the copy of the file you previously downloaded then use the same link to download it again. I have changed the way I saved it - it was previously saved as Excel 2003 format only, I resaved and uploaded it in compatible with Excel 97...Excel 2003. Perhaps that is the problem with the Gray macro button - version conflict? "LenS" wrote: Happy New Year, The Function and Macro codes worked exactly like I wanted in the spreadsheet I down-loaded from your link. I was unable to get the code into my Excel though. The macro option was blanked out on your toolbar. I don't understand why this is? Can you help? If I can get this and duplicate were home free. Thanks, Len LenS wrote: Hello JL, Thanks for the code and your time. I have a question about UDF. 1) Can I step into a UDF and watch the process unfold in the code? I can not figure out why I keep getting Value# or Name# when I run the UDF. Here's the code below I entered into VB: Function ComputeI3(mylocation As Range) As Single Application.Volatile Dim LC As Integer If IsEmpty(mylocation.Offset(0, 1)) Then Exit Function ElseIf mylocation.Offset(0, 1) < 1 Then Exit Function End If 'initialize ComputeI3 = 0 ActiveCell.Offset(0, -1) = 0 For LC = 0 To mylocation.Offset(0, 1) - 1 ComputeI3 = ComputeI3 + mylocation.Offset(-LC, -1) Next End Function Thanks, LenS JLatham wrote: The code works perfectly - except there seems to be a bug in it! Kind of. If you have previously calculated the I3 values using it, then add more data down the sheet and run the macro to get the I3 values for the new data, then the previously calculated values become incorrect because they don't start out as zero, they start at whatever they were calculated to be the first time out. So, if you run it once, you get .17 for an I3 value, run it again, that goes to .34, run it again and that goes to .51, etc. This can be fixed easily by putting this line of code just ahead of the For LC = instruction: ActiveCell=0 to clear out any previous results. With that in mind, I've rewritten it as a User Defined Function (UDF). This means that you can refer to it in a cell just like a built-in Excel function. This method will do away with having to call the macro to calculate the I3 values and will calculate them on the fly. Replace the previous code with this code: Function ComputeI3(myLocation As Range) As Single Application.Volatile Dim LC As Integer If IsEmpty(myLocation.Offset(0, 1)) Then Exit Function ElseIf myLocation.Offset(0, 1) < 1 Then Exit Function End If 'initialize ComputeI3 = 0 For LC = 0 To myLocation.Offset(0, 1) - 1 ComputeI3 = ComputeI3 + myLocation.Offset(-LC, -1) Next End Function Then in your I3 entries in column AG, put formula like this, this example would go into AG2: =ComputeI3(AG2) You can then fill the formula on down the sheet with the AG2 automatically changing to AG3, AG4, etc as it is filled, and it will work for you. This also relieves you from having to have these calculated from columns AF, AG and AH - the function is always relative to the current cell no matter what column it is in, so it will use the value one column to the right as the PS/2 value, and always use the values from the column immediately to it's left to get the Q3 values from. The 'Application.Volatile' statement in it makes it update with any change made on the sheet, so if you change a PS/2 or even a Q3 value, I3 will be recalculated. If the needed PS/2 value has not been entered yet, or if a value of less than one (which would cause the loop to try to go from zero to a negative value, which it cannot do), then the value 0 is returned as the I3 value. "LenS" wrote: JL Thanks LenS JLatham wrote: That's quite true - one of the first things I was taught in a rather rigorous programming school was to "define the problem". Anyhow, the initial code will work as you want with one minor change: change For LC = 0 to Int(ActiveCell.Value/2)-1 to read For LC = 0 to ActiveCell.Value-1 and that will do it. I think you can probably remove the "I'm a little confused..." comment also, if you want to <g. I'll make similar change here and do a bit of testing, but I believe we're home now. If you don't hear back from me, you should be good to go. "LenS" wrote: JL, SP/2 is already divided by 2 and its integer taken and used in the count (your correct, SP/2 is a title, the 4 is the count value in search of x, 3 is the count value in search of x1 etc etc). As far as what values of Q3 are used I'm speculating as to what the Easy Language calls for in its For Count statement: For Count= 0 to Int(SP/2)-1 I think I need 0 to 4-1 =3; 0 to 3-1=2; 0 to 2-1=1 and 0 to 1-1=0. I think the author of this procedure intends for us to use all 4 positions or counts of Q3: Position (count) 0 where Q3=.06, Position (count) 1 where Q3=.04, Position (count) 2 where Q3=.05, Position (count) 3 where Q3=.02. Position 0 always refers to current days data, position 1 refers to the previous days data and so on. If I3=0 at the beginning of the procedure, then after 1 iteration I3 = I3 (0) + Q3 (.02) =.02. I use Q3 =.02 because (4-1 = 3 count or position), Q3 at position 3 = .02. The next iteration I3 = I3 (.02) + Q3 (.05) =.07. I use Q3 =.05 because (3-1 = 2 count or position), Q3 at position 2 =.05. The third iteration I3 = I3 (.07) + Q3 (.04)=.11. I use Q3=.04 because (2-1 =1 count or position), Q3 at position 1 =.04 The last iteration for this cell (x) is I3 = I3 (.11) + Q3 (.06) =.17. I use Q3 =.06 because (1-1=0 count or position), Q3 at position 0=.06. When For Count ends, we have I3 =.17 at the X position in column AG. I would like the next cell down calculated for I3 and so on until the whole data base is calculated for I3. I hope that clears the ambiguity. Let me know if I can clarify further. Thanks for making me think more about what I want!! It seems to be an essential prerequisite to programming. Thanks again for your time, LenS LenS wrote: JL I'm sorry for the confusion, I should take more time to detail the problem. As for the value in AH, in my original communication: There are no blanks in this column and how this value is used is as a counting value (as per the Easy language code : For Count= 0 to Int(SP/2)-1). If AH or SP/2 is 4, then it steps through and adds the Q3 value to I3 which is initially =0. I think that's what the Easy Lang. code is saying but I'm not sure. When this counter SP/2 is done, 4 in this part, I will have obtained I3 for the current cell in AG. I then want the routine to drop to the next cell in AG and begin the process again with a new counter (SP/2 is 3 in the next case) and calculate I3 for that cell. So column AH determines how many Q3's are involved at any one run. I apologize again for not being clear. I'm sure you run into alot of that programmer vs non-programmer. I really appreciate the time you put into this, thanks. Keep in mind that column AF and AH have no blanks except at the beginning and end of the data. LenS JLatham wrote: I'll try to help some, but I must admit to being confused by how you want the value in AH to be used to figure out which values of Q3 (In col AF) to add together to get the value for I3 in column AG. So the code may not be doing that properly. First the code, then some explanation. Sub CalculateI3() Dim I3value As Single Dim LastRowOfData as Long Dim LC as Integer ' Loop Counter 'find last row with data in column AH LastRowOfData = Range("AH" & Rows.Count).End(xlUp).Row 'go to first possible data entry in AH 'assumes row 1 has header text Range("AH2").Select 'work down thru all cells to last row used Do While ActiveCell.Row <= LastRowOfData 'assumes if cell in AH is not empty, it is number If Not(IsEmpty(ActiveCell)) Then 'I'm a little confused here, so may not be right For LC = 0 to Int(ActiveCell.Value/2)-1 ActiveCell.Offset(0,-1) = _ ActiveCell.Offset(0, -1) + _ ActiveCell.Offset(-LC,-2) Next ' end of LC loop End If ' test for empty cells 'move to next Row ActiveCell.Offset(1, 0).Activate Loop ' down thru rows End Sub The 'Dim' statements declare a variable for use later, simply reserving room for it to be used. If your code module includes the declaration Option Explicit at the beginning of it, then Dim or Const declarations are required. I recommend it because of several reasons that I won't go into here (end result: better code less prone to failure). Because we are going to be doing the math based on the contents of cells, we don't need variables to hold interim results. But we do need to know how far down the sheet to work at getting information and we need to know how many values of Q3 to add together to get the I3 values. LastRowOfData is set up as type Long so it can hold very large integer numbers, since you may have thousands of rows of data to work through. LC, to be used as a loop counter, is set up as Integer to hold smaller integer numbers, although to be on the safe side it possibly should be set up as type Long also. The first executable line of code looks up from the bottom of column AH until it finds some entry in that column. That tells us how far down the sheet we have to work in examining the information to be used. We save that row number for reference in LastRowOfData. We get into a Do While loop that is simply going to work down the SP2 column row by row looking for numbers until it gets past the LastRowOfData on the sheet. the If Not(IsEmpty()) statement is used to test if there's something in a cell in the SP2 column (AH) and if there is, an assumption that it is a number is made and we then calculate the I3 value. A little about the ActiveCell.Offset() instructions. Offset() takes two arguments, first the number of rows to offset, where negative numbers are "up" the sheet and positive numbers are on "down" the sheet, and zero is same row the active cell is on. Second argument is the number of columns to offset from the location; negative numbers are to the left of the reference cell, positive numbers are to the right of it. Zero is in same column. So an .Offset(0, 0) is actually no offset at all, and would refer to the reference location itself. The LC loop: is based on the value in SP2 (column AH) at the current time. The way this loop is set up it will always add at least one number to get the I3 value - the value for Q3 on the same row. The math is actually done as a single formula, the " _" at the end of a line tells VB that the line continues on the next line also. ActiveCell.Offset(0,-1) will always refer to the cell in column AG (I3) for the current cell in AH (SP2) being examined. This formula literally says "take the value in the cell in column AG and replace that value with the value calculated by taking its current contents and adding another value to it". That "another value" is obtained by using the LC value as an offset pointer over into column AF (Q3) as the row pointer. We put a - symbol in front of the reference to LC in the Offset statement to tell it to look "up" the sheet rather than down farther on the sheet. So when SP2 is 4, we would add 2 values together to come up with I3: 4/2 - 1 = 1, but our loop goes from 0 to (SP/2)-1, so we will be using offset values of 0, and 1, meaning we would add .04 and .06 together to come |
|
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VBA: For Count, when count changes from cell to cell
That's good to hear. The UDF should be much more convenient and 'sure' to
use since it will do its calculations in real-time without you having to run a macro to update values. "LenS" wrote: JL, I tried Alt F11 on your down load and got the VB Editor and copied the function to my Spreadsheet and it WORKED!!! Thanks again LenS JLatham (removethis) wrote: One thing more to try: Rename the copy of the file you previously downloaded then use the same link to download it again. I have changed the way I saved it - it was previously saved as Excel 2003 format only, I resaved and uploaded it in compatible with Excel 97...Excel 2003. Perhaps that is the problem with the Gray macro button - version conflict? "LenS" wrote: Happy New Year, The Function and Macro codes worked exactly like I wanted in the spreadsheet I down-loaded from your link. I was unable to get the code into my Excel though. The macro option was blanked out on your toolbar. I don't understand why this is? Can you help? If I can get this and duplicate were home free. Thanks, Len LenS wrote: Hello JL, Thanks for the code and your time. I have a question about UDF. 1) Can I step into a UDF and watch the process unfold in the code? I can not figure out why I keep getting Value# or Name# when I run the UDF. Here's the code below I entered into VB: Function ComputeI3(mylocation As Range) As Single Application.Volatile Dim LC As Integer If IsEmpty(mylocation.Offset(0, 1)) Then Exit Function ElseIf mylocation.Offset(0, 1) < 1 Then Exit Function End If 'initialize ComputeI3 = 0 ActiveCell.Offset(0, -1) = 0 For LC = 0 To mylocation.Offset(0, 1) - 1 ComputeI3 = ComputeI3 + mylocation.Offset(-LC, -1) Next End Function Thanks, LenS JLatham wrote: The code works perfectly - except there seems to be a bug in it! Kind of. If you have previously calculated the I3 values using it, then add more data down the sheet and run the macro to get the I3 values for the new data, then the previously calculated values become incorrect because they don't start out as zero, they start at whatever they were calculated to be the first time out. So, if you run it once, you get .17 for an I3 value, run it again, that goes to .34, run it again and that goes to .51, etc. This can be fixed easily by putting this line of code just ahead of the For LC = instruction: ActiveCell=0 to clear out any previous results. With that in mind, I've rewritten it as a User Defined Function (UDF). This means that you can refer to it in a cell just like a built-in Excel function. This method will do away with having to call the macro to calculate the I3 values and will calculate them on the fly. Replace the previous code with this code: Function ComputeI3(myLocation As Range) As Single Application.Volatile Dim LC As Integer If IsEmpty(myLocation.Offset(0, 1)) Then Exit Function ElseIf myLocation.Offset(0, 1) < 1 Then Exit Function End If 'initialize ComputeI3 = 0 For LC = 0 To myLocation.Offset(0, 1) - 1 ComputeI3 = ComputeI3 + myLocation.Offset(-LC, -1) Next End Function Then in your I3 entries in column AG, put formula like this, this example would go into AG2: =ComputeI3(AG2) You can then fill the formula on down the sheet with the AG2 automatically changing to AG3, AG4, etc as it is filled, and it will work for you. This also relieves you from having to have these calculated from columns AF, AG and AH - the function is always relative to the current cell no matter what column it is in, so it will use the value one column to the right as the PS/2 value, and always use the values from the column immediately to it's left to get the Q3 values from. The 'Application.Volatile' statement in it makes it update with any change made on the sheet, so if you change a PS/2 or even a Q3 value, I3 will be recalculated. If the needed PS/2 value has not been entered yet, or if a value of less than one (which would cause the loop to try to go from zero to a negative value, which it cannot do), then the value 0 is returned as the I3 value. "LenS" wrote: JL Thanks LenS JLatham wrote: That's quite true - one of the first things I was taught in a rather rigorous programming school was to "define the problem". Anyhow, the initial code will work as you want with one minor change: change For LC = 0 to Int(ActiveCell.Value/2)-1 to read For LC = 0 to ActiveCell.Value-1 and that will do it. I think you can probably remove the "I'm a little confused..." comment also, if you want to <g. I'll make similar change here and do a bit of testing, but I believe we're home now. If you don't hear back from me, you should be good to go. "LenS" wrote: JL, SP/2 is already divided by 2 and its integer taken and used in the count (your correct, SP/2 is a title, the 4 is the count value in search of x, 3 is the count value in search of x1 etc etc). As far as what values of Q3 are used I'm speculating as to what the Easy Language calls for in its For Count statement: For Count= 0 to Int(SP/2)-1 I think I need 0 to 4-1 =3; 0 to 3-1=2; 0 to 2-1=1 and 0 to 1-1=0. I think the author of this procedure intends for us to use all 4 positions or counts of Q3: Position (count) 0 where Q3=.06, Position (count) 1 where Q3=.04, Position (count) 2 where Q3=.05, Position (count) 3 where Q3=.02. Position 0 always refers to current days data, position 1 refers to the previous days data and so on. If I3=0 at the beginning of the procedure, then after 1 iteration I3 = I3 (0) + Q3 (.02) =.02. I use Q3 =.02 because (4-1 = 3 count or position), Q3 at position 3 = .02. The next iteration I3 = I3 (.02) + Q3 (.05) =.07. I use Q3 =.05 because (3-1 = 2 count or position), Q3 at position 2 =.05. The third iteration I3 = I3 (.07) + Q3 (.04)=.11. I use Q3=.04 because (2-1 =1 count or position), Q3 at position 1 =.04 The last iteration for this cell (x) is I3 = I3 (.11) + Q3 (.06) =.17. I use Q3 =.06 because (1-1=0 count or position), Q3 at position 0=.06. When For Count ends, we have I3 =.17 at the X position in column AG. I would like the next cell down calculated for I3 and so on until the whole data base is calculated for I3. I hope that clears the ambiguity. Let me know if I can clarify further. Thanks for making me think more about what I want!! It seems to be an essential prerequisite to programming. Thanks again for your time, LenS LenS wrote: JL I'm sorry for the confusion, I should take more time to detail the problem. As for the value in AH, in my original communication: There are no blanks in this column and how this value is used is as a counting value (as per the Easy language code : For Count= 0 to Int(SP/2)-1). If AH or SP/2 is 4, then it steps through and adds the Q3 value to I3 which is initially =0. I think that's what the Easy Lang. code is saying but I'm not sure. When this counter SP/2 is done, 4 in this part, I will have obtained I3 for the current cell in AG. I then want the routine to drop to the next cell in AG and begin the process again with a new counter (SP/2 is 3 in the next case) and calculate I3 for that cell. So column AH determines how many Q3's are involved at any one run. I apologize again for not being clear. I'm sure you run into alot of that programmer vs non-programmer. I really appreciate the time you put into this, thanks. Keep in mind that column AF and AH have no blanks except at the beginning and end of the data. LenS JLatham wrote: I'll try to help some, but I must admit to being confused by how you want the value in AH to be used to figure out which values of Q3 (In col AF) to add together to get the value for I3 in column AG. So the code may not be doing that properly. First the code, then some explanation. Sub CalculateI3() Dim I3value As Single Dim LastRowOfData as Long Dim LC as Integer ' Loop Counter 'find last row with data in column AH LastRowOfData = Range("AH" & Rows.Count).End(xlUp).Row 'go to first possible data entry in AH 'assumes row 1 has header text Range("AH2").Select 'work down thru all cells to last row used Do While ActiveCell.Row <= LastRowOfData 'assumes if cell in AH is not empty, it is number If Not(IsEmpty(ActiveCell)) Then 'I'm a little confused here, so may not be right For LC = 0 to Int(ActiveCell.Value/2)-1 ActiveCell.Offset(0,-1) = _ ActiveCell.Offset(0, -1) + _ ActiveCell.Offset(-LC,-2) Next ' end of LC loop End If ' test for empty cells 'move to next Row ActiveCell.Offset(1, 0).Activate Loop ' down thru rows End Sub The 'Dim' statements declare a variable for use later, simply reserving room for it to be used. If your code module includes the declaration Option Explicit at the beginning of it, then Dim or Const declarations are required. I recommend it because of several reasons that I won't go into here (end result: better code less prone to failure). Because we are going to be doing the math based on the contents of cells, we don't need variables to hold interim results. But we do need to know how far down the sheet to work at getting information and we need to know how many values of Q3 to add together to get the I3 values. LastRowOfData is set up as type Long so it can hold very large integer numbers, since you may have thousands of rows of data to work through. LC, to be used as a loop counter, is set up as Integer to hold smaller integer numbers, although to be on the safe side it possibly should be set up as type Long also. The first executable line of code looks up from the bottom of column AH until it finds some entry in that column. That tells us how far down the sheet we have to work in examining the information to be used. We save that row number for reference in LastRowOfData. We get into a Do While loop that is simply going to work down the SP2 column row by row looking for numbers until it gets past the LastRowOfData on the sheet. the If Not(IsEmpty()) statement is used to test if there's something in a cell in the SP2 column (AH) and if there is, an assumption that it is a number is made and we then calculate the I3 value. A little about the ActiveCell.Offset() instructions. Offset() takes two arguments, first the number of rows to offset, where negative numbers are "up" the sheet and positive numbers are on "down" the sheet, and zero is same row the active cell is on. Second argument is the number of columns to offset from the location; negative numbers are to the left of the reference cell, positive numbers are to the right of it. Zero is in same column. So an .Offset(0, 0) is actually no offset at all, and would refer to the reference location itself. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cell References | Excel Discussion (Misc queries) | |||
| Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
| Count # of A's in a Cell | Excel Worksheet Functions | |||
| Cell Count | New Users to Excel | |||
| Count on cell for each time it is changed | Excel Discussion (Misc queries) | |||
Linear Mode
