Reputation: 7
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
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