How to enter this multi-line formula?

R

rollingzep

Board Regular
Joined
Nov 18, 2013
Messages
214
Office Version
  1. 365
Platform
  1. Windows
<p>

I am trying to enter this lengthy formula in multi line but it is incorrect
How to enter the correct way?
I tried a space and an underscore, and as below but it is incorrect </p>

<code>
lrow = ws.Cells(Rows.Count, "BC").End(xlUp).Row
ws.Range("BC2").Formula =
"=IF(BE2=""A-0"",""1"",IF(BE2=""A-1"",""1"",IF(BE2=""A-2"",""1"",IF(BE2=""A-3"",""1"", " & _
"IF(BE2=""A-4"",""1"",IF(BE2=""A-5"",""1"",IF(BE2=""S-1"",""1"",IF(BE2=""S-2"",""1"", " & _
"IF(BE2=""S-3"",""1"",IF(BE2=""S-4"",""1"",IF(BE2=""G-1"",""2A"",IF(BE2=""G-2"",""2A"", " & _
"IF(BE2=""G-3"",""2A"",IF(BE2=""S-5"",""2A"",IF(BE2=""S-6"",""2A"",IF(BE2=""S-7"",""2A"", " & _
"IF(BE2=""E-1"",""2B"",IF(BE2=""E-2"",""2B"",IF(BE2=""IG-1"",""2B"",IF(BE2=""IG-2"",""2B"", " & _
"IF(BE2=""S-8"",""N"",IF(BE2=""G-4"",""N"",IF(BE2=""E-3"",""N"",IF(BE2=""E-4"",""N"", " & _
"IF(BE2=""IG-3"",""N"",IF(BE2=""IG-4"",""N"",IF(BE2=""IG-5"",""N"",IF(BE2=""IG-6"",""N"", " & _
"IF(BE2=""IG-7"",""N"",IF(BE2=""N-1"",""N"",IF(BE2=""N-2"",""N"",IF(BE2=""N-3"",""N"", " & _
"IF(BE2=""N-4"",""N"",IF(BE2=""N-5"",""N"",IF(BE2=""N-6"",""N"",IF(BE2=""N-7"",""N"", " & _
"IF(BE2=""L-1"",""N"",IF(BE2=""L-2"",""N"",IF(BE2=""L-3"",""N"",IF(BE2=""L-4"",""N"", " & _
"IF(BE2=""L-5"",""N"",IF(BE2=""L-6"",""N"",IF(BE2=""L-7"",""N"",IF(BE2=""L-8"",""N"", " & _
"IF(BE2=""L-9"",""N"",IF(BE2=""L-10"",""N"",IF(BE2=""L-11"",""N"", " & _
"IF(BE2=""Y-1"",""N"",IF(BE2=""Y-2"",""N"",IF(BE2=""Y-3"",""N"",IF(BE2=""C-1"",""N"", " & _
"IF(BE2=""Z-1"",""N""))))))))))))))))))))))))))))))))))))))))))))))))))))"
</code>
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
VBA-part
ws.Range("BC2").FormulaR1C1 = "=VLOOKUP(R2C57,Tabel1,2,0)"
add a table "Tabel1" in your workbook like this one, but you still have to add the last lines.
Map1
ABC
1
2
3BE2VALUE
4A-01
5A-11
6A-21
7A-31
8A-41
9A-51
10S-11
11S-21
12S-31
13S-41
14G-12A
15G-22A
16
17
18
19
20
21
Blad1
 
Upvote 0
BSALV said:
VBA-part
ws.Range("BC2").FormulaR1C1 = "=VLOOKUP(R2C57,Tabel1,2,0)"
add a table "Tabel1" in your workbook like this one, but you still have to add the last lines.
Map1
ABC
1
2
3BE2VALUE
4A-01
5A-11
6A-21
7A-31
8A-41
9A-51
10S-11
11S-21
12S-31
13S-41
14G-12A
15G-22A
16
17
18
19
20
21
Blad1
Click to expand...

The headers of this Table need to be the BE2 and Value?

1657115334097.png
 
Upvote 0
Whilst you would be better off (IMO) using a lookup table as suggested by BSALV the problem with your code is that you are missing a space & underscore after the formula line like
VBA Code: 
lrow = ws.Cells(Rows.Count, "BC").End(xlUp).Row
   ws.Range("BC2").Formula = _
