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

 

[Rick Rothstein]

Re: VBA Syntax Help

Explain very carefully exactly what you want to achieve as an end result. (and in detail).

To add to Marks comment...

Please do not simplify your question for us... doing so will get you a great answer to a question you do not actually have and which you do not actually care about AND it will almost always lead to you coming back for help when the solution we give you for the simplified question cannot be applied to your actual data and its layout. One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

 

[Jym396]

Re: VBA Syntax Help

OK...my apologies to all. I really should think it through more completely and explain what my expected end result should be.

After the next...and before the ActiveCell.Offset(0, 3).Select
Check the cell, 1 cell to the left of the date.
If it is blank...proceed.
If it is not blank...abort.

 

[MARK858]

Re: VBA Syntax Help

That is not explaining what you are doing, I already explained what will happen if it is outside the loop.
I will let you try once more before I leave the thread. Btw it was 2 to the left earlier which is what the 2 in the line below does.

If Cl.Offset(, -[COLOR="#FF0000"]2[/COLOR]) <> "" Then Exit Sub

 

[Fluff]

Re: VBA Syntax Help

Rather than trying to explain with pseudo code, could you please explain what you are trying to achieve using normal language.

 

[Jym396]

Re: VBA Syntax Help

Thank you Rick. Admonishment humbly taken. I am truly thankful for all of the help I receive here. I am guilty of all of the charges you stated. I will do better. (I so envy you as a MVP). With all of the languages out there, I have a passion for Excel.
Previously when asked "Do you know Excel? Would respond with "Yes. I am very good with Excel." I learned that the more I know, not only in Excel, the less I know. Now I respond with "I love Excel. Tell me what you need and I can help."

 

[Jym396]

Re: VBA Syntax Help

OK.
When the macro finds today, BEFORE it enters the current time, check the cell 'closed' cell to see if to proceed or not.

 

[MARK858]

Re: VBA Syntax Help

Still not detailed enough but I will post 1 last guess.

The code below loops through C404 to C464. If it finds a cell in column C that has today's date and the cell in column B of the same row is blank then it puts the current time in column F on the same row and exits the sub.

Sub TodayOut2()    
    Dim myCell As Range
    For Each myCell In ActiveSheet.Range("C404:C464")
        If myCell.Value = Date And myCell.Offset(, -1) = "" Then
            myCell.Offset(0, 3) = Format(Now, "hh:mm am/pm")
            Exit Sub
        End If
    Next
End Sub

 

[Jym396]

Re: VBA Syntax Help

Mark! Mark!! Mark!!! Thank you! Thank you!! Thank you!!! Deep apologies for my ignorance and kudos for your persistence. It works!!! It bails out when that cell is not blank and continues if it is not.

Now on my part is to just get a OKonly msgbox in there to tell the user it is not going to update the time...I WILL figure it out!

Thanks again,
-Jim

 

[MARK858]

Re: VBA Syntax Help

Glad we got there (eventually). Btw, code is almost the same as the one I posted in post number 7 that you didn't comment on.
Message box below...

Sub TodayOut3()    '
   Dim myCell As Range
    For Each myCell In ActiveSheet.Range("C404:C464")
        If myCell.Value = Date And myCell.Offset(, -1) = "" Then
            myCell.Offset(0, 3) = Format(Now, "hh:mm am/pm")
            Exit Sub
        End If
    Next
    MsgBox "Time not updated"
End Sub

 

[Jym396]

Re: VBA Syntax Help

Simply amazing Mark. Works like greased lightning. Can't thank you enough.