Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 197

# Modify code

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
Saqib Husain, Syed
• 5
• 3
• 2
• +3
1 Solution

Commented:
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

Commented:
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

Sr. System AnalystCommented:
For Row = 1 To 20
-->
For Row = 1 To 19
0

Commented:
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

Commented:
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

Commented:
ssaqibh,

Patrick
0

Sr. System AnalystCommented:
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

Commented:
ssaqibh,

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

Patrick
0

EngineerAuthor Commented:
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

Commented:
Saqib,

Patrick
0

Commented:
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

EngineerAuthor Commented:
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

ConsultantCommented:
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

EngineerAuthor Commented:
Thanks again Kevin

Saqib
0

## Featured Post

• 5
• 3
• 2
• +3
Tackle projects and never again get stuck behind a technical roadblock.