"=IF(BE2=""A-0"",""1"",IF(BE2=""A-1"",""1"",IF(BE2=""A-2"",""1"",IF(BE2=""A-3"",""1"", " & _
"IF(BE2=""A-4"",""1"",IF(BE2=""A-5"",""1"",IF(BE2=""S-1"",""1"",IF(BE2=""S-2"",""1"", " & _
"IF(BE2=""S-3"",""1"",IF(BE2=""S-4"",""1"",IF(BE2=""G-1"",""2A"",IF(BE2=""G-2"",""2A"", " & _
"IF(BE2=""G-3"",""2A"",IF(BE2=""S-5"",""2A"",IF(BE2=""S-6"",""2A"",IF(BE2=""S-7"",""2A"", " & _
"IF(BE2=""E-1"",""2B"",IF(BE2=""E-2"",""2B"",IF(BE2=""IG-1"",""2B"",IF(BE2=""IG-2"",""2B"", " & _
"IF(BE2=""S-8"",""N"",IF(BE2=""G-4"",""N"",IF(BE2=""E-3"",""N"",IF(BE2=""E-4"",""N"", " & _
"IF(BE2=""IG-3"",""N"",IF(BE2=""IG-4"",""N"",IF(BE2=""IG-5"",""N"",IF(BE2=""IG-6"",""N"", " & _
"IF(BE2=""IG-7"",""N"",IF(BE2=""N-1"",""N"",IF(BE2=""N-2"",""N"",IF(BE2=""N-3"",""N"", " & _
"IF(BE2=""N-4"",""N"",IF(BE2=""N-5"",""N"",IF(BE2=""N-6"",""N"",IF(BE2=""N-7"",""N"", " & _
"IF(BE2=""L-1"",""N"",IF(BE2=""L-2"",""N"",IF(BE2=""L-3"",""N"",IF(BE2=""L-4"",""N"", " & _
"IF(BE2=""L-5"",""N"",IF(BE2=""L-6"",""N"",IF(BE2=""L-7"",""N"",IF(BE2=""L-8"",""N"", " & _
"IF(BE2=""L-9"",""N"",IF(BE2=""L-10"",""N"",IF(BE2=""L-11"",""N"", " & _
"IF(BE2=""Y-1"",""N"",IF(BE2=""Y-2"",""N"",IF(BE2=""Y-3"",""N"",IF(BE2=""C-1"",""N"", " & _
"IF(BE2=""Z-1"",""N""))))))))))))))))))))))))))))))))))))))))))))))))))))"
 
Upvote 0
Fluff said:
Whilst you would be better off (IMO) using a lookup table as suggested by BSALV the problem with your code is that you are missing a space & underscore after the formula line like
VBA Code: 
lrow = ws.Cells(Rows.Count, "BC").End(xlUp).Row
   ws.Range("BC2").Formula = _
"=IF(BE2=""A-0"",""1"",IF(BE2=""A-1"",""1"",IF(BE2=""A-2"",""1"",IF(BE2=""A-3"",""1"", " & _
"IF(BE2=""A-4"",""1"",IF(BE2=""A-5"",""1"",IF(BE2=""S-1"",""1"",IF(BE2=""S-2"",""1"", " & _
"IF(BE2=""S-3"",""1"",IF(BE2=""S-4"",""1"",IF(BE2=""G-1"",""2A"",IF(BE2=""G-2"",""2A"", " & _
"IF(BE2=""G-3"",""2A"",IF(BE2=""S-5"",""2A"",IF(BE2=""S-6"",""2A"",IF(BE2=""S-7"",""2A"", " & _
"IF(BE2=""E-1"",""2B"",IF(BE2=""E-2"",""2B"",IF(BE2=""IG-1"",""2B"",IF(BE2=""IG-2"",""2B"", " & _
"IF(BE2=""S-8"",""N"",IF(BE2=""G-4"",""N"",IF(BE2=""E-3"",""N"",IF(BE2=""E-4"",""N"", " & _
"IF(BE2=""IG-3"",""N"",IF(BE2=""IG-4"",""N"",IF(BE2=""IG-5"",""N"",IF(BE2=""IG-6"",""N"", " & _
"IF(BE2=""IG-7"",""N"",IF(BE2=""N-1"",""N"",IF(BE2=""N-2"",""N"",IF(BE2=""N-3"",""N"", " & _
"IF(BE2=""N-4"",""N"",IF(BE2=""N-5"",""N"",IF(BE2=""N-6"",""N"",IF(BE2=""N-7"",""N"", " & _
"IF(BE2=""L-1"",""N"",IF(BE2=""L-2"",""N"",IF(BE2=""L-3"",""N"",IF(BE2=""L-4"",""N"", " & _
"IF(BE2=""L-5"",""N"",IF(BE2=""L-6"",""N"",IF(BE2=""L-7"",""N"",IF(BE2=""L-8"",""N"", " & _
"IF(BE2=""L-9"",""N"",IF(BE2=""L-10"",""N"",IF(BE2=""L-11"",""N"", " & _
"IF(BE2=""Y-1"",""N"",IF(BE2=""Y-2"",""N"",IF(BE2=""Y-3"",""N"",IF(BE2=""C-1"",""N"", " & _
"IF(BE2=""Z-1"",""N""))))))))))))))))))))))))))))))))))))))))))))))))))))"
Click to expand...
I tried this but it goes to only one row. How to make it select until the last row.

