what is wrong with this macro code?

[yichuansancun]

What I try to achieve is that when the cell named housing_choice = "specified amount", excel should wipe out all the contents in the range with name "housing_m", and if the cell named housing_choice = "m data" then the cell named "housing" should be empty

If Range("housing_choice").Value = "specified amount" Then
Range("housing_m").Select
Selection.Value = ""
ElseIf Range("housing_choice").Value = "m data" Then
Range("housing").Select
Selection.Value = ""
End If

but it won't work... any ideas?

Thanks

 

[VoG]

Perhaps

If Range("housing_choice").Value = "specified amount" Then
    Range("housing_m").ClearContents
ElseIf Range("housing_choice").Value = "m data" Then
    Range("housing").ClearContents
End If

 

[zaska]

Hello,

Try this

If Range("housing_choice").Value = "specified amount" Then
Range("housing_m").ClearContents
ElseIf Range("housing_choice").Value = "m data" Then
Range("housing").ClearContents
End If

 

[yichuansancun]

got an error message say "can't execute code in break mode"?

 

[VoG]

Click the Reset button (just under Run) then try again.

 

[yichuansancun]

how it gives me "compile error: wrong number of arguments of invalid property assignment"

Here is the entire code (changed some ranges' name):

Sub Macro8()
'
Macro8 Macro

If Range("choice").Value = "specified amount" Then
Range("housing_selection").ClearContents
ElseIf Range("choice").Value = "m data" Then
Range("housing").ClearContents
End If

SolverOk SetCell:="$N$7", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$3"
SolverSolve userFinish:=True

End Sub

 

[VoG]

In Tools > References make sure that Solver is ticked.

You also need to delete or comment out this line of code

Macro8 Macro

 

[yichuansancun]

this is frustrating... when I click the "calculate" button which is assigned with macro 8, the solver returns "#num!". And it is random... sometimes it works and sometimes it doesn't, once it didn't work, then I cannot even do "undo".


<table border="0" cellpadding="0" cellspacing="0" width="100"><tr height="22"><td class="xl67" style="height: 16.5pt; width: 75pt;" height="22" width="100">
</td></tr></table>

 

[VoG]

That's because running a macro clears the undo stack.

Try recording a macro whilst running Solver manually to make sure that you have the correct syntax.

 

[yichuansancun]

Now the macro runs without the error message, and the solver works fine, but the code before solverok doesn't do anything...

Sub Macro8()​

If Range("choice").Value = "specified amount" Then
Range("housing_selection").ClearContents
ElseIf Range("choice").Value = "m data" Then
Range("housing").ClearContents
End If​

SolverOk SetCell:="$N$7", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$3"
SolverSolve userFinish:=True​

End Sub​

Here are the ranges definition:
​

choice = C21​

housing_selection = C22 - C25​

housing = C26

It looks like the codes before solverok were by passed...
​