VBA Noob, "Compile Error: Invalid Use of Property" On First line of Code (great..)

Hi all,

I'm trying to create a code for cell G20 (see attached workbook), which represents a value for "intercept". This "intercept" value changes, depending on multiple conditions, which is why I need to write a complex nesting IF function with multiple AND conditions to get the correct value. Currently, I have only six nesting IFs, but eventually I will need 963. I am just testing this out to see if I can get six going and then I will worry about the remaining 957 lol.

So here is my code to accomplish this:

Sub IfThenNesting()

If C3 = "CCI" And C5 = "Case-Shiller National Home Price Index" And C9 = 5 Then Range("I4").Value
End If
ElseIf C3 = "CCI" And C5 = "National Unemployment Rate" And C9 = 0 Then Range("I5").Value
End If
ElseIf C3 = "CCI" And C5 = "S&P 500 Stock Prices" And C9 = 0 Then Range("I6").Value
End If
ElseIf C3 = "CCI" And C5 = "# of House Units Starts" And C9 = 3 Then Range("I7").Value
End If
ElseIf C3 = "National Unemployment Rate" And C5 = "S&P 500 Stock Prices" And C9 = 0 Then Range("I8").Value
End If
ElseIf C3 = "S&P 500 Stock Prices" And C5 = "Nordstrom Stock Price" And C9 = 0 Then Range("I9").Value
End If
End Sub

and my workbook is attached here:
VBA IF test.xlsx

I want to do a nesting IF statement with multiple conditions. It all makes sense if you look at the worksheet. I want the calculation of the IF statement to be entered into cell G20.

Any help is GREATLY appreciated on getting me started. THANK YOU.

Get rid of every "End If" and "ElseIf" - you really are not using nesting at all.

But your code has other problems: if your C3, C5, and C9 are cells , then use

If Range("C3").Value = "CCI" And Range("C5").Value = "Case-Shiller National Home Price Index" And Range("C9").Value = 5 Then Range("G19").Value = Range("I4").Value

But if you expect 900+ nested ifs then you really should be doing something different.

Originally Posted by Bernie Deitrick

Get rid of every "End If" and "ElseIf" - you really are not using nesting at all.

But your code has other problems: if your C3, C5, and C9 are cells , then use

If Range("C3").Value = "CCI" And Range("C5").Value = "Case-Shiller National Home Price Index" And Range("C9").Value = 5 Then Range("G19").Value = Range("I4").Value

But if you expect 900+ nested ifs then you really should be doing something different.

OK - I still get the same error with the first line even after making those 'Range("C9").Value' updates for all of my cell values. what are other options that I should be doing other than nesting IF statements? Keep in mind, I am not a VBA coder so I was trying to keep it simple even though it is very repetitive.

My code now currently reads like this and still get the error on first line:

Sub Test()
If Range("C3").Value = "CCI" And Range("C5").Value = "Case-Shiller National Home Price Index" And Range("C9").Value = 5 Then Range("I4").Value

If Range("C3").Value = "CCI" And Range("C5").Value = "National Unemployment Rate" And Range("C9").Value = 0 Then Range("I5").Value

If Range("C3").Value = "CCI" And Range("C5").Value = "S&P 500 Stock Prices" And Range("C9").Value = 0 Then Range("I6").Value

If Range("C3").Value = "CCI" And Range("C5").Value = "# of House Units Starts" And Range("C9").Value = 3 Then Range("I7").Value

If Range("C3").Value = "National Unemployment Rate" And C5 = "S&P 500 Stock Prices" And Range("C9").Value = 0 Then Range("I8").Value

If Range("C3").Value = "S&P 500 Stock Prices" And C5 = "Nordstrom Stock Price" And Range("C9").Value = 0 Then Range("I9").Value

End Sub

First, you are putting your code into the wrong place. Delete the code that you have, then double-click the Module1 object in your project, and paste this into the window that appears. You were getting the error on the first line because the sub was not compiling, because you were using

.... Then Range("I4").Value

without assigning a value to I4 or using I4's value in any way.

Please Login or Register  to view this content.

Originally Posted by Bernie Deitrick

First, you are putting your code into the wrong place. Delete the code that you have, then double-click the Module1 object in your project, and paste this into the window that appears. You were getting the error on the first line because the sub was not compiling, because you were using

