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

 

[MARK858]

Re: VBA Syntax Help

The formula line is exactly the same line you said worked in post number 8, it hasn't changed

 

[MARK858]

Re: VBA Syntax Help

Question, do you only get the error when you are stepping through the code with F8 and not when doing a straight run?

 

[Fluff]

Re: VBA Syntax Help

@Jym396
A few questions.
1) do you want the formula inserted into col F?
2) if yes to above should it be done before or after checking if col E is blank?
3) You've said

I think it crashes on the formula statement.

Do you get any error messages? If so click debug.
Then please tell us the error message & which line is highlighted in yellow

 

[Jym396]

Re: VBA Syntax Help

The problem is the formula. I only used it sheet to see if I could today and then offset it. It doesn't belong in the VBA. I THINK I'm looking for a :

If ....code... then

Sorry such a poor VBA programmer.

 

[Rick Rothstein]

Re: VBA Syntax Help

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

The OP used the variable name "Cell" which is not a VBA keyword (no "s" on the end). While "Cell" is really close to the VBA keyword "Cells", and I know many frown on using it, I tend to use "Cell" as a variable name all the time as I think it makes the code more self-documenting than a name like "Cl" does. If I were going to suggest an alternative, I might conside "Cel" as that is close enough to the actual word to still make it self-documenting (although my tendency for being somewhat obsessive-compulsive make it hard for me to use that variable name within my own code).

 

[MARK858]

Re: VBA Syntax Help

The problem is the formula. I only used it sheet to see if I could today and then offset it. It doesn't belong in the VBA.

What do you actually want to put in the cell 3 to the right of the cell with today's date if not the formula?

Explain very carefully exactly what you want to achieve as an end result.

 

[Jym396]

Re: VBA Syntax Help

Luv the name..Fluff.
1) No. I do not want the formula inserted.
2)It should be done right after the 'next'.

Abort sub if cell is not blank.

I think, an if then.
If blank...proceed.
If not blank...abort.

 

[Fluff]

Re: VBA Syntax Help

The OP used the variable name "Cell" which is not a VBA keyword (no "s" on the end). While "Cell" is really close to the VBA keyword "Cells", and I know many frown on using it, I tend to use "Cell" as a variable name all the time as I think it makes the code more self-documenting than a name like "Cl" does. If I were going to suggest an alternative, I might conside "Cel" as that is close enough to the actual word to still make it self-documenting (although my tendency for being somewhat obsessive-compulsive make it hard for me to use that variable name within my own code).

Must admit I always thought that both Cell & Cells were keywords. Thanks for the correction.

 

[MARK858]

Re: VBA Syntax Help

Luv the name..Fluff.
1) No. I do not want the formula inserted.
2)It should be done right after the 'next'.

Abort sub if cell is not blank.

Your posts are very confusing, what do you actually want to happen in the loop?
If you put the test outside the loop then it will just loop to C464 then exit if A464 isn't blank not when the cell with today's date is found.

It should be done right after the 'next'.

What is "it" in the above quote?

Again.

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

 

[Rick Rothstein]

Re: VBA Syntax Help

Must admit I always thought that both Cell & Cells were keywords. Thanks for the correction.

I guess I should mention that, technically, "Cells" is not a VBA keyword, rather, it is an Excel object property name (there is that tendency of mine to be somewhat obsessive-compulsive rearing its ugly head again) although I will admit this is a difference without a real distinction give the underlying caution you were trying to give to the OP.