| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how i can make formula | Excel Discussion (Misc queries) | |||
| Make zero formula | Excel Discussion (Misc queries) | |||
| How do I make a formula read a result rather than a formula | Excel Discussion (Misc queries) | |||
| I'd like to make a formula that does this.. | Excel Worksheet Functions | |||
| How do I make this formula? | Excel Discussion (Misc queries) | |||
Linear Mode
