VBA Syntax

[Jym396]

I'm tryin' here...just can't get it...
Using this formula in the worksheet:
=IF(INDEX(B388:B404,MATCH(TODAY(),C388:C404,0))<>"","N","Y")
And it works just fine. Just can't seem to get the VBA syntax right. Do I need to use the
Application.Application.WorksheetFunction.Index(Sheets("Timesheet").Range("B388...etc
Application.Application.WorksheetFunction.Match ...etc.
Do I need those before every function?

Thanks a lot,
Jim

 

[Misca]

Re: VBA Syntax Help

When you're calling Excel functions from VBA you need to use the Application.WorksheetFunction before the actual function.

However you can also use the With -statement around your formula to make it easier to read / write:

Dim Rng As Range

Set Rng = Cells(1, 1).CurrentRegion


With Application.WorksheetFunction
    MsgBox .Index(Rng, .Match(2, Rng.Columns(1), 0), .Match(4, Rng.Rows(1), 0))
End With

 

[Jym396]

Re: VBA Syntax Help

Thank you so much Misca. I'm almost there. I already have code written to insert the time into the correct cell. (I don't know how to post my code correctly...sorry). I just want to check the cell 2 cells to the left and either end sub or execute the rest. I would copy and paste the code directly, but I don't know if that is allowed.

Thanks again,
Jim

 

[Fluff]

Re: VBA Syntax Help

@Jym396
You can post code to the site, but when doing so please use code tags. In the reply window click on the # icon & then paste your code between the tags that appear.

 

[Jym396]

Re: VBA Syntax Help

Thanks Fluff! Can you help me with my problem? Here Is what I have so far:
(I hope I do this right)
Code:

Sub TodayOut()'
' TodayOut Macro
' Goto today's date and select cell for time out.
' Created 081717


'


For Each cell In ActiveSheet.Range("C404:C464")
    If cell.Value = [Today()] Then
    cell.Select
    End If
    
Next


ActiveCell.Offset(0, 3).Select






Call CurrentTime


End Sub

Would like to insert code before the call.

 

[Fluff]

Re: VBA Syntax Help

Is this what you mean

Sub TodayOut() '
' TodayOut Macro
' Goto today's date and select cell for time out.
' Created 081717


'
Dim Cl As Range

For Each Cl In ActiveSheet.Range("C404:C464")
    If Cl.Value = [Today()] Then
    Cl.Offset(, 3).Formula = "=IF(Index(B388:B404,MATCH(TODAY(),C388:C404,0))<>"""",""N"",""Y"")"
    End If
Next

Call CurrentTime


End Sub

I would recommend avoiding the use of VBA keywords as variables, as it can cause problems. Hence I've changed your variable cell to Cl

 

[MARK858]

Re: VBA Syntax Help

I'm guessing by the last post the OP made it isn't related to the formula ...

Sub TodayOut()    '
    ' TodayOut Macro
    ' Goto today's date and select cell for time out.
    ' Created 081717
    Dim myCell As Range
    For Each myCell In ActiveSheet.Range("C404:C464")
        If myCell.Value = Date Then
            myCell.Offset(0, 3) = Format(Now, "hh:mm am/pm")
            Exit Sub
        End If
    Next

End Sub

 

[Jym396]

Re: VBA Syntax Help

Wow Fluff! That is impressive. You know waaaay more than I do. Just curious...how did you learn? I am trying to find a 'real' classroom, not online, but can't. Anyway...how would you check the cell 2 to the left of the one it finds. If it is blank ("")...then proceed. If it contains anything (<>)...then end sub. Probably before the call.

 

[MARK858]

Re: VBA Syntax Help

Seeing as Fluff doesn't appear to be online do you mean....

For Each Cl In ActiveSheet.Range("C404:C464")
    If Cl.Value = [Today()] Then
    Cl.Offset(, 3).Formula = "=IF(Index(B388:B404,MATCH(TODAY(),C388:C404,0))<>"""",""N"",""Y"")"
    [COLOR="#FF0000"]If Cl.Offset(, -2) <> "" Then Exit Sub[/COLOR]
    End If
Next

 

[Jym396]

Re: VBA Syntax Help

Seeing as Fluff doesn't appear to be online do you mean....

For Each Cl In ActiveSheet.Range("C404:C464")
    If Cl.Value = [Today()] Then
    Cl.Offset(, 3).Formula = "=IF(Index(B388:B404,MATCH(TODAY(),C388:C404,0))<>"""",""N"",""Y"")"
    [COLOR=#FF0000]If Cl.Offset(, -2) <> "" Then Exit Sub[/COLOR]
    End If
Next

Thanks Mark, but I think it crashes on the formula statement. It really doesn't need the "Y","N"...that was only for function purpises in the sheet.