confusion with IF statement

**Mortada** · 11-13-2012, 09:26 PM

Hii guys,

Currently I'm working on creating a priortisation matrix in excel to weight different criteria, and select the right project for investment.

So far the macro works for all but one criteria. I want the macro to rank projects in Ascending order if the user inputs a weighting factor for depreciation value bigger then 0%. With my very limited programming skills I would've thought a simple If statmenet when the cell which contains the depreciation weighting value (in this case is b10) is > 0 then do the same as above with the exception being changing the sorting to ascending? Currently the macro is ranking the projects in descending order for depreciation value and I want to reverse that

Here is the code

Please Login or Register  to view this content.

I would appreciate some light on this.

Thanks

**6StringJazzer** · 11-13-2012, 10:11 PM

See correction below.

You would have avoided this error if you had checked the option to "Require variable declarations." B10 is seen as a variable. It is never assigned a value so it has the default value of 0. See code below.

Also, the code could be structured much better. I didn't go through all that code line by line, but is the sort order the only difference between those two blocks of code inside the If statements? If so, you just need to put that one statement inside an If as shown. Also, conditions in an If statement do not need to be enclosed in parentheses, although there's nothing wrong with it.

Please Login or Register  to view this content.

**Mortada** · 11-13-2012, 10:50 PM

Thank you for your help 6StringJazzer!!

I kinda did some research and i found out that i written the If statmente wrongly and i should had it

Range("b10).select
if selection.value > 0 then
.
.

Range("b10").select
if selection.value = 0 then
.
.
.

I did it that way before your response and it worked for me. But your way also worked of course. No doubt the macro can be simplied probably in 10lines or less.. but I'm novice and I'm just learning.. with everything I learn I always end up taking the long way to the solution it's just the way I've stumbled through life

Anyway I thank you again for quick response!

**6StringJazzer** · 11-14-2012, 09:29 AM

Please Login or Register  to view this content.

This is the type of code that is generated from the macro recorder. However, a programmer knows more than the macro recorder. It is not necessary to Select a range that you want to get the value of or assign a value to. When you select a cell, the macro recorder has no idea what you plan to do next, so it records the selection. Then if you change the value, it operates on the selection. But you can collapse this down into one step in your code, as I did:

Please Login or Register  to view this content.

When using the macro recorder, there is a large amount of excessive code generated, including explicitly setting defaults for things like fonts. Here is another example:

Please Login or Register  to view this content.

This simply sets the font style and size, which is what I assume you were trying to.

**Mortada** · 11-14-2012, 11:00 PM

Thanks for the heads up 6string! much appreciated!!!

This might be against forum rules. Please let me know if so, but I thought it might be a good idea to post here since you're prompt in your responses.

I've started modifying the code a little. I'm trying to paste an image from my C:\ for certain conditions.

heres the code

Please Login or Register  to view this content.

Now while running this I can get teh first condition to play game! but it's my while loop thats give me an error message of Object doesn't support this property or method, and it highlights this part of the code (With ActiveSheet.Picture.Insert("C:\09_OIP_Project\OIP_IMAGES\NoSelection.JPG")). If at all possible can you shed some light on this matter

Thanks

**6StringJazzer** · 11-15-2012, 07:37 AM

It's not against the rules but it's not a good idea. You have a thread marked SOLVED that is about If statements and you have posted a new question about inserting pictures. Nobody is going to read it except me. And if I don't know the answer you're out in the cold.

Fortunately I do.

Make the change below. You have the same problem in two places.

Please Login or Register  to view this content.

**Mortada** · 11-15-2012, 04:35 PM

Thanks for that 6string!

That corrected the error message, however, i'm having difficulty pasting an image. Once i execute the macro excel becomes unresponsive and I would have to alt f4 and start again. Now is that becasue i structured the while loop to loop wrongly? I just want it to function so that if A20 is "Beachlands A" and then check B20 >0 or =0 and do the respective assigned tasks of pasting an image.