Excel macro producing wrong results

Posted on 2003-12-03
Last Modified: 2010-04-17
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.
Question by:Mosquitoe
  • 3
  • 2

Expert Comment

ID: 9866955
what exactly are you trying to do with the code?

Expert Comment

ID: 9867048
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


Author Comment

ID: 9867203
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.

Accepted Solution

victoresq earned 250 total points
ID: 9867805
har har har

I work with VBA all the time and often find that the devil is in the details.

I am a Chemical Engineer by education (concentrations speak to me !!!) and have been doing work for the Chemical/Petroleum Industry for the last 6 years.  I run across applications like this all the time.

I think I found your issue:

please change the "" to a 0 and rerun the code, because this is what you are really checking for in the G-E calculation, and get rid of all that Format stuff:

Private Sub spanseek_Click()

    For cell = 5 To 27 Step 1

   'make sure the difference between column G and E is actually a number if he/she did
   'intend to force a "" value, then I think this is wrong.  But you can put  back the "" if this is the case.

   'Checking to see if G-E is actually 0 please pay attention to precision ... ie is it REALLY ever zero
   'This should actually be something more like If abs(Range("I" & cell)) > 0.0001 or some such nonsense
   'Which speaks more to my point about getting rid of the "" check and going to a straight number evaluation.
   'Numbers should be used all over XLS, leave the text for reports.
   'note: if you keep the "" evaluation then get do not use the abs line above, since the function does not evaluate strings

    If Range("I" & cell) <> 0 Then

            'I guess I will say the same for the Goal: value here as well for precision.  But zero is a nice way

            Range("I" & cell).GoalSeek Goal:=0, ChangingCell:=Range("J" & cell)
    End If
    Next cell

End Sub

I ran this against the XLS macro that I created and it does not give an exact zero value more like +/- .0001 precision.  But XLS does report this as the value for the solution when I run it by hand, so the above calculation does work as I have configured it.  Oh, yeah the J column no longer increments.

let me know if you need further assistance.


Author Comment

ID: 9874620

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!

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SPLUNK REST  API call to Splunk to create and index? 2 103
changePi Challenge 15 104
groupSumClump challenge 9 102
autoit - check if option is checked in another program 2 111
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now