<code>
ws.Range("BC1").Value = "GOV"

lrow = ws.Cells(Rows.Count, "BC").End(xlUp).Row
ws.Range("BC2").Formula = _
"=IF(BE2=""A-0"",""1"",IF(BE2=""A-1"",""1"",IF(BE2=""A-2"",""1"",IF(BE2=""A-3"",""1"", " & _
"IF(BE2=""A-4"",""1"",IF(BE2=""A-5"",""1"",IF(BE2=""S-1"",""1"",IF(BE2=""S-2"",""1"", " & _
"IF(BE2=""S-3"",""1"",IF(BE2=""S-4"",""1"",IF(BE2=""G-1"",""2A"",IF(BE2=""G-2"",""2A"", " & _
"IF(BE2=""G-3"",""2A"",IF(BE2=""S-5"",""2A"",IF(BE2=""S-6"",""2A"",IF(BE2=""S-7"",""2A"", " & _
"IF(BE2=""E-1"",""2B"",IF(BE2=""E-2"",""2B"",IF(BE2=""IG-1"",""2B"",IF(BE2=""IG-2"",""2B"", " & _
"IF(BE2=""S-8"",""N"",IF(BE2=""G-4"",""N"",IF(BE2=""E-3"",""N"",IF(BE2=""E-4"",""N"", " & _
"IF(BE2=""IG-3"",""N"",IF(BE2=""IG-4"",""N"",IF(BE2=""IG-5"",""N"",IF(BE2=""IG-6"",""N"", " & _
"IF(BE2=""IG-7"",""N"",IF(BE2=""N-1"",""N"",IF(BE2=""N-2"",""N"",IF(BE2=""N-3"",""N"", " & _
"IF(BE2=""N-4"",""N"",IF(BE2=""N-5"",""N"",IF(BE2=""N-6"",""N"",IF(BE2=""N-7"",""N"", " & _
"IF(BE2=""L-1"",""N"",IF(BE2=""L-2"",""N"",IF(BE2=""L-3"",""N"",IF(BE2=""L-4"",""N"", " & _
"IF(BE2=""L-5"",""N"",IF(BE2=""L-6"",""N"",IF(BE2=""L-7"",""N"",IF(BE2=""L-8"",""N"", " & _
"IF(BE2=""L-9"",""N"",IF(BE2=""L-10"",""N"",IF(BE2=""L-11"",""N"", " & _
"IF(BE2=""Y-1"",""N"",IF(BE2=""Y-2"",""N"",IF(BE2=""Y-3"",""N"",IF(BE2=""C-1"",""N"", " & _
"IF(BE2=""Z-1"",""N""))))))))))))))))))))))))))))))))))))))))))))))))))))"
ws.Range("BC2").AutoFill Destination:=Range("BC2:BC" & lrow)

</code>
 
Upvote 0
Just use
VBA Code: 
ws.Range("BC2:BC" & LRow).Formula = _
"=IF(BE2=""A-0"",""1"",IF(BE2=""A-1"",""1"",IF(BE2=""A-2"",""1"",IF(BE2=""A-3"",""1"", " & _
"IF(BE2=""A-4"",""1"",IF(BE2=""A-5"",""1"",IF(BE2=""S-1"",""1"",IF(BE2=""S-2"",""1"", " & _
"IF(BE2=""S-3"",""1"",IF(BE2=""S-4"",""1"",IF(BE2=""G-1"",""2A"",IF(BE2=""G-2"",""2A"", " & _
"IF(BE2=""G-3"",""2A"",IF(BE2=""S-5"",""2A"",IF(BE2=""S-6"",""2A"",IF(BE2=""S-7"",""2A"", " & _
"IF(BE2=""E-1"",""2B"",IF(BE2=""E-2"",""2B"",IF(BE2=""IG-1"",""2B"",IF(BE2=""IG-2"",""2B"", " & _
"IF(BE2=""S-8"",""N"",IF(BE2=""G-4"",""N"",IF(BE2=""E-3"",""N"",IF(BE2=""E-4"",""N"", " & _
"IF(BE2=""IG-3"",""N"",IF(BE2=""IG-4"",""N"",IF(BE2=""IG-5"",""N"",IF(BE2=""IG-6"",""N"", " & _
"IF(BE2=""IG-7"",""N"",IF(BE2=""N-1"",""N"",IF(BE2=""N-2"",""N"",IF(BE2=""N-3"",""N"", " & _
"IF(BE2=""N-4"",""N"",IF(BE2=""N-5"",""N"",IF(BE2=""N-6"",""N"",IF(BE2=""N-7"",""N"", " & _
"IF(BE2=""L-1"",""N"",IF(BE2=""L-2"",""N"",IF(BE2=""L-3"",""N"",IF(BE2=""L-4"",""N"", " & _
"IF(BE2=""L-5"",""N"",IF(BE2=""L-6"",""N"",IF(BE2=""L-7"",""N"",IF(BE2=""L-8"",""N"", " & _
"IF(BE2=""L-9"",""N"",IF(BE2=""L-10"",""N"",IF(BE2=""L-11"",""N"", " & _
"IF(BE2=""Y-1"",""N"",IF(BE2=""Y-2"",""N"",IF(BE2=""Y-3"",""N"",IF(BE2=""C-1"",""N"", " & _
"IF(BE2=""Z-1"",""N""))))))))))))))))))))))))))))))))))))))))))))))))))))"

