excel - Variables inside formulas appearing with quotes -
i trying insert 2 variables formula. altough appearing inside quotes.
fase1 = cells(2, diffdate_ac + 4).address(rowabsolute:=false, columnabsolute:=false) prod1 = cells(4, diffdate_ac + 4).address(rowabsolute:=false, columnabsolute:=false) fase1 refers ag2 , prod1 ag4.
range("b3").formula = "=sum((sumifs(ben_quant,ben_dia,r[-1]c,ben_fase, " & fase1 & ",ben_linha," & prod1 & ")) it part of formula. " & fase 1 & " giving me 'ag2', , " & prod 1 & " returning 'ag4'. yes, both inside quotes inside formula image shows:
i want remove single quotes, because way formula not referring cells.
the complete part of code of command
'prints array of dates dim startac2 long, finishac2 long dim datearray3 variant, y long finishac2 = clng(datevalue(lastac)) startac2 = clng(datevalue(firstac)) redim datearray3(finishac2 - startac2 + 1) y = 0 finishac2 - startac2 datearray3(y) = startac2 + y next diffdate_ac = datediff("d", startac2, finishac2) + 1 wspben.activate range(cells(2, 2), cells(2, diffdate_ac + 1)).value = datearray3 range(cells(2, 2), cells(2, diffdate_ac + 1)).numberformat = "d/m;@" ' range("a1").value = "beneficiamento: tinturaria liso" range("a2").value = "data" range("a3").value = "produção" cells(2, diffdate_ac + 2).value = "total" cells(2, diffdate_ac + 3).value = "fase" cells(4, diffdate_ac + 3).value = "produto" cells(2, diffdate_ac + 4).value = "tingir alta temp." cells(3, diffdate_ac + 4).value = "tingir baixa temp." cells(4, diffdate_ac + 4).value = "tinto ramado" cells(5, diffdate_ac + 4).value = "tinto tubular" dim fase1 string dim fase2 string dim prod1 string dim prod2 string fase1 = cells(2, diffdate_ac + 4).address(rowabsolute:=false, columnabsolute:=false) fase2 = cells(3, diffdate_ac + 4).address(rowabsolute:=false, columnabsolute:=false) prod1 = cells(4, diffdate_ac + 4).address(rowabsolute:=false, columnabsolute:=false) prod2 = cells(5, diffdate_ac + 4).address(rowabsolute:=false, columnabsolute:=false) range("b3").formula = "=sum((sumifs(ben_quant,ben_dia,r[-1]c,ben_fase," & fase1 & ",ben_linha," & prod1 & ")),(sumifs(ben_quant,ben_dia,r[-1]c,ben_fase," & fase1 & ",ben_linha," & prod2 & ")),(sumifs(ben_quant,ben_dia,r[-1]c,ben_fase," & fase2 & ",ben_linha," & prod1 & ")),(sumifs(ben_quant,ben_dia,r[-1]c,ben_fase," & fase2 & ",ben_linha," & prod2 & ")))"
if read correctly, vba line:
range("b3").formula = "=sum((sumifs(ben_quant,ben_dia,r[-1]c,ben_fase, " & fase1 & ",ben_linha," & prod1 & "))" is trying set formula expression:
=sum((sumifs(ben_quant,ben_dia,r[-1]c,ben_fase, ag2,ben_linha,ag4)) however, mixing r1c1 , a1 cell addressing in same formula. far know, invalid syntax.
Comments
Post a Comment