.... Then Range("I4").Value

without assigning a value to I4 or using I4's value in any way.

Please Login or Register  to view this content.

Thank you. Copying and pasting the above code worked!!! WOW. I am very thankful for your help and it makes sense. Except I made a typo in my first post and said G19 when I meant G20 so I will change it. You're awesome! THANK YOU.

Except now I have to press "run" anytime I want G20 to update. How do I have it automatically run anytime someone changes any of the criteria in cells C3, C5, and or C9?

Great! Get yourself a good book - I always recommend John Walkenbach's "Excel XXXX Power Programming With VBA" series - available for older versions used (and cheaper), but the basics still apply, so it is a good start:

http://www.amazon.com/gp/offer-listi...condition=used

1) Copy this code.
2) Right-Click the sheet tab of interest.
3) Select "View Code"
4) Paste the code into the window that appears.
5) Save the file as a macro-enabled .xlsm file.
6) Make changes as needed to the macro name

Please Login or Register  to view this content.

Originally Posted by Bernie Deitrick

1) Copy this code.
2) Right-Click the sheet tab of interest.
3) Select "View Code"
4) Paste the code into the window that appears.
5) Save the file as a macro-enabled .xlsm file.
6) Make changes as needed to the macro name

Please Login or Register  to view this content.

Hi Bernie,

I ordered that book from Amazon and it hasn't come yet. But I'm excited to be getting it next week. So I have another question regarding this project for you since you have been so great at helping! Attached is my updated workbook. Everything is working correctly and flawlessly, thanks to your help.

Right now all of my data and calculations are on the same sheet, but I would like to move the data to sheet 2, and have my calcuations on sheet 1 so I don't have to be looking at all the data on the form. Here is the workbook for reference:

La la.xlsm

Here is my code for reference:

In Module1

Please Login or Register  to view this content.

In Sheet 2

Please Login or Register  to view this content.

So all of the above code works perfectly. Except now I have all the data on sheet named "Calculation" in the same position except on sheet named "data". I want the VBA code to use the cells on worksheet named "Data" instead of the current cell ranges it is using on worksheet named "calculation.

I tried changing all of the above to

Please Login or Register  to view this content.

but this did not work? Can you send me in the right direction to how to keep the code working properly by changing the data to another sheet?

Thank you! <3

You don't have a worksheet name "data" in the posted file.

So try this. Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

You should just use a formula in those three cells:

B28:

=SUMPRODUCT((F4:F15=C3)*(G4:G15=C5)*I4:I15)

B29:

=SUMPRODUCT((F4:F15=C3)*(G4:G15=C5)*H4:H15)

C19

=INDEX(J:J,SUMPRODUCT((F4:F15=C3)*(G4:G15=C5)*ROW(H4:H15)))

When you have a larger table just change the 15s to the a number higher than the number or rows in your table.


In code, you would use:

Please Login or Register  to view this content.

OK I got it to work. THe issue was I did not have a T in the "shtd" or "shtc" in this part:

Corrected version:

Please Login or Register  to view this content.

it used to be..
Incorrect version:

Please Login or Register  to view this content.

I added the T, and it works when I manually "run" the code, but it is no longer automatically updating. The code you gave me to get it to automatically update is no longer applicable because I changed the sheet.How do I get it to run automatically again? I copied the code into the "data" sheet's code dialogue, but this does not work.

EDIT: OK so weirdly enough, i closed out of excel and re-opened it and now it all works automatically without any problems. this is so confusing! Thank you again for all of your help.

Great - glad to hear you got it to work, despite my poor typing skills

Please Login or Register  to view this content.

Post your workbook.

Did you look at using the formula that I posted instead of macro code?

Originally Posted by Bernie Deitrick

Post your workbook.

Did you look at using the formula that I posted instead of macro code?

Sorry for the late reply Bernie, usually I get an email notification when you respond but this time I did not for some reason. I did not look at the formula you posted, no. Do you think I should do that still - here is my workbook:

La la (Autosaved).xlsm

Let me know! Thank you.

EDIT:

It appears that your formula method is working!!!!!!!!!!!!!!! WOW. THANK YOU~

If you're interested, here is the final worksheet (for now... )

La la Final.xlsm