Solved Compile Error From VBA Editor

June 26, 2014 at 11:40:11
Specs: Windows 7
Problem with formula?
I tried to use the formula listed on the July 26, 2010 from Humar and when I got to the + Range ("B1"). Value I got the following message: Compile error: Expected: line number or label or statement or end of statement and the data was in RED. Can you help me figure out what the problem is?

See More: Compile Error From VBA Editor

Report •


✔ Best Answer
July 6, 2014 at 14:29:28
Please post this question in it's own thread with a relevant subject line.

This thread had morphed from a "compiler error" question to a question about how to code a specific mathematical operation in VBA. In order to keep the archives organized, your current question deserves it's own thread.

DerbyDad03
Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
June 26, 2014 at 13:32:59
First a posting tip:

First, when posting in a Help forum such as this, please try to use a Subject Line that tells us something about your question. If everyone used a generic Subject Line such as "Why does this not work?" or "I Need Help", we wouldn't be able to tell one question from another and the Archives would essentially be useless.

As moderator of this forum, I have editied the subject line of your post to reflect what your question is about. If you would take a look at some of the other subject lines used for questions in this forum, you should be able to get a feel for what a "relevant subject line" looks like. Thanks!

As for your question, let's just make sure we have the terminology correct. What you referred to as a "formula" is actually called a "macro". A formula resides in a worksheet cell, while a macro resides in a VBA module within a workbook.

That said, based on the screen shot I see in your post, it looks to me as if one line has been split into two.

While this line is a valid VBA instruction...

Range("A1").Value = Range("A1").Value

...this line, by itself, is not, and therefore presents the error message:

+ Range("B1").Value

While the instruction referencing A1 is valid and VBA will process it, it really doesn't do anything except set A1 equal to itself, basically changing nothing. However, look at what happens when we combine those 2 lines into a single instruction:

Range("A1").Value = Range("A1").Value + Range("B1").Value

We are telling VBA to add the value in B1 to the value in A1 and store the result in A1.

There are 2 ways that an instruction like that can be written. It can be written as single line as shown above, or it can be split in two if the "continuation character" (an underscore) is used. If we use the continuation character, it would look like this, and VBA would read it as a single line.

Range("A1").Value = Range("A1").Value _
                  + Range("B1").Value

Note the space before the underscore. That space is required in order for VBA to recognize the underscore as the continuation character.

I hope that resolves your issue.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 28, 2014 at 06:24:04
Thank you for letting me know protocol. Unfortunately I am self taught and stumble.

I did what I thought your instructions were and when I try to apply them I get an error. I have included a Screen Capture for you to look at.


Thanks for your help.


Report •

#3
June 28, 2014 at 06:32:57
All I see is the original screen capture in your first post. That shows the original issue with the line highlighted in red.

Until I see what changes you've made, there's not much else I can offer. Instead of using a screen capture, why not paste the code directly into a post so I can read it? Please click on the following line and read the instructions on how to post VBA code in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
June 30, 2014 at 05:40:35
Thank you again. I am using Excel 2010. I saved this in Excel Macro Enabled Workbook and when I tried to use it I got an error and I had when tried to Debug this line got a yellow fill color on it.

Range("A1").Value = Range("Al").Value + Range("B1").Value

Here is the entire thing I typed:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
'disable events
Application.EnableEvents = False
'add B1 value to A1 and save in A1
Range("A1").Value = Range("Al").Value + Range("B1").Value
End If
'reenable events
Application.EnableEvents = True
Exit Sub
'error handler
ErrHnd:
Err.Clear
'reenable events
Application.EnableEvents = True
End Sub


Report •

#5
June 30, 2014 at 06:35:42
Range("A1").Value = Range("Al").Value + Range("B1").Value
_______________________^^

Do you really want the Range to be Al or A1??

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#6
June 30, 2014 at 06:59:03
Mike appears to have found the error.

You, however, do not appear to have read the instructions on how to post code in this forum, as I requested.

If you fix the error that Mike pointed out, you should be good to go. If you need to repost the code, please read the instructions found by clicking on the following line.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
June 30, 2014 at 14:21:26
Thank you, Thank you, Thank you. Such a small, and important thing!
I now will be able to continue with what I am trying to do.

Now that it works what I need to do is, not ADD B1 but use the amount in B1 as a multiplier. For instance if I have 100 in A1 and 15% in B1 I want A1 to have the answer.

Can I do that?

message edited by nellb


Report •

#8
June 30, 2014 at 17:15:47
Let's see, if the code uses the addition operator (a plus sign +) to add the values in A1 and B1, what operator do you think it should use to multiply A1 by B1?

BTW...I have reset the Best Answer since it now appears that your issue has not been solved.


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#9
June 30, 2014 at 18:01:47
DerbyDad03

In reference to your post at 6:39, I am sorry if I did not post correctly. I did read it but didn't understand what I was supposed to do and did what I thought I should have done.

Could you explain what I should have done more thoroughly?

I am very new at this, am self taught and would like to "obey the rules.

Thank you for reset the Best Answer for me.


Report •

#10
July 1, 2014 at 04:17:07
Above the box where you enter the text for your post, you will see icons for bold, italic, strikethrough, etc. Those tags are used to apply formatting to your post.

As noted in the How-To...

Step 1. Click the < pre > icon found above the Reply box.
Step 2. Enter your data/table(s) or VBA code between the tags.

After you click on "pre" you will see < pre ></ pre > in your post. If you enter/paste your code between the pre tags it will retain it's indentation, spacing, etc. The text should look like this:

     Notice how this line is indented.
          This line is indented further.
     This     line    has    extra     spaces.
        The pre tags allow the text to retain it's original indenting and 
        spacing which should be used in VBA code to make it easier
        to read.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#11
July 6, 2014 at 05:51:45
Thank you again. I did enter multiply B1 value to A1 and save in A1 and put the * sign in the next line so it multiply it. That worked great.

What I needed to do is add that answer back to the original number in A1 so I can get the new total I am looking for.

It seems as if I need to add some instructions but don't know what and where.

Here is my posting so far:

If Target.Address = "$B$1" Then
    'disable events
    Application.EnableEvents = False
    'multiply B1 value to A1 and save in A1
    Range("A1").Value = Range("A1").Value * Range("B1").Value
End If
'reenable events
Application.EnableEvents = True
Exit Sub
'error handler
ErrHnd:
Err.Clear
'reenable events
Application.EnableEvents = True
End Sub


Report •

#12
July 6, 2014 at 14:29:28
✔ Best Answer
Please post this question in it's own thread with a relevant subject line.

This thread had morphed from a "compiler error" question to a question about how to code a specific mathematical operation in VBA. In order to keep the archives organized, your current question deserves it's own thread.

DerbyDad03
Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#13
July 10, 2014 at 05:09:42
Thank you. I will and I will try to adhere to the "rules" I have learned. This part should be marked solved, but I could not figure out how to do that. Sorry.

Report •


Ask Question