Solved

# Modify code

Posted on 2009-12-25
190 Views
I got this code from this question

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_24999085.html#a26121801

Public Sub Routine()
Dim Row As Long
Dim LastBlankRow As Long
For Row = 1 To 20
If Len(Cells(Row, 2)) = 0 Then
LastBlankRow = Row
Else
If Len(Cells(Row + 1, 2)) = 0 Then
If Row - LastBlankRow = 1 Then
X Cells(Row, 1)
Else
Y Cells(LastBlankRow + 1, 1), Cells(Row, 1)
End If
End If
End If
Next Row
End Sub

I want this code modified so that the code should not check the 21st row because I do not have row 21 and the program gives an error if there is data in the 20th row.

Saqib
0
Question by:Saqib Husain, Syed

LVL 26

Expert Comment

Public Sub Routine()
Dim Row As Long
Dim LastBlankRow As Long
For Row = 1 To 19
If Len(Cells(Row, 2)) = 0 Then
LastBlankRow = Row
Else
If Len(Cells(Row + 1, 2)) = 0 Then
If Row - LastBlankRow = 1 Then
X Cells(Row, 1)
Else
Y Cells(LastBlankRow + 1, 1), Cells(Row, 1)
End If
End If
End If
Next Row
End Sub
0

LVL 8

Expert Comment

For Row = 1 To 20
If Len(Cells(Row, 2)) = 0 Then
LastBlankRow = Row
Else
If (Row<20)
If Len(Cells(Row + 1, 2)) = 0 Then
If Row - LastBlankRow = 1 Then
X Cells(Row, 1)
Else
Y Cells(LastBlankRow + 1, 1), Cells(Row, 1)
End If
End If
End If
End If
0

LVL 51

Expert Comment

For Row = 1 To 20
-->
For Row = 1 To 19
0

LVL 8

Expert Comment

For Row = 1 To 20
-->
For Row = 1 To 19

-->Wrong cause that way it does not check row 20th . Better use one more if clause like i did.
0

LVL 45

Expert Comment

ssaqibh,

1. You should not be using a function name like Row as a variable. It's poor practise because if you need to use that function as a function you can't. Apart from that it's confusing to read the code.

2. What are X and Y ? They are doing nothing in the code. Are they cell locations? If they are cell locations then they should have declared as such earlier in the code. As it is these two lines of code do nothing:

X Cells(Row, 1)
Y Cells(LastBlankRow + 1, 1), Cells(Row, 1)

3. If you had used 'Option Explicit' at the top of your code, you would have received an error message when you attempted to Compile the project. It's a useful check and helps you produce error-free code. However all variables must be declared if you use Option Explicit.

4. The code only checks rows  to 20 so I'm not too sure what row 21 has got to do with the problem. Apart from which this is impossible 'because I do not have row 21'. You will always have row 21 in every worksheet. You might not be using it or it might be hidden, but you will always have row 21.

Patrick
0

LVL 45

Expert Comment

ssaqibh,

Patrick
0

LVL 51

Expert Comment

ok then do this ;)

Y Cells(LastBlankRow + 1, 1), Cells(Row, 1)
-->
if (LastBlankRow <20) Then Y Cells(LastBlankRow + 1, 1), Cells(Row, 1)
0

LVL 45

Expert Comment

ssaqibh,

I've now looked back at the previous question and see that X and Y are subs.

Patrick
0

LVL 43

Author Comment

tigin44, Arabia_vn: your codes do not call the subroutines when there is data in the last row

HainKurt: the error occurs in the first line after the first ELSE statement

Patrick:
1. This function has been provided by Kevin who tops the overall hall of fame in excel so i would not comment on the use of "row" as a variable.
2. You have already discovered that X and Y are subroutines.
3. I never use Option Explicit as a matter of choice.
4. As I have mentioned in response to HainKurt's proposal, the first line after the ELSE statement checks for the 21st row. Actually these rows belong to a dimensioned variable and not to an excel spreadsheet. Nevertheless the row count refers to a limit which could be row 65536 for Excel 2003.

Saqib
0

LVL 45

Expert Comment

Saqib,

Patrick
0

LVL 45

Expert Comment

Saqib,

>1. This function has been provided by Kevin who tops the overall hall of fame in excel so i would not comment on the use of "row" as a variable.

My comment is not personal. It is simply practical. I stand by what I have said about not using VBA function names as the names of variables.

>3. I never use Option Explicit as a matter of choice.

Each to their own - it all depends on your approach to coding.

Patrick
0

LVL 43

Author Comment

Hi all,

I could have done this myself but I know I would have taken time to do it. That is why I put the question here so that someone could help me while I use that time to attend to the rest of a large program. This way I save time and someone gets the points. But I end up spending more time than that testing people's untested snippets and discussing the programming concepts.

I am still interested in the solution because I am still in the middle of the program and have time to wait for a solution.

So please, only post if you have a solution. And that too one which works.

Saqib

0

LVL 81

Accepted Solution

Saqib,

Fixed and tested:

Public Sub Routine()
Dim Row As Long
Dim LastBlankRow As Long
Dim CallRoutine As Boolean
For Row = 1 To 20
CallRoutine = False
If Len(Cells(Row, 2)) = 0 Then
LastBlankRow = Row
Else
If Row < 20 Then
If Len(Cells(Row + 1, 2)) = 0 Then CallRoutine = True
Else
CallRoutine = True
End If
End If
If CallRoutine Then
If Row - LastBlankRow = 1 Then
X Cells(Row, 1)
Else
Y Cells(LastBlankRow + 1, 1), Cells(Row, 1)
End If
End If
Next Row
End Sub

>You should not be using a function name like Row as a variable. It's poor practise because if you need to use that function as a function you can't. I stand by what I have said about not using VBA function names as the names of variables.

Although "Row" is not a function name, using actual VBA function names as variable names is not a good idea as it prevents using that VBA function within the scope of the variable declaration. I agree with Patrick.

In this case "Row" is not a function, it's a property of the Range object. So it can be used as a variable name without any consequences. So can "Cell" ;-)

>Apart from that it's confusing to read the code.

How so? "Row" is a property of the Range object and hence cannot be used on it's own as a property.

>I never use Option Explicit as a matter of choice.

I would highly recommend starting the practice. It saves a lot of headache when debugging.

Kevin
0

LVL 43

Author Comment

Thanks again Kevin

Saqib
0

## Featured Post

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.