Using IF statement in VBA

  • Thread starter Thread starter Dustin B
  • Start date Start date
D

Dustin B

I am writing some VBA code. What I want to do is go through each row of a
sheet and put a the formula =IF(R" & intRowNumber & " = "US", "D", "F").
Basically I want it to check each cell in Column R to see if it is US if it
is put a D in if not put in an F. The code is below.

Private Sub DorF()

Dim intRowNumber As Integer
Dim strCountry As String

For intRowNumber = 2 To Rows.Count
Range("brazil!W" & intRowNumber).Formula = ("=IF(R"& intRowNumber & " =
"US", "D", "F")")
Next

End Sub

Obviously this doesn't work because of all of the quote marks. How do I get
around that.
 
It's pretty rare that someone wants to fill up all the cells in the column with
a formula. Lots of times, the user wants to fill up until the last used row.
(But this may not be what you really want.)

This code looks in column A to find the last used cell in that column and fills
up column W with your formula:

Option Explicit
Private Sub DorF()
Dim intRowNumber As Long
Dim LastRow As Long

With Worksheets("Brazil")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For intRowNumber = 2 To LastRow
.Range("W" & intRowNumber).Formula _
= "=IF(R" & intRowNumber & " =""US"",""D"",""F"")"
Next intRowNumber
End With
End Sub

But filling the formula cell by cell can take a pretty long time.

If you were doing this manually, you could select W2:W### and then type the
formula (for W2) and then hit ctrl-enter to fill all those selected cell with
the same formula. Excel will adjust the references just like if you copied and
pasted it.

In code, it would look like:

Option Explicit
Private Sub DorF()
Dim LastRow As Long

With Worksheets("Brazil")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("W2:W" & LastRow).Formula = "=IF(R2=""US"",""D"",""F"")"
End With
End Sub

The general tip for those quotation marks in your VBA code is to double them up.
 
If your objective is to put the formula into the cell so it will
automatically update when future changes are made in Column R, then this
patch to the code statement inside the For-Next loop should work (note the
doubling up of the **internal** quote marks)...

Range("brazil!W" & intRowNumber).Formula = ("=IF(R" & _
intRowNumber & " => ""US"", ""D"", ""F"")")

Rick
 
Try this to just mark without a formula being left.

Sub markcellif1()
For i = 2 To Cells(Rows.Count, "r").End(xlUp).Row
If Len(Trim(Cells(i, "r"))) > 0 Then
Select Case UCase(Cells(i, "r"))
Case Is = "US": x = "D"
Case Else: x = "F"
End Select
Cells(i, "s") = x
End If
Next i
End Sub
 
Back
Top