Also when posting code, please use code tags How to Post Your VBA Code
 
Upvote 0
Solution
Fluff said:
Just use
VBA Code: 
ws.Range("BC2:BC" & LRow).Formula = _
"=IF(BE2=""A-0"",""1"",IF(BE2=""A-1"",""1"",IF(BE2=""A-2"",""1"",IF(BE2=""A-3"",""1"", " & _
"IF(BE2=""A-4"",""1"",IF(BE2=""A-5"",""1"",IF(BE2=""S-1"",""1"",IF(BE2=""S-2"",""1"", " & _
"IF(BE2=""S-3"",""1"",IF(BE2=""S-4"",""1"",IF(BE2=""G-1"",""2A"",IF(BE2=""G-2"",""2A"", " & _
"IF(BE2=""G-3"",""2A"",IF(BE2=""S-5"",""2A"",IF(BE2=""S-6"",""2A"",IF(BE2=""S-7"",""2A"", " & _
"IF(BE2=""E-1"",""2B"",IF(BE2=""E-2"",""2B"",IF(BE2=""IG-1"",""2B"",IF(BE2=""IG-2"",""2B"", " & _
"IF(BE2=""S-8"",""N"",IF(BE2=""G-4"",""N"",IF(BE2=""E-3"",""N"",IF(BE2=""E-4"",""N"", " & _
"IF(BE2=""IG-3"",""N"",IF(BE2=""IG-4"",""N"",IF(BE2=""IG-5"",""N"",IF(BE2=""IG-6"",""N"", " & _
"IF(BE2=""IG-7"",""N"",IF(BE2=""N-1"",""N"",IF(BE2=""N-2"",""N"",IF(BE2=""N-3"",""N"", " & _
"IF(BE2=""N-4"",""N"",IF(BE2=""N-5"",""N"",IF(BE2=""N-6"",""N"",IF(BE2=""N-7"",""N"", " & _
"IF(BE2=""L-1"",""N"",IF(BE2=""L-2"",""N"",IF(BE2=""L-3"",""N"",IF(BE2=""L-4"",""N"", " & _
"IF(BE2=""L-5"",""N"",IF(BE2=""L-6"",""N"",IF(BE2=""L-7"",""N"",IF(BE2=""L-8"",""N"", " & _
"IF(BE2=""L-9"",""N"",IF(BE2=""L-10"",""N"",IF(BE2=""L-11"",""N"", " & _
"IF(BE2=""Y-1"",""N"",IF(BE2=""Y-2"",""N"",IF(BE2=""Y-3"",""N"",IF(BE2=""C-1"",""N"", " & _
"IF(BE2=""Z-1"",""N""))))))))))))))))))))))))))))))))))))))))))))))))))))"

Also when posting code, please use code tags How to Post Your VBA Code
Click to expand...

I did that and now I get the below error on the line

ws.Range("BC2").AutoFill Destination:=Range("BC2:BC" & lrow).


1657127283149.png


I am looking to autofill col BC with the above formula
 
Upvote 0
Remove that line it's not needed.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Dropdown code VBA doesn't execute
Replies
0
Views
388
niresh28
N
E
  • Solved
too many "if"s ?
2
Replies
15
Views
609
Fluff
Fluff
J
  • Solved
conditional format to change color
2
Replies
10
Views
209
SanjayGMusafir
SanjayGMusafir
N
  • Question
MATCH two ranges, column by column
Replies
4
Views
179
Fluff
Fluff
J
  • Question
using sumifs function across multiple sheets
Replies
4
Views
258
KRice
K

Forum statistics

Threads
1,214,975
Messages
6,122,538
Members
449,088
Latest member
RandomExceller01

Share this page

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
Back
Top