CODEWITHSUNDEEP
excelvba
PGD15
PGD15

Reputation: 183

VBA - populating cell value based on another cell's value (code not working)

I've tried to write the below code so depending on the value in cell worksheets "2. Survey" cell f11 then i want "yes" or no value to appear in my current tab cell e59 (current tab - 3. Unit Specification). Im currently getting run time error 9 but don't know why. (probably done something dumb)

 Worksheets("3.Unit Specification").Range("e59").Formula = "=IF('2. 
 Survey'!F11=""Mains"",""Yes"","")"

run time error 9 is my current issue i just want the cell e59 to have the value yes or have no value (this is a drop down box though and the value is an exact match to the list so don't know if that is resulting in the problem).

Upvotes: 0

Views: 63

Answers (1)

SJR
SJR

Reputation: 23081

You need to double up all the quotes. You missed the last pair.

Worksheets("3.Unit Specification").Range("e59").Formula = _
                             "=IF('2. Survey'!F11=""Mains"",""Yes"","""")"

Edit

'Non-formula approaches

If Worksheets("2. Survey").Range("F11").Value = "Mains" Then
    Worksheets("3.Unit Specification").Range("e59").Value = "Yes"
Else
    Worksheets("3.Unit Specification").Range("e59").Value = ""
End If

'OR

Worksheets("3.Unit Specification").Range("e59").Value = IIf(Worksheets("2. Survey").Range("F11").Value = "Mains", "Yes", "")

Upvotes: 1

Related Questions