Qfyilyi

Is it possible to set the value of myTerm using a series of If statements?

I have 3 columns in a worksheet which will have data added to them over the course of the year.

I have the following scenarios:

K has data and M & O = "Autumn"

K and M have data and O is empty = "Spring"

K, M and O all have data = Summer

I have written the following (a snippet of the full code):

Sub FilterProgressData()



    Dim myTerm As String



    If SrcWs.Cells(i, "O").Value = "" And SrcWs.Cells(i, "M").Value = "" And _

        SrcWs.Cells(i, "K").Value <> "" Then

            myTerm = "Autumn"



    ElseIf SrcWs.Cells(i, "O").Value = "" And SrcWs.Cells(i, "M").Value <> "" _

        And SrcWs.Cells(i, "K").Value <> "" Then

            myTerm = "Spring"



    ElseIf SrcWs.Cells(i, "O").Value <> "" And SrcWs.Cells(i, "M").Value <> "" _

        And SrcWs.Cells(i, "K").Value <> "" Then

            myTerm = "Summer"

but get a 'Run-time error '1004' error - Application-defined or object-defined error'

Any advice will be gratefully received!

Thanks.

This works fine. I do not see a problem with your if statement.

Option Explicit



Sub FilterProgressData()

Dim myTerm As String

Dim i As Integer

Dim SrcWs As Worksheet

Set SrcWs = Worksheets("Sheet1")



i = 1



    If SrcWs.Cells(i, "O").Value = "" And SrcWs.Cells(i, "M").Value = "" And _

        SrcWs.Cells(i, "K").Value <> "" Then

            myTerm = "Autumn"



    ElseIf SrcWs.Cells(i, "O").Value = "" And SrcWs.Cells(i, "M").Value <> "" _

        And SrcWs.Cells(i, "K").Value <> "" Then

            myTerm = "Spring"



    ElseIf SrcWs.Cells(i, "O").Value <> "" And SrcWs.Cells(i, "M").Value <> "" _

        And SrcWs.Cells(i, "K").Value <> "" Then

            myTerm = "Summer"



    End If

    End Sub

Couple of issues that I could see in your snippet:

• 
  SrcWs is not declared/instantiated anywhere in your snippet.


• 
  i is not assigned. If it's being implicitly converted to 0 (when using Cells()), then you're trying to access the zeroth row of the worksheet (which doesn't exist and will give you an error).

Say you want to assign a value to myTerm based on the values in columns K, M, O and rows 1 to 100 (for example's sake). You could then try something like the below:

Option Explicit



Sub ConditionallyAssignMyTerm()

    Dim SrcWs As Worksheet

    Set SrcWs = ThisWorkbook.Worksheets("Sheet1")



    Dim i As Long ' Might be better to rename this to rowIndex or something more descriptive than just i



    With SrcWs

        Dim myTerm As String



        Dim columnKisBlank As Boolean

        Dim columnMisBlank As Boolean

        Dim columnOisBlank As Boolean



        For i = 1 To 100 ' Arbitrary loop values

            columnKisBlank = CellIsBlank(.Cells(i, "K"))

            columnMisBlank = CellIsBlank(.Cells(i, "M"))

            columnOisBlank = CellIsBlank(.Cells(i, "O"))



            If (Not columnKisBlank) And columnMisBlank And columnOisBlank Then

                myTerm = "Autumn"



            ElseIf (Not columnKisBlank) And (Not columnMisBlank) And columnOisBlank Then

                myTerm = "Spring"



            ElseIf (Not columnKisBlank) And (Not columnMisBlank) And (Not columnOisBlank) Then

                myTerm = "Summer"

            End If

        Next i



    End With

End Sub



Function CellIsBlank(ByRef cellToCheck As Range) As Boolean

    CellIsBlank = VBA.IsEmpty(cellToCheck.Value2) Or (Len(cellToCheck.Value2) = 0)

End Function