Excel macro producing wrong results
I have inherited a macro written by another *gone* programmer which is not working as expected. I am not experienced writing macros, but readingthis code and steppingthrough, seems to be correct and yet, it is not producing the expected results:
Sub spanseek()
For cell = 5 To 27 Step 1
If Range("I" + Format$(cell)) <> "" Then
Range("I" + Format$(cell)).GoalSeek Goal:=0, ChangingCell:=Range("J" + Format$(cell))
End If
Next cell
End Sub
When I run this macro, rather than changing the column I cell to 0 and changing the J column to the correct figure, it leaves the I column as is and changes only the J column cell, but it seems to increment the J column every time it is run to a larger, and larger figure...I really don't know why it's is not wotking correctly as the code seems to be pretty straightforward. Can anybody see something wrong with this? Any feedback would be much appreciated.
Sub spanseek()
For cell = 5 To 27 Step 1
If Range("I" + Format$(cell)) <> "" Then
Range("I" + Format$(cell)).GoalSeek Goal:=0, ChangingCell:=Range("J" + Format$(cell))
End If
Next cell
End Sub
When I run this macro, rather than changing the column I cell to 0 and changing the J column to the correct figure, it leaves the I column as is and changes only the J column cell, but it seems to increment the J column every time it is run to a larger, and larger figure...I really don't know why it's is not wotking correctly as the code seems to be pretty straightforward. Can anybody see something wrong with this? Any feedback would be much appreciated.
what exactly are you trying to do with the code?
presuming what you want to do is verify that the value of the I column is not empty, by the way you should check for this condition instead of the "" condition. Since " " is not equivolent to "" in VB, make sure it is what you want it to be a number, blank, etc.
so my proposed code, If I understand the code is as follows:
for cell = 5 to 27 step 1
if IsNumeric( Range("I" & cell).Value ) then
Range("J" & cell).Value = cell ' this assumes you wants the value of cell in the J cell position
'Range("J" & cell).Value = Range("I" & cell).Value ' this assumes you wants the value of I cell in the J cell position
Range("I" & cell).Value = 0 'This sets the value to 0 for the I cell position
end if
next cell
let me know if this helps
whodaman
so my proposed code, If I understand the code is as follows:
for cell = 5 to 27 step 1
if IsNumeric( Range("I" & cell).Value ) then
Range("J" & cell).Value = cell ' this assumes you wants the value of cell in the J cell position
'Range("J" & cell).Value = Range("I" & cell).Value ' this assumes you wants the value of I cell in the J cell position
Range("I" & cell).Value = 0 'This sets the value to 0 for the I cell position
end if
next cell
let me know if this helps
whodaman
ASKER
Some good points - I should explain further:
Column I (lets say row 7 for example) is determined by subtracting G7-E7 (those two cells hold a bottle concentration)
Column G is detremined by this calculation: J7*J7^2+L7+J7^3*M7+J7^4+N7
which would look something like this if i used real values:
86.59 * 9.3558E-01 ^ 2 * 1.0350E-03 + 86.59 ^ 3 * - 8.5145E-06 + 86.59 ^ 4 + 3.5300E-08
So if column I is not 0, this means that the calculated bottle concentration and the actual bottle concentration are not the same value. This also means that the calculated spam number is not correct which is column J. So, the above macro is run and is supposed to calculate what the new spam number in column J would be if the value in column I is changed to match the value in column E.
Column I (lets say row 7 for example) is determined by subtracting G7-E7 (those two cells hold a bottle concentration)
Column G is detremined by this calculation: J7*J7^2+L7+J7^3*M7+J7^4+N7
which would look something like this if i used real values:
86.59 * 9.3558E-01 ^ 2 * 1.0350E-03 + 86.59 ^ 3 * - 8.5145E-06 + 86.59 ^ 4 + 3.5300E-08
So if column I is not 0, this means that the calculated bottle concentration and the actual bottle concentration are not the same value. This also means that the calculated spam number is not correct which is column J. So, the above macro is run and is supposed to calculate what the new spam number in column J would be if the value in column I is changed to match the value in column E.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I ran the code with the revisions, with one alteration, there are some blank fields in the column so I had to filter for that condition. But the result was what I was looking for - The devil IS in the details considering how minor these changes were - Thanks alot!