how to make the formula
 

hi,

i want to make a formula like this

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

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

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

thanks

how to make the formula
 

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)


"EXCEL$B!!(BNEWS" wrote in message
...
hi,

i want to make a formula like this

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

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

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

thanks

how to make the formula
 

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



"Bob Phillips" wrote in message
...
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)


"EXCEL$B!!(BNEWS" wrote in message
...
hi,

i want to make a formula like this

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

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

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

thanks

how to make the formula
 

i get it,

that is chr(34)

thanks


"EXCEL$B!!(BNEWS" wrote in message
...
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



"Bob Phillips" wrote in message
...
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)


"EXCEL$B!!(BNEWS" wrote in message
...
hi,

i want to make a formula like this

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

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

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

thanks

how to make the formula
 

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)


"EXCEL$B!!(BNEWS" wrote in message
...
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



"Bob Phillips" wrote in message
...
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)


"EXCEL$B!!(BNEWS" wrote in message
...
hi,

i want to make a formula like this

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

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

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

thanks