To many Continuations line in VB problem

hatstand

Well-known Member
Joined
Mar 17, 2005
Messages
764
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I am modifying some code for a user form. But have came across a (To many line continuations) message.

The “And r Offset” lines are causing the errors.

Code:
Do
            'New-----------------------------------------------------
            If r.Offset(0, 1) = NumRepl _
            And r.Offset(0, 2) = BARepl _
            And r.Offset(0, 3) = PriceRepl _
                        And r.Offset(0, 4) = testRepl _
                        And r.Offset(0, 5) = hoursRepl _
                        And r.Offset(0, 6) = weeklyhoursRepl
                        And r.Offset(0, 7) = typeRepl _
                        And r.Offset(0, 8) = pcteleveldateRepl _
                        And r.Offset(0, 9) = sptelleveldateRepl _
                        And r.Offset(0, 10) = pcpaperleveldateRepl
                        And r.Offset(0, 11) = sppaperleveldateRepl _
                        And r.Offset(0, 12) = skrmleveldateRepl
                        And r.Offset(0, 13) = pcspecialistleveldateRepl _
                        And r.Offset(0, 14) = spspecialistleveldateRepl _
                        And r.Offset(0, 15) = teamleaderleveldateRepl _
                        And r.Offset(0, 16) = skskleveldateRepl _
                        And r.Offset(0, 17) = pctelevelRepl _
                        And r.Offset(0, 18) = sptellevelRepl _
                        And r.Offset(0, 19) = pcpaperlevelRepl _
                        And r.Offset(0, 20) = sppaperlevelRepl _
                        And r.Offset(0, 21) = skrmlevelRepl _
                        And r.Offset(0, 22) = pcspecialistlevelRepl _
                        And r.Offset(0, 23) = spspecialistlevelRepl _
                        And r.Offset(0, 24) = teamleaderlevelRepl _
                        And r.Offset(0, 25) = sksklevelRepl Then

                       r = Me.txtDescription
            r.Offset(0, 1) = NNumRepl
            r.Offset(0, 2) = NBARepl
            r.Offset(0, 3) = NPriceRepl
                            
                            r.Offset(0, 4) = NtestRepl
                            
                            r.Offset(0, 4) = NpctellevelRepl
            Exit Do

Could I just have once continuous line?
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
At that rate, I would imagine you'd run into an error from the statement (line) being too long.

All this means is that you need to find a different/better way of doing things. What exactly are you trying to do?
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
One completely and utterly untested alternative idea:

Code:
Dim i As Long, arrVars As Variant, blChk As Boolean

arrVars = Array(NumRep1, BARep1, PriceRep1, testRep1, hoursRep1, weeklyhoursRep1, _
typeRep1, pcteleveldateRep1, sptelleveldateRep1, pcpaperleveldateRep1, _
skrmleveldateRep1, pcspecialistleveldateRep1, teamleaderleveldateRep1, _
skskleveldateRep1, pctelevelRep1, sptellevelRep1, pcpaperlevelRep1, _
sppaperlevelRep1, skrmlevelRep1, pcspecialistlevelRep1, spspecialistlevelRep1, _
teamleaderlevelRep1, sksklevelRep1)

For i = 1 To 25
    If r.Offset(0, i) = arrVars(i - 1) Then
        blChk = True
    Else
        blChk = False
    End If
    If blChk = False Then
        MsgBox "The items do not match"
        Exit Sub
    End If
Next i

'if all items match, code continues here
r = Me.txtDescription
r.Offset(0, 1) = NNumRepl
r.Offset(0, 2) = NBARepl
r.Offset(0, 3) = NPriceRepl
r.Offset(0, 4) = NpctellevelRepl
 

hatstand

Well-known Member
Joined
Mar 17, 2005
Messages
764
Office Version
  1. 2016
Platform
  1. Windows
Hi, thanks for the reply.

just as I was about to post how I managed to do it. You beat me to it.

I ended up doubling the line length by taking out every other _. It works so I can move onto other problems the form is posing.
 

Kaps_mr2

Well-known Member
Joined
Jul 5, 2008
Messages
1,538
One solution would be to divide your numerous if conditions into groups - with individual outputs as Boolean variables. Then do an overall IF condition on the Boolean variables - see the code below where I have started this for you. Kaps

Code:
Dim  Group1 As Boolean
Dim  Group2 As Boolean
Dim  Group3 As Boolean
 
If          r.Offset(0,1) = NumRepl _ 
      and r.Offset(0,2) = BARep l_
      and r.Offset(0,3) = PriceRepl _
      and r. Offset(0,4) = testRepl _
Then Group1 = True
End If
 
If    r.Offset(0,5) = hoursRepl _ 
    and r.Offset(0,6) = weeklyhoursRepll_
    and r.Offset(0,7) = PriceRepl _
    and r. Offset(0,8) = pcteleveldateRepl 
Then Group2 = True
End If
 
If ((Group1 = True) and (Group2 = True)) Then
 
 
             r = Me.txtDescription
            r.Offset(0, 1) = NNumRepl
            r.Offset(0, 2) = NBARepl
            r.Offset(0, 3) = NPriceRep
            r.Offset(0, 4) = NtestRepl
            r.Offset(0, 4) = NpctellevelRepl
End If
 
            Exit Do
<!-- / message --><!-- edit note -->
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
Yes, that is the most obvious way to resolve the problem. {grin}

Hi, thanks for the reply.

just as I was about to post how I managed to do it. You beat me to it.

I ended up doubling the line length by taking out every other _. It works so I can move onto other problems the form is posing.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,610
Messages
5,625,797
Members
416,138
Latest member
Pizzaman22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top