Formula into Vb

[DanD]

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:

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

'=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

 

[xld]

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]

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

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]

Marvellous, thanks

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

Thanks again

 

[DanD]

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:

=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

For Each Area1 In Area
Area1.Formula = "=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)))))"
Next

Any help would be fantastic

Thanks in advance

Dan