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]

See if this works

Sub Macro8()
If LCase(Range("choice").Value) Like "*specified amount*" Then
    Range("housing_selection").ClearContents
ElseIf LCase(Range("choice").Value) Like "*m data*" Then
    Range("housing").ClearContents
End If
SolverOk SetCell:="$N$7", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$3"
SolverSolve userFinish:=True
End Sub

 

[yichuansancun]

OH MY GOD, I think it worked!!!!! I will do more testing... thank you so much!!!!

Just for my learning sake, what makes the differences?

You are awesome!!!

 

[VoG]

Two things:

I used LCase to make it case-insensitive so Specified Amount would also work.

I used Like with * before and after in case there was other text or spaces in the cell.

 

[yichuansancun]

thanks again!

 

[yichuansancun]

Sorry... more issues...

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

for the above codes, how can I make them work in 2003? Apparently 2003 doesn't have SolverOK?

Thanks,
Perri

 

[VoG]

I think it does but you need to set a reference (see above).

 

[yichuansancun]

Set reference? I don't see anything above... sorry if my question sounded stupid

 

[VoG]

As above

In Tools > References make sure that Solver is ticked.

 

[yichuansancun]

Yes, solver is checked.

 

[VoG]

Sorry, then I don't know why it isn't working.