Excel VBA "If" statement Error

Question

So I'm getting an error with some code and I don't know how to get around it

Range("K" & varOffset).Select
           Output = If (ISBLANK(H2), "No", "Yes")      <----------Shows up red
           ActiveCell.FormulaR1C1 = Output`

How do I make this if statement work?

yoyoyoyo123 · Accepted Answer · 2015-07-16 15:26:11Z

1

Looks like you are trying to use the worksheet version of IF.

Try VBA IF block

If IsEmpty(H2) Then
    Output = "No"
Else
    Output = "Yes"
End If

answered Jul 16, 2015 at 15:26

yoyoyoyo123

2,4923 gold badges25 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

VBAnoob Over a year ago

So I also want it to update when I change "H2" in the sheet after I do the macro, any ideas?

Krishna · Accepted Answer · 2015-07-16 15:20:35Z

0

Try this

Formulae in Excel is not VBA. VBA has its code structure that needs to be adherered to

Range("K" & varOffset).Select
If trim(Range("H2")) = "" then 
  output = "No"
else 
  output =  "Yes"
end if
ActiveCell.FormulaR1C1 = Output

answered Jul 16, 2015 at 15:20

Krishna

2,4811 gold badge26 silver badges31 bronze badges

2 Comments

VBAnoob Over a year ago

Thank you very much, this helped a lot! However, I realized I made a huge mistake in my question, I actually want it to check two cells to the left of the current cell (Instead of always H2). I also want it to update when I change that information, as of right now the cell stays as "yes" or "no" regardless of whether H2 has changed. Would this be possible?

VBAnoob Over a year ago

So I figured out how to make the offset, but need it to update when I add information in.

99moorem · Accepted Answer · 2015-07-17 08:20:25Z

0

the below will avoid the need to select a cell which should always be avoided. It also avoids the need for the output variable

with Range("K" & varOffset)
    If trim(Range("K" & varOffset).offset(0,-2).value)="" then'If blank
        .value= "Yes"
    else
        .value= "No"
    endif
end with

edited Jul 17, 2015 at 8:20

answered Jul 16, 2015 at 15:24

99moorem

1,9931 gold badge16 silver badges28 bronze badges

1 Comment

VBAnoob Over a year ago

So I made a mistake. I actually want it to compare 2 cell to the left of the current cell.

Collectives™ on Stack Overflow

Excel VBA "If" statement Error

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related