Error using "If Not Then" in a loop

[kanonathena]

Hi all

I'm trying to use a macro to update the comments on a range, the comments are picked up from another sheet named "Data" using Vlookup.

Basically from Cells(3,21) to Cells(3,32) are months, the cells below from Cells(4,21) to Cells(4,32) are data for the months, I need to insert comments on each of the data cells. My idea is to using a loop to update Cells(1,1) with comment picked up using Vlookup and then insert the comment to the cells.

Every time I run the Macro, the existing comment is cleared first. That's what I got with Macro below.

Please Login or Register  to view this content.

Now what I want is that if the new comment picked up is empty (cells(1,1)) then after clearing the existing comment I don't want to add a empty comment box to the data cell. I want to continue the loop. So I insert "If Not IsEmpty(sText) Then" after the comment clearing line, but excel gives me a "Next without For" error. Could anyone tell me where went wrong?

Another question is that is it possible to reference cell in another sheet directly using Vlookup without having to use a proxy cell like cells(1,1)?

The third question is that I don't understand what "With WorksheetFunction" doing here? Why it it outside the loop and what is "With" command anyway?

Thank you very much.

[ajryan88]

Hi,

Add

Please Login or Register  to view this content.

before

Please Login or Register  to view this content.

That's it!

Hope this helps

[ajryan88]

To answer your second and third questions:

2. I'm not sure what you mean by this question...could you please try rewording it?
3. The "With WorksheetFunction" line doesn't need to be there. The "With" syntax is used as a shortcut to avoid the coder having to repeat the same keywords over again. For example

Please Login or Register  to view this content.

could be simplfied to

Please Login or Register  to view this content.

with the use of a "With" statement. However, because you have only called one worksheet function in your "With" statement, there is no need for it to be simplified at all (in fact, there is more typing involved in this case to use a "With" statement in the first place! )

I hope this explains a lot to you

[kanonathena]

Thanks, ajryan88. That helped a great deal.

Regarding my second question, at the moment I first put comment in Cells(1,1) and then refer to the comment using

Please Login or Register  to view this content.

and Add "Stext" to the destination cell using

Please Login or Register  to view this content.

Is there a way to get rid of middle step of using sText and instead do only one step doing something like this

Please Login or Register  to view this content.

[ajryan88]

Hi,

"Else" is an optional statement, there is no NEED to have one in an If Then...End If statement.

To your original second question, yes you can skip over the middle step...just use the code that you have suggested yourself above:

Please Login or Register  to view this content.

Hope this helps

[protonLeah]

kanonathena

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[kanonathena]

Hi, I did some changing to the code. Now there seems to be a problem with the line "If Not IsEmpty(sText) Then". The code is not recognizing if sText is empty or not. sText is a string.

Please Login or Register  to view this content.

[ajryan88]

Hi,

I probably should have realised this earlier, but IsEmpty will only return True if sText is uninitialised or explicitly set to Empty, otherwise it returns False.

You should try replacing

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

This will also produce an error if there is a VLOOKUP error, so you may want to catch any errors there too.

Hope this helps

[kanonathena]

Hi,

I probably should have realised this earlier, but IsEmpty will only return True if sText is uninitialised or explicitly set to Empty, otherwise it returns False.

So string has a different "emptiness" from a cell like Cells(1,1)?

[ajryan88]

That is correct