Formula Using a Variable Cell Location and a Fixed Cell Location

[FrenchCelt]

Hello,

I'm trying to do two formulas on a spreadsheet, both of which involve a fixed cell location (B8) and a variable cell location (depending on how many rows are generated each time). The first formula is a simple sum of all the columns from C onward for that row that I want to copy down to a specific point. Using filldown takes it all the way to the bottom, which I don't want:

Range("B8").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[76])"
    Range("B8", "B" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown

I need the formula to stop at the cell in Column B next to the cell in Column A labeled "Total Unapproved Indirect Labor".

The second formula will then go in Column B next to the row labeled "% INDIRECT LESS BREAKS" in Column A and divide the value in the cell immediately above by the value in B8, multiplied by 100.

I've tried to use variations of offset in my code, but either get syntax errors or, in one case, a divide by 0 error. Can anyone offer any suggestions?

 

[oldbrewer]

so you want to go down to say row 30, and say sum C30 to T30

The second formula will then go in Column B next to the row labeled "% INDIRECT LESS BREAKS" in Column A and divide the value in the cell immediately above by the value in B7, multiplied by 100. I do not understand what you want.....

 

[FrenchCelt]

so you want to go down to say row 30, and say sum C30 to T30

The second formula will then go in Column B next to the row labeled "% INDIRECT LESS BREAKS" in Column A and divide the value in the cell immediately above by the value in B7, multiplied by 100. I do not understand what you want.....

No, I want to take the formula that sums from C8 to BZ8 and go down to the row that says "Total Unapproved Indirect Labor", so in one case that might be B8 down to B25, or B8 down to B15, depending on what gets generated on any given spreadsheet. The only constant is B8 is the starting point.

In the case of the second formula, when I do it manually, it will look like =B23/B8*100, or =B13/B8*100, always in the cell in Column B next to the cell in Column A labeled "% INDIRECT LESS BREAKS".

 

[JoeMo]

See if this works for you.

Sub test()
Const Find1 As String = "Total Unapproved Indirect Labor"
Const Find2 As String = "% INDIRECT LESS BREAKS"
Dim lR As Long, Found1 As Range, Found2 As Range
Set Found1 = Columns("A").Find(Find1)
If Not Found1 Is Nothing Then
    lR = Found1.Row
Else
    MsgBox "Can't find " & Find1 & " in column A - exiting sub"
    Exit Sub
End If
Application.ScreenUpdating = False
Range("B8").FormulaR1C1 = "=SUM(RC[1]:RC[76])"
Range("B8", "B" & lR).FillDown
Set Found2 = Columns("A").Find(Find2)
If Not Found2 Is Nothing Then
    lR = Found2.Row
    If Cells(8, "B") <> 0 Then
        Cells(lR, "B").Value = 100 * (Cells(lR, "B").Offset(-1, 0) / Cells(8, "B"))
    Else
        MsgBox "Cell B8 has a zero value - can't divide by 0 - exiting sub"
        Application.ScreenUpdating = True
        Exit Sub
    End If
Else
    MsgBox "Can't find " & Find2 & " in column A - exiting sub"
    Application.ScreenUpdating = True
End If

End Sub

 

[Fluff]

How about

   Dim fnd As Range
   Set fnd = Range("A:A").Find("Total Unapproved Indirect Labor", , , xlWhole, , , False, , False)
   Range("B8:B" & fnd.Row).FormulaR1C1 = "=SUM(RC[1]:RC[76])"
   Set fnd = Range("A:A").Find("% INDIRECT LESS BREAKS", , , xlWhole, , , False, , False)
   fnd.Offset(, 1).FormulaR1C1 = "=r[-1]c/r8c2*100"

 

[JoeMo]

Just noticed that you want a formula not just the value in col B. Here's a revision of code in post #4 .

Sub test()
Const Find1 As String = "Total Unapproved Indirect Labor"
Const Find2 As String = "% INDIRECT LESS BREAKS"
Dim lR As Long, Found1 As Range, Found2 As Range
Set Found1 = Columns("A").Find(Find1)
If Not Found1 Is Nothing Then
    lR = Found1.Row
Else
    MsgBox "Can't find " & Find1 & " in column A - exiting sub"
    Exit Sub
End If
Application.ScreenUpdating = False
Range("B8").FormulaR1C1 = "=SUM(RC[1]:RC[76])"
Range("B8", "B" & lR).FillDown
Set Found2 = Columns("A").Find(Find2)
If Not Found2 Is Nothing Then
    lR = Found2.Row
    If Cells(8, "B") <> 0 Then
        Cells(lR, "B").Formula = "=100 *(" & Cells(lR, "B").Offset(-1, 0).Address(0, 0) & "/B8)"
    Else
        MsgBox "Cell B8 has a zero value - can't divide by 0 - exiting sub"
        Application.ScreenUpdating = True
        Exit Sub
    End If
Else
    MsgBox "Can't find " & Find2 & " in column A - exiting sub"
    Application.ScreenUpdating = True
End If

End Sub

 

[FrenchCelt]

How about

   Dim fnd As Range
   Set fnd = Range("A:A").Find("Total Unapproved Indirect Labor", , , xlWhole, , , False, , False)
   Range("B8:B" & fnd.Row).FormulaR1C1 = "=SUM(RC[1]:RC[76])"
   Set fnd = Range("A:A").Find("% INDIRECT LESS BREAKS", , , xlWhole, , , False, , False)
   fnd.Offset(, 1).FormulaR1C1 = "=r[-1]c/r8c2*100"

This worked perfectly! Thanks so much!

I also appreciate your input, JoeMo. Thanks to you, as well.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[oldbrewer]

whereas old brewer merely got the OP to clarify what he wanted....