Adding formulas to cells

I am trying to add the following formual to a column of cells in Excel. The
code first goes through and counts the number of rows and then adds the
formula. We have used this many time before but are unable to get this one
to work. I have narrowed it down to it not working on the ones that use "*"
and/or "/" in them. Can anyone help me with this?

I posted this Friday and didnt get a response, so I am trying to clarifiy
and simplify my question. Any and all help will be greatly appriciated.

One that does work:

Call InsertFormula(GetRowCount("C"), 15,
"=IF(ISERROR(FIND(""$"",S#,1))=TRUE,P#,"""")")

One that does not work:

Call InsertFormula(GetRowCount("C"), 20,
"=IF(AB#=""1"",S#,IF(AB#=""3"",N#/W#/100,""""))=SUM(T#*V#))")

This is some of the code we use before using the lines above:

Private Function GetRowCount(strColumn As String) As Integer
Dim iCount As Long
Dim i As Long

For i = 1 To 65000
If Range(strColumn & i).Value <> "" Then
iCount = iCount + 1
Else
Exit For
End If
Next
GetRowCount = iCount
End Function

Private Sub InsertFormula(intRowCount As Long, intColumn As Long, strFormula
As String)
Dim ws As Worksheet
Set ws = ThisWorkbook.ActiveSheet

For i = 2 To intRowCount
ws.Cells(i, intColumn) = Replace(strFormula, "#", CStr(i))
Next
End Sub

I don't understand the "=SUM(T#*V#))" in the formula ... this causes the
error i.e removing it creates a valid formula.

What are you logically trying to do?

"Jordan" wrote:

> I am trying to add the following formual to a column of cells in Excel. The
> code first goes through and counts the number of rows and then adds the
> formula. We have used this many time before but are unable to get this one
> to work. I have narrowed it down to it not working on the ones that use "*"
> and/or "/" in them. Can anyone help me with this?
>
> I posted this Friday and didnt get a response, so I am trying to clarifiy
> and simplify my question. Any and all help will be greatly appriciated.
>
> One that does work:
>
> Call InsertFormula(GetRowCount("C"), 15,
> "=IF(ISERROR(FIND(""$"",S#,1))=TRUE,P#,"""")")
>
> One that does not work:
>
> Call InsertFormula(GetRowCount("C"), 20,
> "=IF(AB#=""1"",S#,IF(AB#=""3"",N#/W#/100,""""))=SUM(T#*V#))")
>
> This is some of the code we use before using the lines above:
>
> Private Function GetRowCount(strColumn As String) As Integer
> Dim iCount As Long
> Dim i As Long
>
> For i = 1 To 65000
> If Range(strColumn & i).Value <> "" Then
> iCount = iCount + 1
> Else
> Exit For
> End If
> Next
> GetRowCount = iCount
> End Function
>
> Private Sub InsertFormula(intRowCount As Long, intColumn As Long, strFormula
> As String)
> Dim ws As Worksheet
> Set ws = ThisWorkbook.ActiveSheet
>
> For i = 2 To intRowCount
> ws.Cells(i, intColumn) = Replace(strFormula, "#", CStr(i))
> Next
> End Sub
>
>
>

