# Formula into Vb

#### DanD

##### Board Regular
Hi all,

I have a vb function that enters a formula into a work sheet but I seem to have made an error and I can't for the life of me see it.

Actc is the column
Rw is also the column
When I just stepped through the code actc = 21 and Rw =U
Vb formula:
Code:
``Cells(14, Actc).Formula = "=IF(VLOOKUP(\$C\$3,Data!\$A:\$EU,(118+Report!" & Rw & "1),0)=123456,IF((INDEX(ServT,MATCH((" & Rw & "15/\$L\$4),INDEX(ServT,,2),-1),4))=R,(ROUNDUP((AVERAGE(" & Rw & "12:Offset(" & Rw & "12,0,3))*(INDEX(ServT,MATCH((" & Rw & "\$15/\$L\$4),INDEX(ServT,,2),-1),3)))+\$L\$4*\$I\$6,0)),(INDEX(ServT,MATCH((" & Rw & "15/\$L\$4),INDEX(ServT,,2),-1),3)*\$L\$4)+(\$I\$6*\$L\$4)),VLOOKUP(\$C\$3,Data!\$A:\$ET,(118+Report!" & Rw & "1)))"``

The output should be in excel terms
Code:
``'=IF(VLOOKUP(\$C\$3,Data!\$A:\$ET,(118+Report!U\$1),0)=123456,IF((INDEX(ServT,MATCH((U\$15/\$L\$4),INDEX(ServT,,2),-1),4))="R",(ROUNDUP((AVERAGE(U\$12:OFFSET(U12,0,3))*(INDEX(ServT,MATCH((U\$15/\$L\$4),INDEX(ServT,,2),-1),3)))+\$L\$4*\$I\$6,0)),(INDEX(ServT,MATCH((U\$15/\$L\$4),INDEX(ServT,,2),-1),3)*\$L\$4)+(\$I\$6*\$L\$4)),VLOOKUP(\$C\$3,Data!\$A:\$ET,(118+Report!U\$1)))``

Any help would be marvellous!

Dan.

Who is looking forward to 5:30

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Code:
``````Cells(14, Actc).Formula = "=IF(VLOOKUP(\$C\$3,Data!\$A:\$EU,(118+Report!" & Rw & "\$1),0)=123456," & _
"IF((INDEX(ServT,MATCH((" & Rw & "15/\$L\$4),INDEX(ServT,,2),-1),4))=""R""," & _
"(ROUNDUP((AVERAGE(" & Rw & "12:Offset(" & Rw & "12,0,3))*" & _
"(INDEX(ServT,MATCH((" & Rw & "\$15/\$L\$4),INDEX(ServT,,2),-1),3)))+\$L\$4*\$I\$6,0))," & _
"(INDEX(ServT,MATCH((" & Rw & "15/\$L\$4),INDEX(ServT,,2),-1),3)*\$L\$4)+(\$I\$6*\$L\$4))," & _
"VLOOKUP(\$C\$3,Data!\$A:\$ET,(118+Report!" & Rw & "1)))"``````

One thing I noticed is you need to put double quotes around the R

Code:
``````Cells(14, Actc).Formula = "=IF(VLOOKUP(\$C\$3,Data!\$A:\$EU,(118+Report!" & rw & "1),0)" & _
"=123456,IF((INDEX(ServT,MATCH((" & rw & "15/\$L\$4),INDEX(" & _
"ServT,,2),-1),4))=""R"",(ROUNDUP((AVERAGE(" & rw & _
"12:Offset(" & rw & "12,0,3))*(INDEX(ServT,MATCH((" & _
rw & "\$15/\$L\$4),INDEX(ServT,,2),-1),3)))+\$L\$4*\$I\$6,0))," & _
"(INDEX(ServT,MATCH((" & rw & "15/\$L\$4),INDEX(ServT,,2)" & _
",-1),3)*\$L\$4)+(\$I\$6*\$L\$4)),VLOOKUP(\$C\$3,Data!\$A:\$ET," & _
"(118+Report!" & rw & "1)))"``````

Hard to know if that's the only error though...

Marvellous, thanks

Yeah I've put the double quotes in on the R, and it works perfectly.

Thanks again

I thought I'd bump this old thread as I'm trying to do a similar thing and I'm gettign stuck again!

I'm trying to get the following formula into VBA
SelTemp is a named range that identifies which Template sheet to use.
Excel Formula:

Code:
``=IF(ISERROR(INDIRECT(CONCATENATE("'",SelTemp,"'!",ADDRESS(ROW()-6,COLUMN()-2)))&" - "&VLOOKUP((INDIRECT(ADDRESS(7,COLUMN()))&INDIRECT(ADDRESS(3,5))&INDIRECT(ADDRESS(6,COLUMN()))&INDIRECT(ADDRESS(ROW(),3))),sudbp160!\$C:\$K,9,0)&" - "&VLOOKUP((INDIRECT(ADDRESS(7,COLUMN()))&INDIRECT(ADDRESS(3,5))&INDIRECT(ADDRESS(6,COLUMN()))&INDIRECT(ADDRESS(ROW(),3))),sudbp160!\$C:\$K,7,0)&" - "&SUMIF(sudbp160!\$C:\$C,(INDIRECT(ADDRESS(7,COLUMN()))&INDIRECT(ADDRESS(3,5))&INDIRECT(ADDRESS(6,COLUMN()))&INDIRECT(ADDRESS(ROW(),3))),sudbp160!\$P:\$P)&"/"&COUNTIF(sudbp160!\$C:\$C,(INDIRECT(ADDRESS(7,COLUMN()))&INDIRECT(ADDRESS(3,5))&INDIRECT(ADDRESS(6,COLUMN()))&INDIRECT(ADDRESS(ROW(),3))))),"",INDIRECT(CONCATENATE("'",SelTemp,"'!",ADDRESS(ROW()-6,COLUMN()-2)))&" - "&VLOOKUP((INDIRECT(ADDRESS(7,COLUMN()))&INDIRECT(ADDRESS(3,5))&INDIRECT(ADDRESS(6,COLUMN()))&INDIRECT(ADDRESS(ROW(),3))),sudbp160!\$C:\$K,9,0)&" - "&VLOOKUP((INDIRECT(ADDRESS(7,COLUMN()))&INDIRECT(ADDRESS(3,5))&INDIRECT(ADDRESS(6,COLUMN()))&INDIRECT(ADDRESS(ROW(),3))),sudbp160!\$C:\$K,7,0)&" - "&SUMIF(sudbp160!\$C:\$C,(INDIRECT(ADDRESS(7,COLUMN()))&INDIRECT(ADDRESS(3,5))&INDIRECT(ADDRESS(6,COLUMN()))&INDIRECT(ADDRESS(ROW(),3))),sudbp160!\$P:\$P)&"/"&COUNTIF(sudbp160!\$C:\$C,(INDIRECT(ADDRESS(7,COLUMN()))&INDIRECT(ADDRESS(3,5))&INDIRECT(ADDRESS(6,COLUMN()))&INDIRECT(ADDRESS(ROW(),3)))))``

Going into VBA
Code:
``````For Each Area1 In Area
Next``````

Any help would be fantastic

Dan

Replies
6
Views
149
Replies
1
Views
95
Replies
14
Views
202
Replies
2
Views
83
Replies
24
Views
979

1,219,811
Messages
6,150,366
Members
450,955
Latest member
rose8693

### 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.

### Which adblocker are you using?

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

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