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
  • Last Modified:

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
Asked:
Saqib Husain, Syed
  • 5
  • 3
  • 2
  • +3
1 Solution
 
tigin44Commented:
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
 
Arabia_vnCommented:
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
 
HainKurtSr. System AnalystCommented:
For Row = 1 To 20
-->
For Row = 1 To 19
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Arabia_vnCommented:
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
 
patrickabCommented:
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
 
patrickabCommented:
ssaqibh,

Please upload your file and let us know what you are wanting to achieve.

Patrick
0
 
HainKurtSr. 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
 
patrickabCommented:
ssaqibh,

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

Patrick
0
 
Saqib Husain, SyedEngineerAuthor 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
 
patrickabCommented:
Saqib,

Thanks for your responses.

Patrick
0
 
patrickabCommented:
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
 
Saqib Husain, SyedEngineerAuthor 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
 
zorvek (Kevin Jones)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
 
Saqib Husain, SyedEngineerAuthor Commented:
Thanks again Kevin

Saqib
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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