The # sign represents the row #, it is set from the code run before the
insert formulas. In the example you sent back we are trying to place the
formula =sum(T# * V#) once in excel in row 2 it would read =sum(T2*V2). We
have to use the # sign so the row number changes with each time the formula
is inserted into a new row. The problem is it will not handle the * muliple
and / divide signs.

Thanks for you help.

Private Sub InsertFormula(intRowCount As Long, intColumn As Long, strFormula
> > As String)
> > Dim ws As Worksheet
> > Set ws = ThisWorkbook.ActiveSheet
> >
> > For i = 2 To intRowCount
> > ws.Cells(i, intColumn) = Replace(strFormula, "#", CStr(i))
> > Next
> > End Sub


"Toppers" wrote:

> I don't understand the "=SUM(T#*V#))" in the formula ... this causes the
> error i.e removing it creates a valid formula.
>
> What are you logically trying to do?
>
> "Jordan" wrote:
>
> > I am trying to add the following formual to a column of cells in Excel. The
> > code first goes through and counts the number of rows and then adds the
> > formula. We have used this many time before but are unable to get this one
> > to work. I have narrowed it down to it not working on the ones that use "*"
> > and/or "/" in them. Can anyone help me with this?
> >
> > I posted this Friday and didnt get a response, so I am trying to clarifiy
> > and simplify my question. Any and all help will be greatly appriciated.
> >
> > One that does work:
> >
> > Call InsertFormula(GetRowCount("C"), 15,
> > "=IF(ISERROR(FIND(""$"",S#,1))=TRUE,P#,"""")")
> >
> > One that does not work:
> >
> > Call InsertFormula(GetRowCount("C"), 20,
> > "=IF(AB#=""1"",S#,IF(AB#=""3"",N#/W#/100,""""))=SUM(T#*V#))")
> >
> > This is some of the code we use before using the lines above:
> >
> > Private Function GetRowCount(strColumn As String) As Integer
> > Dim iCount As Long
> > Dim i As Long
> >
> > For i = 1 To 65000
> > If Range(strColumn & i).Value <> "" Then
> > iCount = iCount + 1
> > Else
> > Exit For
> > End If
> > Next
> > GetRowCount = iCount
> > End Function
> >
> > Private Sub InsertFormula(intRowCount As Long, intColumn As Long, strFormula
> > As String)
> > Dim ws As Worksheet
> > Set ws = ThisWorkbook.ActiveSheet
> >
> > For i = 2 To intRowCount
> > ws.Cells(i, intColumn) = Replace(strFormula, "#", CStr(i))
> > Next
> > End Sub
> >
> >
> >

Your formula has an extra Parenthesis in it:
Instead of:

Call InsertFormula(GetRowCount("C"), 20,
"=IF(AB#=""1"",S#,IF(AB#=""3"",N#/W#/100,""""))=SUM(T#*V#))")

try

Call InsertFormula(GetRowCount("C"), 20,
"=IF(AB#=""1"",S#,IF(AB#=""3"",N#/W#/100,""""))=SUM(T#*V#)")

--
Regards,
Tom Ogilvy




"Jordan" wrote:

> I am trying to add the following formual to a column of cells in Excel. The
> code first goes through and counts the number of rows and then adds the
> formula. We have used this many time before but are unable to get this one
> to work. I have narrowed it down to it not working on the ones that use "*"
> and/or "/" in them. Can anyone help me with this?
>
> I posted this Friday and didnt get a response, so I am trying to clarifiy
> and simplify my question. Any and all help will be greatly appriciated.
>
> One that does work:
>
> Call InsertFormula(GetRowCount("C"), 15,
> "=IF(ISERROR(FIND(""$"",S#,1))=TRUE,P#,"""")")
>
> One that does not work:
>
> Call InsertFormula(GetRowCount("C"), 20,
> "=IF(AB#=""1"",S#,IF(AB#=""3"",N#/W#/100,""""))=SUM(T#*V#))")
>
> This is some of the code we use before using the lines above:
>
> Private Function GetRowCount(strColumn As String) As Integer
> Dim iCount As Long
> Dim i As Long
>
> For i = 1 To 65000
> If Range(strColumn & i).Value <> "" Then
> iCount = iCount + 1
> Else
> Exit For
> End If
> Next
> GetRowCount = iCount
> End Function
>
> Private Sub InsertFormula(intRowCount As Long, intColumn As Long, strFormula
> As String)
> Dim ws As Worksheet
> Set ws = ThisWorkbook.ActiveSheet
>
> For i = 2 To intRowCount
> ws.Cells(i, intColumn) = Replace(strFormula, "#", CStr(i))
> Next
> End Sub
>
>
>

Tom, Sorry I wasted your time. When I pasted the code in I must have grabbed
too much. It reads as follows:

Call InsertFormula(GetRowCount("C"), 20, "=IF(AB#=""1"",S#,
IF(AB#=""3"",N#/W#/100,"""")")

If you can still help, I would appreciated it. I think it has to do with
the use of * and / as all the other lines that work dont use multiple or
divide.

This is an example that does work:

Call InsertFormula(GetRowCount("C"), 15,
"=IF(ISERROR(FIND(""$"",S#,1))=TRUE,P#,"""")")

Again, sorry for sending the wrong code.



"Tom Ogilvy" wrote:

> Your formula has an extra Parenthesis in it:
> Instead of:
>
> Call InsertFormula(GetRowCount("C"), 20,
> "=IF(AB#=""1"",S#,IF(AB#=""3"",N#/W#/100,""""))=SUM(T#*V#))")
>
> try
>
> Call InsertFormula(GetRowCount("C"), 20,
> "=IF(AB#=""1"",S#,IF(AB#=""3"",N#/W#/100,""""))=SUM(T#*V#)")
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "Jordan" wrote:
>
> > I am trying to add the following formual to a column of cells in Excel. The
> > code first goes through and counts the number of rows and then adds the
> > formula. We have used this many time before but are unable to get this one
> > to work. I have narrowed it down to it not working on the ones that use "*"
> > and/or "/" in them. Can anyone help me with this?
> >
> > I posted this Friday and didnt get a response, so I am trying to clarifiy
> > and simplify my question. Any and all help will be greatly appriciated.
> >
> > One that does work:
> >
> > Call InsertFormula(GetRowCount("C"), 15,
> > "=IF(ISERROR(FIND(""$"",S#,1))=TRUE,P#,"""")")
> >
> > One that does not work:
> >
> > Call InsertFormula(GetRowCount("C"), 20,
> > "=IF(AB#=""1"",S#,IF(AB#=""3"",N#/W#/100,""""))=SUM(T#*V#))")
> >
> > This is some of the code we use before using the lines above:
> >
> > Private Function GetRowCount(strColumn As String) As Integer
> > Dim iCount As Long
> > Dim i As Long
> >
> > For i = 1 To 65000
> > If Range(strColumn & i).Value <> "" Then
> > iCount = iCount + 1
> > Else
> > Exit For
> > End If
> > Next
> > GetRowCount = iCount
> > End Function
> >
> > Private Sub InsertFormula(intRowCount As Long, intColumn As Long, strFormula
> > As String)
> > Dim ws As Worksheet
> > Set ws = ThisWorkbook.ActiveSheet
> >
> > For i = 2 To intRowCount
> > ws.Cells(i, intColumn) = Replace(strFormula, "#", CStr(i))
> > Next
> > End Sub
> >
> >
> >

TRY:

Call InsertFormula(GetRowCount("C"),
20,"=IF(AB#=""1"",S#,IF(AB#=""3"",N#/W#/100,""""))")

"Jordan" wrote:

> Tom, Sorry I wasted your time. When I pasted the code in I must have grabbed
> too much. It reads as follows:
>
> Call InsertFormula(GetRowCount("C"), 20, "=IF(AB#=""1"",S#,
> IF(AB#=""3"",N#/W#/100,"""")")
>
> If you can still help, I would appreciated it. I think it has to do with
> the use of * and / as all the other lines that work dont use multiple or
> divide.
>
> This is an example that does work:
>
> Call InsertFormula(GetRowCount("C"), 15,
> "=IF(ISERROR(FIND(""$"",S#,1))=TRUE,P#,"""")")
>
> Again, sorry for sending the wrong code.
>
>
>
> "Tom Ogilvy" wrote:
>
> > Your formula has an extra Parenthesis in it:
> > Instead of:
> >
> > Call InsertFormula(GetRowCount("C"), 20,
> > "=IF(AB#=""1"",S#,IF(AB#=""3"",N#/W#/100,""""))=SUM(T#*V#))")
> >
> > try
> >
> > Call InsertFormula(GetRowCount("C"), 20,
> > "=IF(AB#=""1"",S#,IF(AB#=""3"",N#/W#/100,""""))=SUM(T#*V#)")
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> >
> > "Jordan" wrote:
> >
> > > I am trying to add the following formual to a column of cells in Excel. The
> > > code first goes through and counts the number of rows and then adds the
> > > formula. We have used this many time before but are unable to get this one
> > > to work. I have narrowed it down to it not working on the ones that use "*"
> > > and/or "/" in them. Can anyone help me with this?
> > >
> > > I posted this Friday and didnt get a response, so I am trying to clarifiy
> > > and simplify my question. Any and all help will be greatly appriciated.
> > >
> > > One that does work:
> > >
> > > Call InsertFormula(GetRowCount("C"), 15,
> > > "=IF(ISERROR(FIND(""$"",S#,1))=TRUE,P#,"""")")
> > >
> > > One that does not work:
> > >
> > > Call InsertFormula(GetRowCount("C"), 20,
> > > "=IF(AB#=""1"",S#,IF(AB#=""3"",N#/W#/100,""""))=SUM(T#*V#))")
> > >
> > > This is some of the code we use before using the lines above:
> > >
> > > Private Function GetRowCount(strColumn As String) As Integer
> > > Dim iCount As Long
> > > Dim i As Long
> > >
> > > For i = 1 To 65000
> > > If Range(strColumn & i).Value <> "" Then
> > > iCount = iCount + 1
> > > Else
> > > Exit For
> > > End If
> > > Next
> > > GetRowCount = iCount
> > > End Function
> > >
> > > Private Sub InsertFormula(intRowCount As Long, intColumn As Long, strFormula
> > > As String)
> > > Dim ws As Worksheet
> > > Set ws = ThisWorkbook.ActiveSheet
> > >
> > > For i = 2 To intRowCount
> > > ws.Cells(i, intColumn) = Replace(strFormula, "#", CStr(i))
> > > Next
> > > End Sub
> > >
> > >
> > >

Thank you so much for your help. I was able to take this and fix the other
lines that were not working as well. I can't believe is was not writing the
formula correctly, I can't tell you how long I worked on this.

Again, thanks so much for your help.

"Toppers" wrote:

> TRY:
>
> Call InsertFormula(GetRowCount("C"),
> 20,"=IF(AB#=""1"",S#,IF(AB#=""3"",N#/W#/100,""""))")
>
> "Jordan" wrote:
>
> > Tom, Sorry I wasted your time. When I pasted the code in I must have grabbed
> > too much. It reads as follows:
> >
> > Call InsertFormula(GetRowCount("C"), 20, "=IF(AB#=""1"",S#,
> > IF(AB#=""3"",N#/W#/100,"""")")
> >
> > If you can still help, I would appreciated it. I think it has to do with
> > the use of * and / as all the other lines that work dont use multiple or
> > divide.
> >
> > This is an example that does work:
> >
> > Call InsertFormula(GetRowCount("C"), 15,
> > "=IF(ISERROR(FIND(""$"",S#,1))=TRUE,P#,"""")")
> >
> > Again, sorry for sending the wrong code.
> >
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > Your formula has an extra Parenthesis in it:
> > > Instead of:
> > >
> > > Call InsertFormula(GetRowCount("C"), 20,
> > > "=IF(AB#=""1"",S#,IF(AB#=""3"",N#/W#/100,""""))=SUM(T#*V#))")
> > >
> > > try
> > >
> > > Call InsertFormula(GetRowCount("C"), 20,
> > > "=IF(AB#=""1"",S#,IF(AB#=""3"",N#/W#/100,""""))=SUM(T#*V#)")
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > >
> > > "Jordan" wrote:
> > >
> > > > I am trying to add the following formual to a column of cells in Excel. The
> > > > code first goes through and counts the number of rows and then adds the
> > > > formula. We have used this many time before but are unable to get this one
> > > > to work. I have narrowed it down to it not working on the ones that use "*"
> > > > and/or "/" in them. Can anyone help me with this?
> > > >
> > > > I posted this Friday and didnt get a response, so I am trying to clarifiy
> > > > and simplify my question. Any and all help will be greatly appriciated.
> > > >
> > > > One that does work:
> > > >
> > > > Call InsertFormula(GetRowCount("C"), 15,
> > > > "=IF(ISERROR(FIND(""$"",S#,1))=TRUE,P#,"""")")
> > > >
> > > > One that does not work:
> > > >
> > > > Call InsertFormula(GetRowCount("C"), 20,
> > > > "=IF(AB#=""1"",S#,IF(AB#=""3"",N#/W#/100,""""))=SUM(T#*V#))")
> > > >
> > > > This is some of the code we use before using the lines above:
> > > >
> > > > Private Function GetRowCount(strColumn As String) As Integer
> > > > Dim iCount As Long
> > > > Dim i As Long
> > > >
> > > > For i = 1 To 65000
> > > > If Range(strColumn & i).Value <> "" Then
> > > > iCount = iCount + 1
> > > > Else
> > > > Exit For
> > > > End If
> > > > Next
> > > > GetRowCount = iCount
> > > > End Function
> > > >
> > > > Private Sub InsertFormula(intRowCount As Long, intColumn As Long, strFormula
> > > > As String)
> > > > Dim ws As Worksheet
> > > > Set ws = ThisWorkbook.ActiveSheet
> > > >
> > > > For i = 2 To intRowCount
> > > > ws.Cells(i, intColumn) = Replace(strFormula, "#", CStr(i))
> > > > Next
> > > > End Sub
> > > >
> > > >
> > > >

Hi Jordan,

I'm not sure if this is what you're trying to do, but here's my take on your
post:

You want to populate columns O(15) and T(20) with formulas for as many rows
as there are contiguous entries starting in row 1 of column C.

Your GetRowCount() function suggests that you want to stop at the row before
the first empty cell in column C. If this is true then InsertFormulas1() does
that. If you want to insert formulas for the number of cells down to the last
entry in column C then InsertFormulas2() does that.

Your post states concern that the formulas automatically adjust for the row
they're in. Note that using row-relative references causes Excel to adjust
for the row automatically as you fill down.

Your formula for column T indicates that column AB stores numbers as text.
If this is not the case then remove the quotes around the numbers. (ie
IF(AB2=1,...) It also contains a circular reference to itself. (ie.
=SUM(T2*V2))

That said, if what you want to do is populate the target columns with
formulas that adjust accordingly for the row it's in, then here's two simple
subs that do that: (modify accordingly)

Sub InsertFormulas1()
' This stops before the first empty cell

Dim lLastRow As Long
lLastRow = ActiveSheet.Cells(1, "C").End(xlDown).Row

Range("N2:N" & lLastRow).Formula = _
"=IF(ISERROR(FIND(""$"",S2,1))=TRUE,P2,"""")"
Range("O2:O" & lLastRow).Formula = _
"=IF(AB2=""1"",S2,IF(AB2=""3"",N2/W2/100,""""))=SUM(T2*V2)"
End Sub

Sub InsertFormulas2()
' This stops at the last used row

Dim lLastRow As Long
lLastRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row

Range("N2:N" & lLastRow).Formula = _
"=IF(ISERROR(FIND(""$"",S2,1))=TRUE,P2,"""")"
Range("O2:O" & lLastRow).Formula = _
"=IF(AB2=""1"",S2,IF(AB2=""3"",N2/W2/100,""""))=SUM(T2*V2)"
End Sub
---

HTH
Regards,
Garry