c.yoder
c.yoder

Reputation: 7

IF-Then-Else Loops in Excel VBA

Good day all,

I am trying to write a macro that will pull information from Column C of worksheet auxdata if the agent's name and date match. The rub is that the way the reports are compiled there may be a mismatch of one day (ie a record on the unified page such as 5/1/2014 | x | AGENT X may actually correspond to a record under 5/2/2014 on the auxdata sheet). I have the following code:

Sub testloop()
Dim unified As Worksheet
Dim auxdata As Worksheet
Set unified = ThisWorkbook.Sheets("unified")
Set auxdata = ThisWorkbook.Sheets("auxdata")

unified.Activate
unified.Range("a2").Select
Do While ActiveCell.Value <> Empty
    If ActiveCell.Value = "auxdata[Date]" Then
         ActiveCell.Offset(0, 3).FormulaR1C1 = _
            "=INDEX(auxdata[Staffed Time], MATCH([@agent], auxdata[Agent], 0))"
    Else
        ActiveCell.Offset(0, 3).Value = "false"
        ActiveCell.Offset(1, 0).Select
    End If
Loop
End Sub

This loop only goes to the Else statement. Activecell.value = date from unified worksheet

I need help on the If-then Statement and how to add 1 day to the auxdata[Date] in the else statemnet

Upvotes: 0

Views: 960

Answers (1)

CodeJockey
CodeJockey

Reputation: 1981

You have typed a string literal in the following line:

If ActiveCell.Value = "auxdata[Date]" Then

This is checking for the string "auxdata[Date]" not the date you mean to represent. You want it to match the actual date. If the data is in one fixed cell, you can use the following:

If ActiveCell.Value = auxData.Range("A2").Value Then

Hope this helps!

Upvotes: 1

Related Questions