# 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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### xld

##### Banned
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)))"``````

#### Colin Legg

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

#### DanD

##### Board Regular
Marvellous, thanks

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

Thanks again

#### DanD

##### Board Regular
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 Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,682
Messages
5,838,788
Members
430,569
Latest member
sbardelli ### 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