CODEWITHSUNDEEP
vbaexcel
user8958702
user8958702

Reputation: 57

Using Loop to Clear contents

I've written the following code to check if values in row A equal to "Forecast" then Range D5:D53 should have its contents cleared.

Row 1 is a vlookup so there's a formula that derives "Actual" or "Forecast"

Dim k As Integer

For k = 1 To 13
    If Sheets("2017 Forecast").Cells(k, 1).Value = "Forecast" Then
        Sheets("2017 Forecast").Range("D5:D53").Select.ClearContents
    End If
Next k

Upvotes: 0

Views: 1832

Answers (2)

Shai Rado
Shai Rado

Reputation: 33682

There's no need to use Select before you use ClearContents.

Also, try adding UCase to make sure you don't have any CAPITAL letter in the middle of your text.

Code

Dim k As Integer

With ThisWorkbook.Sheets("2017 Forecast")
    For k = 1 To 13
        If UCase(.Cells(k, 1).Value2) = "FORECAST" Then
            .Range("D5:D53").ClearContents
        End If
    Next k
End With

Upvotes: 1

chillin
chillin

Reputation: 4486

Maybe this works for you?

Option explicit

Sub Compare skies()

Dim k As long
Dim ValueRead as variant

With Sheets("2017 Forecast")
For k = 1 To 13

ValueRead = .Cells(k, 1).Value

' Slow, case insensitive string comparison '
If strcomp(ValueRead,"Forecast",vbtextcompare) = 0 Then
    .Range("D5:D53").ClearContents ' You want to clear the exact same range 13 times? '
Elseif strcomp(ValueRead,"Actual",vbtextcompare) <> 0 then
Msgbox("VLOOKUP returned a value outside of Actual and Forecast")

End if

Next k
End with

End sub

Upvotes: 0

Related Questions