Link to home
Start Free TrialLog in
Avatar of W.E.B
W.E.B

asked on

Excel VBA

Hello,
can you please help,

how do I break this long line into multiple lines.

I tried the (space then _ then enter ) at end of a line, it didn't work,
I tried the (" & _ then enter ) at end of a line, it didn't work,

error, Expected line number or end statement

    Const Fmla1 = "=OR(A2=(""1300"",""1302"",""1303"",""1304"",""1305"",""1310"",""1311"",""1314"",""1315"",""1316"",""1318"",""1320"",""1321"",""1322"",""1323"",""1324"",""1325"",""1326"",""1330"",""1336"",""1338"",""1340"",""1341"",""1345"",""1350"",""1353"",""1355"",""1357"",""1358"",""1360"",""1362"",""1365"",""1370"",""1378"",""1379"",""1380"",""1381"",""1382"",""1385"",""3500"",""3506"",""3507"",""3514"",""3521"",""3528"",""3534"",""3535"",""3542"",""3545"",""3600"",""3601"",""3608"",""3609"",""3610"",""3611"",""3612"",""3613"",""3614"",""3616"",""3624"",""3625"",""3626"",""3627"",""3628"",""3629"",""3630"",""3631"",""3632"",""3637"",""3642"",""3643"",""3648"",""3649"",""3650"",""3651"",""3653"",""3654"",""3655"",""3656"",""3659"",""3660"",""3662"",""3664"",""3665"",""3666"",""3700"",""3702"",""3703"",""3704"",""3705"",""3708"",""3709"",""3710"",""3715"",""3720"",""3725"",""3727"",""3730"",""3735"",""3736"",""3737"",""3738"",""3745"",""3750"",""3751"",""3754"",""3760"",""3761"",""3762"",""3763"" , ""3764"",""3765"",""3766"",""3767"",""3771"",
""3777"",""3778"",""3779"",""3781"",""3785"",""3786"",""3794"",""3795"",
""3796"",""3844"",""3848"",""3851"",""3854"",""3857""))"


thanks
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image
You can't break a constant up with line breaks (space followed by an underscore).

Alternatives: store the value in a cell and load it into a variable or build it in code.

Kevin
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image
Building it:

    Dim Fmla1 As String
    Fmla1 = "=OR(A2=(""" & Join(Array(1300, 1302, 1303, 1304, 1305, 1310, 1311, 1314, 1315, 1316, 1318, 1320, 1321, 1322, 1323, 1324, 1325, 1326, 1330, 1336, 1338, 1340, 1341, 1345, 1350, 1353, 1355, 1357, 1358, 1360, 1362, 1365, 1370, 1378, 1379, 1380, 1381, 1382, 1385, 3500, 3506, 3507, 3514, 3521, 3528, 3534, 3535, 3542, 3545, 3600, 3601, 3608, 3609, 3610, 3611, 3612, 3613, 3614, 3616, 3624, 3625, 3626, 3627, 3628, 3629, 3630, 3631, 3632, 3637, 3642, 3643, 3648, 3649, 3650, 3651, 3653, 3654, 3655, 3656, 3659, 3660, 3662, 3664, 3665, 3666, 3700, 3702, 3703, 3704, 3705, 3708, 3709, 3710, 3715, 3720, 3725, 3727, 3730, 3735, 3736, 3737, 3738, 3745, 3750, 3751, 3754, 3760, 3761, 3762, 3763, 3764, 3765, 3766, 3767, 3771, 3777, 3778, 3779, 3781, 3785, 3786, 3794, 3795, 3796, 3844, 3848, 3851, 3854, 3857), """,""") & """))"
    Debug.Print Fmla1

Open in new window


Kevin
Avatar of W.E.B
W.E.B

ASKER

Hi Kevin,
can you please show me how ,
I attached the first part of my code.

thanks
sample.txt
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image
See code above. It's ready to go.

Kevin
Avatar of W.E.B
W.E.B

ASKER

I get error
compile error
constant expression required.

= "=OR(A2=(""" & Join(Array(

thanks
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image
Copy the code above as-is. Don't copy parts of it.

Kevin
Avatar of W.E.B
W.E.B

ASKER

HI Kevin,
I have the full code,
but I still get an error.

error : run-time error 1004
application-defined or object-defined error

debug on this line
    wksTemp.Range("Y2").Formula = Fmla1

sample 2 attached is the full code.

thanks again.
sample-2.txt
ASKER CERTIFIED SOLUTION
Avatar of zorvek (Kevin Jones)
zorvek (Kevin Jones)
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of W.E.B
W.E.B

ASKER

thank you, thank you,
Avatar of W.E.B
W.E.B

ASKER

Superb,
thanks