how to make the formula

[=?iso-2022-jp?B?RVhDRUwbJEIhIRsoQk5FV1M=?=]

hi,

i want to make a formula like this

cells(0,0)=sumif(range1.AddressLocal,TEXT,range2.AddressLocal) in vba

but in excel the text must be like "ego",insteadof ego,

how to make the to be "ego" instead of ego.

thanks

 

[Bob Phillips]

You cannot use cells(0,0) as Cells is 1 based.

Cells(1, 1) = "=sumif(" & range1.AddressLocal & ",""TEXT""," &
range2.AddressLocal & ")"


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[=?iso-2022-jp?B?RVhDRUwbJEIhIRsoQk5FV1M=?=]

c1.Offset(n1 + 2 + m + 1, D).Formula = "=" + "SUMIF(" + Range(c1.Offset(n1 +
2, D - 1), c1.Offset(n1 + 2 + m - 1, D -1)).AddressLocal(RowAbsolute:=False,
ColumnAbsolute:=False) + ","D16"," +Range(c1.Offset(n1 + 2, D), c1.Offset(n1
+ 2 + m - 1,
D)).AddressLocal(RowAbsolute:=False, ColumnAbsolute:=False) + "$B!K(B"

if$B!!(B i input ","D16",",the statement will be in red, it is error.
what should i do


thanks

 

[=?iso-2022-jp?B?RVhDRUwbJEIhIRsoQk5FV1M=?=]

i get it,

that is chr(34)

thanks

c1.Offset(n1 + 2 + m + 1, D).Formula = "=" + "SUMIF(" + Range(c1.Offset(n1 +
2, D - 1), c1.Offset(n1 + 2 + m - 1,

D -1)).AddressLocal(RowAbsolute:=False,

 

[Bob Phillips]

Because you are not double-quoting a string. It should be

Dim sFormula As String
Dim c1 As range

Set c1 = ActiveCell
sFormula = "=" + "SUMIF(" + _
range(c1.Offset(n1 + 2, d - 1), _
c1.Offset(n1 + 2 + m - 1, d - 1)).AddressLocal( _
RowAbsolute:=False, _
ColumnAbsolute:=False) + ",D16," + _
range(c1.Offset(n1 + 2, d), c1.Offset(n1 + 2 + m - 1,
d)).AddressLocal( _
RowAbsolute:=False, _
ColumnAbsolute:=False) + ")"

c1.Offset(n1 + 2 + m + 1, d).Formula = sFormula

if D16 is a cell, or

Dim sFormula As String
Dim c1 As range

Set c1 = ActiveCell
sFormula = "=" + "SUMIF(" + _
range(c1.Offset(n1 + 2, d - 1), _
c1.Offset(n1 + 2 + m - 1, d - 1)).AddressLocal( _
RowAbsolute:=False, _
ColumnAbsolute:=False) + "",D16,"" + _
range(c1.Offset(n1 + 2, d), c1.Offset(n1 + 2 + m - 1,
d)).AddressLocal( _
RowAbsolute:=False, _
ColumnAbsolute:=False) + ")"

c1.Offset(n1 + 2 + m + 1, d).Formula = sFormula

if D16 is text

--

HTH

RP
(remove nothere from the email address if mailing direct)

c1.Offset(n1 + 2 + m + 1, D).Formula = "=" + "SUMIF(" + Range(c1.Offset(n1 +
2, D - 1), c1.Offset(n1 + 2 + m - 1,

D -1)).AddressLocal(RowAbsolute:=False,