Solved

Excel VBA

Posted on 2014-02-20
10
254 Views
Last Modified: 2014-02-20
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
0
Comment
Question by:W.E.B
  • 5
  • 5
10 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39874449
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
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39874463
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
0
 

Author Comment

by:W.E.B
ID: 39874529
Hi Kevin,
can you please show me how ,
I attached the first part of my code.

thanks
sample.txt
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39874538
See code above. It's ready to go.

Kevin
0
 

Author Comment

by:W.E.B
ID: 39874601
I get error
compile error
constant expression required.

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

thanks
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39874706
Copy the code above as-is. Don't copy parts of it.

Kevin
0
 

Author Comment

by:W.E.B
ID: 39875280
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
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 300 total points
ID: 39875293
Replace the assignment statement with this:

    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), """,""") & """})"

Open in new window


Kevin
0
 

Author Comment

by:W.E.B
ID: 39875344
thank you, thank you,
0
 

Author Closing Comment

by:W.E.B
ID: 39875346
Superb,
thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now