row empty end if...

[edlim85]

hi all,

Im using vba to go through the rows, I need to tell excel if the cell(a, 9).Value is empty...then Next a.

how should i code that? please advise.

For a = 1 to LR

'if cell is empty next a.

next a


regards
Edmund

 

[Weaver]

Depends what you want to do if it's empty.

I'd advise against trying to increment your loop within the loop itself tho.

Are you able to derive a value for LR or is that part of the query too?

lr = cells(rows.count,9).end(xlup).row
for a = 1 to lr
  if isempty(cells(a,9)) then
    'do something if it's empty
  end if
next a

 

[VBACO]

This should do what you want.

For a = 1 to LR

If Worksheets("Sheet1").Range("A"&a)="" Then Goto Skip
'Do Stuff
Skip:
next a

 

[Weaver]

This should do what you want.

For a = 1 to LR
 
If Worksheets("Sheet1").Range("A"&a)="" Then Goto Skip
'Do Stuff
Skip:
next a

Sorry, but why would you use a goto for this? If you wanted to not do something if the cell is empty then

For a = 1 to LR

If Worksheets("Sheet1").Range("A"&a)<>"" Then 
'Do Stuff
next a

is better

 

[VBACO]

I guess I dont really understand why one way would be better then the other. Can you advise?

 

[Weaver]

In a 5 line code snippet, you may have a point (apart from the fact that the if..then structure allows you to deal with the scenario without it), but most programmers I speak to accept that using goto when there are more structured alternatives is easier to debug, especially in larger chunks of code, so apart from error trapping, I'd say it was best practice to avoid using it altogether. Feel free to disagree.

 

[VBACO]

I am not wanting to disagree, moreso to learn. I am totally self taught/web taught. I can get about anything done, sometimes its not the best method though.

 

[Weaver]

Goto is just a bad habit to get into. You'll need it for error trapping, but that should be it. I'm completely self taught also, and yes, the web was the best invention going for learning new techniques.

Anyway, apologies to edlim95 for hijacking your thread. You may have to scroll up the page a little to find your solution.

 

[edlim85]

hi guys,

Thank you so much for your prompt replies and your solutions

Im 1mth old in excel vba. I have a few questions to ask

1)I went to research and i saw usage in <> ... "" ... does this mean the value within?

2)Say i want to do nothing and go next row if the row/cell is empty how do i go about doing it?

3) does this 2 codes do the exact same thing
if isempty(cells(a,9)) then
If (cells(a,9)) is nothing then


Thanks guys

 

[Weaver]

<>

means "not equal to"

So if your code wants to only do somethin if the cell is NOT empty, it's the ideal test.

As for isempty(), this might be better if you want to differentiate cells with nothing in from cells containing formulas that evaluate to an empty string.