I'm trying to divide Cell C2 by the data in the last row in Column B. Can
anyone help me with the VB coding for this?

ActiveCell.FormulaR1C1 = "=RC[-1]/ "

Also, after I've done the division I'm trying to write code to auto-fill
from cell C2 down to the row that contains the word "Total" in column A. In
other words, if the word "Total" appears in Cell A41 then I would like to
have code that auto-fills cells C2:C41. The problem I'm having is the word
"Total" will appear only once but in different rows within column A each day.
Any thoughts?

Sub missive()
Dim n As Long
n = Cells(Rows.Count, "B").End(xlUp).Row
ActiveCell.Formula = "=C2/B" & n
End Sub


This is the easy part - creating a formula for C2
--
Gary''s Student - gsnu200717


"Stan" wrote:

I'm trying to divide Cell C2 by the data in the last row in Column B. Can
anyone help me with the VB coding for this?

ActiveCell.FormulaR1C1 = "=RC[-1]/ "

Also, after I've done the division I'm trying to write code to auto-fill
from cell C2 down to the row that contains the word "Total" in column A. In
other words, if the word "Total" appears in Cell A41 then I would like to
have code that auto-fills cells C2:C41. The problem I'm having is the word
"Total" will appear only once but in different rows within column A each day.
Any thoughts?

That works great! Can you tell me what code I can use to lock in the last
cell in column B? In other words C2/$B$last cell

"Gary''s Student" wrote:

Sub missive()
Dim n As Long
n = Cells(Rows.Count, "B").End(xlUp).Row
ActiveCell.Formula = "=C2/B" & n
End Sub


This is the easy part - creating a formula for C2
--
Gary''s Student - gsnu200717


"Stan" wrote:

I'm trying to divide Cell C2 by the data in the last row in Column B. Can
anyone help me with the VB coding for this?

ActiveCell.FormulaR1C1 = "=RC[-1]/ "

Also, after I've done the division I'm trying to write code to auto-fill
from cell C2 down to the row that contains the word "Total" in column A. In
other words, if the word "Total" appears in Cell A41 then I would like to
have code that auto-fills cells C2:C41. The problem I'm having is the word
"Total" will appear only once but in different rows within column A each day.
Any thoughts?

Yesterday's response didn't help?

Stan wrote:

I'm trying to divide Cell C2 by the data in the last row in Column B. Can
anyone help me with the VB coding for this?

ActiveCell.FormulaR1C1 = "=RC[-1]/ "

Also, after I've done the division I'm trying to write code to auto-fill
from cell C2 down to the row that contains the word "Total" in column A. In
other words, if the word "Total" appears in Cell A41 then I would like to
have code that auto-fills cells C2:C41. The problem I'm having is the word
"Total" will appear only once but in different rows within column A each day.
Any thoughts?

--

Dave Peterson

Unfortunately it didn't because data appears after the word "Total" in column
A. I'm still trying to find out how to lock a cell by using code. You know
the $B$24.

"Dave Peterson" wrote:

Yesterday's response didn't help?

Stan wrote:

I'm trying to divide Cell C2 by the data in the last row in Column B. Can
anyone help me with the VB coding for this?

ActiveCell.FormulaR1C1 = "=RC[-1]/ "

Also, after I've done the division I'm trying to write code to auto-fill
from cell C2 down to the row that contains the word "Total" in column A. In
other words, if the word "Total" appears in Cell A41 then I would like to
have code that auto-fills cells C2:C41. The problem I'm having is the word
"Total" will appear only once but in different rows within column A each day.
Any thoughts?

--

Dave Peterson

So instead of starting multiple new threads, why not just follow up with more
information?

I'm confused about if you want to divide by the value in the row with total or
the previous row.

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
With .Range("A:A")
Set FoundCell = .Cells.Find(What:="total", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

End With

If FoundCell Is Nothing Then
MsgBox "Total not found in column B!"
Exit Sub
End If


.Range("C2:C" & FoundCell.Row - 1).FormulaR1C1 _
= "=rc[-1]/r" & FoundCell.Row & "c[-1]"
'or??
'= "=rc[-1]/r" & FoundCell.Row - 1 & "c[-1]"

End With
End Sub


Stan wrote:

Unfortunately it didn't because data appears after the word "Total" in column
A. I'm still trying to find out how to lock a cell by using code. You know
the $B$24.

"Dave Peterson" wrote:

Yesterday's response didn't help?

Stan wrote:

I'm trying to divide Cell C2 by the data in the last row in Column B. Can
anyone help me with the VB coding for this?

ActiveCell.FormulaR1C1 = "=RC[-1]/ "

Also, after I've done the division I'm trying to write code to auto-fill
from cell C2 down to the row that contains the word "Total" in column A. In
other words, if the word "Total" appears in Cell A41 then I would like to
have code that auto-fills cells C2:C41. The problem I'm having is the word
"Total" will appear only once but in different rows within column A each day.
Any thoughts?

--

Dave Peterson


--

Dave Peterson

MsgBox "Total not found in column B!"
should be
MsgBox "Total not found in column A!"

Dave Peterson wrote:

So instead of starting multiple new threads, why not just follow up with more
information?

I'm confused about if you want to divide by the value in the row with total or
the previous row.

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
With .Range("A:A")
Set FoundCell = .Cells.Find(What:="total", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

End With

If FoundCell Is Nothing Then
MsgBox "Total not found in column B!"
Exit Sub
End If


.Range("C2:C" & FoundCell.Row - 1).FormulaR1C1 _
= "=rc[-1]/r" & FoundCell.Row & "c[-1]"
'or??
'= "=rc[-1]/r" & FoundCell.Row - 1 & "c[-1]"

End With
End Sub

Stan wrote:

Unfortunately it didn't because data appears after the word "Total" in column
A. I'm still trying to find out how to lock a cell by using code. You know
the $B$24.

"Dave Peterson" wrote:

Yesterday's response didn't help?

Stan wrote:

I'm trying to divide Cell C2 by the data in the last row in Column B. Can
anyone help me with the VB coding for this?

ActiveCell.FormulaR1C1 = "=RC[-1]/ "

Also, after I've done the division I'm trying to write code to auto-fill
from cell C2 down to the row that contains the word "Total" in column A. In
other words, if the word "Total" appears in Cell A41 then I would like to
have code that auto-fills cells C2:C41. The problem I'm having is the word
"Total" will appear only once but in different rows within column A each day.
Any thoughts?

--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

Dave,

Many thanks for all your help! I'm new to VB. I'm actually looking at 2
different problems. Want I would like to do is divide cell B2 by the last
last cell with data in it in column B. The value of the division would be
placed in cell C2.

Then I'm trying to auto-fill column C beginning at cell C2 and going down
until I reach the row in column C that is the same row as column A that has
the word Total in it.


"Dave Peterson" wrote:

So instead of starting multiple new threads, why not just follow up with more
information?

I'm confused about if you want to divide by the value in the row with total or
the previous row.

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
With .Range("A:A")
Set FoundCell = .Cells.Find(What:="total", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

End With

If FoundCell Is Nothing Then
MsgBox "Total not found in column B!"
Exit Sub
End If


.Range("C2:C" & FoundCell.Row - 1).FormulaR1C1 _
= "=rc[-1]/r" & FoundCell.Row & "c[-1]"
'or??
'= "=rc[-1]/r" & FoundCell.Row - 1 & "c[-1]"

End With
End Sub


Stan wrote:

Unfortunately it didn't because data appears after the word "Total" in column
A. I'm still trying to find out how to lock a cell by using code. You know
the $B$24.

"Dave Peterson" wrote:

Yesterday's response didn't help?

Stan wrote:

I'm trying to divide Cell C2 by the data in the last row in Column B. Can
anyone help me with the VB coding for this?

ActiveCell.FormulaR1C1 = "=RC[-1]/ "

Also, after I've done the division I'm trying to write code to auto-fill
from cell C2 down to the row that contains the word "Total" in column A. In
other words, if the word "Total" appears in Cell A41 then I would like to
have code that auto-fills cells C2:C41. The problem I'm having is the word
"Total" will appear only once but in different rows within column A each day.
Any thoughts?

--

Dave Peterson


--

Dave Peterson

I'm confused about what you want.

If you want the last value in column B:

dim LastCellInB as range
with activesheet
set lastcellinB = .cells(.rows.count,"B").end(xlup)
.range("c2").formula = "=B2/" & lastcellinb.address(0,0)
end with

Then you can use the other code to determine the row with Total in it.

Stan wrote:

Dave,

Many thanks for all your help! I'm new to VB. I'm actually looking at 2
different problems. Want I would like to do is divide cell B2 by the last
last cell with data in it in column B. The value of the division would be
placed in cell C2.

Then I'm trying to auto-fill column C beginning at cell C2 and going down
until I reach the row in column C that is the same row as column A that has
the word Total in it.

"Dave Peterson" wrote:

So instead of starting multiple new threads, why not just follow up with more
information?

I'm confused about if you want to divide by the value in the row with total or
the previous row.

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
With .Range("A:A")
Set FoundCell = .Cells.Find(What:="total", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

End With

If FoundCell Is Nothing Then
MsgBox "Total not found in column B!"
Exit Sub
End If


.Range("C2:C" & FoundCell.Row - 1).FormulaR1C1 _
= "=rc[-1]/r" & FoundCell.Row & "c[-1]"
'or??
'= "=rc[-1]/r" & FoundCell.Row - 1 & "c[-1]"

End With
End Sub


Stan wrote:

Unfortunately it didn't because data appears after the word "Total" in column
A. I'm still trying to find out how to lock a cell by using code. You know
the $B$24.

"Dave Peterson" wrote:

Yesterday's response didn't help?

Stan wrote:

I'm trying to divide Cell C2 by the data in the last row in Column B. Can
anyone help me with the VB coding for this?

ActiveCell.FormulaR1C1 = "=RC[-1]/ "

Also, after I've done the division I'm trying to write code to auto-fill
from cell C2 down to the row that contains the word "Total" in column A. In
other words, if the word "Total" appears in Cell A41 then I would like to
have code that auto-fills cells C2:C41. The problem I'm having is the word
"Total" will appear only once but in different rows within column A each day.
Any thoughts?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Sorry about that. Let me try again.

In cell C2 I want the value of cell B2 divided by the last cell in column B.
I have to use the last cell in column B because the number of rows in column
B will change each time.

Once the division is done and the value is in cell C2, I then want to lock
the value in the last row of column B and Auto-fill from cell C2 to the row
that has the word 'Total' in it which resides in column A.

"Dave Peterson" wrote:

I'm confused about what you want.

If you want the last value in column B:

dim LastCellInB as range
with activesheet
set lastcellinB = .cells(.rows.count,"B").end(xlup)
.range("c2").formula = "=B2/" & lastcellinb.address(0,0)
end with

Then you can use the other code to determine the row with Total in it.

Stan wrote:

Dave,

Many thanks for all your help! I'm new to VB. I'm actually looking at 2
different problems. Want I would like to do is divide cell B2 by the last
last cell with data in it in column B. The value of the division would be
placed in cell C2.

Then I'm trying to auto-fill column C beginning at cell C2 and going down
until I reach the row in column C that is the same row as column A that has
the word Total in it.

"Dave Peterson" wrote:

So instead of starting multiple new threads, why not just follow up with more
information?

I'm confused about if you want to divide by the value in the row with total or
the previous row.

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
With .Range("A:A")
Set FoundCell = .Cells.Find(What:="total", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

End With

If FoundCell Is Nothing Then
MsgBox "Total not found in column B!"
Exit Sub
End If


.Range("C2:C" & FoundCell.Row - 1).FormulaR1C1 _
= "=rc[-1]/r" & FoundCell.Row & "c[-1]"
'or??
'= "=rc[-1]/r" & FoundCell.Row - 1 & "c[-1]"

End With
End Sub


Stan wrote:

Unfortunately it didn't because data appears after the word "Total" in column
A. I'm still trying to find out how to lock a cell by using code. You know
the $B$24.

"Dave Peterson" wrote:

Yesterday's response didn't help?

Stan wrote:

I'm trying to divide Cell C2 by the data in the last row in Column B. Can
anyone help me with the VB coding for this?

ActiveCell.FormulaR1C1 = "=RC[-1]/ "

Also, after I've done the division I'm trying to write code to auto-fill
from cell C2 down to the row that contains the word "Total" in column A. In
other words, if the word "Total" appears in Cell A41 then I would like to
have code that auto-fills cells C2:C41. The problem I'm having is the word
"Total" will appear only once but in different rows within column A each day.
Any thoughts?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim wks As Worksheet
Dim LastRowInB As Long

Set wks = ActiveSheet

With wks
With .Range("A:A")
Set FoundCell = .Cells.Find(What:="total", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

End With

If FoundCell Is Nothing Then
MsgBox "Total not found in column A!"
Exit Sub
End If

LastRowInB = .Cells(.Rows.Count, "B").End(xlUp).Row

.Range("C2:C" & FoundCell.Row).FormulaR1C1 _
= "=rc[-1]/r" & LastRowInB & "c[-1]"

End With
End Sub


Stan wrote:

Sorry about that. Let me try again.

In cell C2 I want the value of cell B2 divided by the last cell in column B.
I have to use the last cell in column B because the number of rows in column
B will change each time.

Once the division is done and the value is in cell C2, I then want to lock
the value in the last row of column B and Auto-fill from cell C2 to the row
that has the word 'Total' in it which resides in column A.

"Dave Peterson" wrote:

I'm confused about what you want.

If you want the last value in column B:

dim LastCellInB as range
with activesheet
set lastcellinB = .cells(.rows.count,"B").end(xlup)
.range("c2").formula = "=B2/" & lastcellinb.address(0,0)
end with

Then you can use the other code to determine the row with Total in it.

Stan wrote:

Dave,

Many thanks for all your help! I'm new to VB. I'm actually looking at 2
different problems. Want I would like to do is divide cell B2 by the last
last cell with data in it in column B. The value of the division would be
placed in cell C2.

Then I'm trying to auto-fill column C beginning at cell C2 and going down
until I reach the row in column C that is the same row as column A that has
the word Total in it.

"Dave Peterson" wrote:

So instead of starting multiple new threads, why not just follow up with more
information?

I'm confused about if you want to divide by the value in the row with total or
the previous row.

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
With .Range("A:A")
Set FoundCell = .Cells.Find(What:="total", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

End With

If FoundCell Is Nothing Then
MsgBox "Total not found in column B!"
Exit Sub
End If


.Range("C2:C" & FoundCell.Row - 1).FormulaR1C1 _
= "=rc[-1]/r" & FoundCell.Row & "c[-1]"
'or??
'= "=rc[-1]/r" & FoundCell.Row - 1 & "c[-1]"

End With
End Sub


Stan wrote:

Unfortunately it didn't because data appears after the word "Total" in column
A. I'm still trying to find out how to lock a cell by using code. You know
the $B$24.

"Dave Peterson" wrote:

Yesterday's response didn't help?

Stan wrote:

I'm trying to divide Cell C2 by the data in the last row in Column B. Can
anyone help me with the VB coding for this?

ActiveCell.FormulaR1C1 = "=RC[-1]/ "

Also, after I've done the division I'm trying to write code to auto-fill
from cell C2 down to the row that contains the word "Total" in column A. In
other words, if the word "Total" appears in Cell A41 then I would like to
have code that auto-fills cells C2:C41. The problem I'm having is the word
"Total" will appear only once but in different rows within column A each day.
Any thoughts?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Dave,

Perfect sir!!! That works exactly how I need it to!!!

One thing I failed to mention is that column C needs to be formated as a
percent. Is there any easy way to do this with code after column C
auto-fills?

"Dave Peterson" wrote:

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim wks As Worksheet
Dim LastRowInB As Long

Set wks = ActiveSheet

With wks
With .Range("A:A")
Set FoundCell = .Cells.Find(What:="total", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

End With

If FoundCell Is Nothing Then
MsgBox "Total not found in column A!"
Exit Sub
End If

LastRowInB = .Cells(.Rows.Count, "B").End(xlUp).Row

.Range("C2:C" & FoundCell.Row).FormulaR1C1 _
= "=rc[-1]/r" & LastRowInB & "c[-1]"

End With
End Sub


Stan wrote:

Sorry about that. Let me try again.

In cell C2 I want the value of cell B2 divided by the last cell in column B.
I have to use the last cell in column B because the number of rows in column
B will change each time.

Once the division is done and the value is in cell C2, I then want to lock
the value in the last row of column B and Auto-fill from cell C2 to the row
that has the word 'Total' in it which resides in column A.

"Dave Peterson" wrote:

I'm confused about what you want.

If you want the last value in column B:

dim LastCellInB as range
with activesheet
set lastcellinB = .cells(.rows.count,"B").end(xlup)
.range("c2").formula = "=B2/" & lastcellinb.address(0,0)
end with

Then you can use the other code to determine the row with Total in it.

Stan wrote:

Dave,

Many thanks for all your help! I'm new to VB. I'm actually looking at 2
different problems. Want I would like to do is divide cell B2 by the last
last cell with data in it in column B. The value of the division would be
placed in cell C2.

Then I'm trying to auto-fill column C beginning at cell C2 and going down
until I reach the row in column C that is the same row as column A that has
the word Total in it.

"Dave Peterson" wrote:

So instead of starting multiple new threads, why not just follow up with more
information?

I'm confused about if you want to divide by the value in the row with total or
the previous row.

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
With .Range("A:A")
Set FoundCell = .Cells.Find(What:="total", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

End With

If FoundCell Is Nothing Then
MsgBox "Total not found in column B!"
Exit Sub
End If


.Range("C2:C" & FoundCell.Row - 1).FormulaR1C1 _
= "=rc[-1]/r" & FoundCell.Row & "c[-1]"
'or??
'= "=rc[-1]/r" & FoundCell.Row - 1 & "c[-1]"

End With
End Sub


Stan wrote:

Unfortunately it didn't because data appears after the word "Total" in column
A. I'm still trying to find out how to lock a cell by using code. You know
the $B$24.

"Dave Peterson" wrote:

Yesterday's response didn't help?

Stan wrote:

I'm trying to divide Cell C2 by the data in the last row in Column B. Can
anyone help me with the VB coding for this?

ActiveCell.FormulaR1C1 = "=RC[-1]/ "

Also, after I've done the division I'm trying to write code to auto-fill
from cell C2 down to the row that contains the word "Total" in column A. In
other words, if the word "Total" appears in Cell A41 then I would like to
have code that auto-fills cells C2:C41. The problem I'm having is the word
"Total" will appear only once but in different rows within column A each day.
Any thoughts?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Do you want all of column C formatted as a percent--or just the range that got
the formula?

.range("C:c").numberformat = "0.00%"
or
.Range("C2:C" & FoundCell.Row).numberformat = "0.00%"

Right before the "end with" line.

Stan wrote:

Dave,

Perfect sir!!! That works exactly how I need it to!!!

One thing I failed to mention is that column C needs to be formated as a
percent. Is there any easy way to do this with code after column C
auto-fills?

"Dave Peterson" wrote:

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim wks As Worksheet
Dim LastRowInB As Long

Set wks = ActiveSheet

With wks
With .Range("A:A")
Set FoundCell = .Cells.Find(What:="total", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

End With

If FoundCell Is Nothing Then
MsgBox "Total not found in column A!"
Exit Sub
End If

LastRowInB = .Cells(.Rows.Count, "B").End(xlUp).Row

.Range("C2:C" & FoundCell.Row).FormulaR1C1 _
= "=rc[-1]/r" & LastRowInB & "c[-1]"

End With
End Sub


Stan wrote:

Sorry about that. Let me try again.

In cell C2 I want the value of cell B2 divided by the last cell in column B.
I have to use the last cell in column B because the number of rows in column
B will change each time.

Once the division is done and the value is in cell C2, I then want to lock
the value in the last row of column B and Auto-fill from cell C2 to the row
that has the word 'Total' in it which resides in column A.

"Dave Peterson" wrote:

I'm confused about what you want.

If you want the last value in column B:

dim LastCellInB as range
with activesheet
set lastcellinB = .cells(.rows.count,"B").end(xlup)
.range("c2").formula = "=B2/" & lastcellinb.address(0,0)
end with

Then you can use the other code to determine the row with Total in it.

Stan wrote:

Dave,

Many thanks for all your help! I'm new to VB. I'm actually looking at 2
different problems. Want I would like to do is divide cell B2 by the last
last cell with data in it in column B. The value of the division would be
placed in cell C2.

Then I'm trying to auto-fill column C beginning at cell C2 and going down
until I reach the row in column C that is the same row as column A that has
the word Total in it.

"Dave Peterson" wrote:

So instead of starting multiple new threads, why not just follow up with more
information?

I'm confused about if you want to divide by the value in the row with total or
the previous row.

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
With .Range("A:A")
Set FoundCell = .Cells.Find(What:="total", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

End With

If FoundCell Is Nothing Then
MsgBox "Total not found in column B!"
Exit Sub
End If


.Range("C2:C" & FoundCell.Row - 1).FormulaR1C1 _
= "=rc[-1]/r" & FoundCell.Row & "c[-1]"
'or??
'= "=rc[-1]/r" & FoundCell.Row - 1 & "c[-1]"

End With
End Sub


Stan wrote:

Unfortunately it didn't because data appears after the word "Total" in column
A. I'm still trying to find out how to lock a cell by using code. You know
the $B$24.

"Dave Peterson" wrote:

Yesterday's response didn't help?

Stan wrote:

I'm trying to divide Cell C2 by the data in the last row in Column B. Can
anyone help me with the VB coding for this?

ActiveCell.FormulaR1C1 = "=RC[-1]/ "

Also, after I've done the division I'm trying to write code to auto-fill
from cell C2 down to the row that contains the word "Total" in column A. In
other words, if the word "Total" appears in Cell A41 then I would like to
have code that auto-fills cells C2:C41. The problem I'm having is the word
"Total" will appear only once but in different rows within column A each day.
Any thoughts?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Dave, you're the best! You write really good code!

Many thanks for all your help!


"Dave Peterson" wrote:

Do you want all of column C formatted as a percent--or just the range that got
the formula?

.range("C:c").numberformat = "0.00%"
or
.Range("C2:C" & FoundCell.Row).numberformat = "0.00%"

Right before the "end with" line.

Stan wrote:

Dave,

Perfect sir!!! That works exactly how I need it to!!!

One thing I failed to mention is that column C needs to be formated as a
percent. Is there any easy way to do this with code after column C
auto-fills?

"Dave Peterson" wrote:

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim wks As Worksheet
Dim LastRowInB As Long

Set wks = ActiveSheet

With wks
With .Range("A:A")
Set FoundCell = .Cells.Find(What:="total", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

End With

If FoundCell Is Nothing Then
MsgBox "Total not found in column A!"
Exit Sub
End If

LastRowInB = .Cells(.Rows.Count, "B").End(xlUp).Row

.Range("C2:C" & FoundCell.Row).FormulaR1C1 _
= "=rc[-1]/r" & LastRowInB & "c[-1]"

End With
End Sub


Stan wrote:

Sorry about that. Let me try again.

In cell C2 I want the value of cell B2 divided by the last cell in column B.
I have to use the last cell in column B because the number of rows in column
B will change each time.

Once the division is done and the value is in cell C2, I then want to lock
the value in the last row of column B and Auto-fill from cell C2 to the row
that has the word 'Total' in it which resides in column A.

"Dave Peterson" wrote:

I'm confused about what you want.

If you want the last value in column B:

dim LastCellInB as range
with activesheet
set lastcellinB = .cells(.rows.count,"B").end(xlup)
.range("c2").formula = "=B2/" & lastcellinb.address(0,0)
end with

Then you can use the other code to determine the row with Total in it.

Stan wrote:

Dave,

Many thanks for all your help! I'm new to VB. I'm actually looking at 2
different problems. Want I would like to do is divide cell B2 by the last
last cell with data in it in column B. The value of the division would be
placed in cell C2.

Then I'm trying to auto-fill column C beginning at cell C2 and going down
until I reach the row in column C that is the same row as column A that has
the word Total in it.

"Dave Peterson" wrote:

So instead of starting multiple new threads, why not just follow up with more
information?

I'm confused about if you want to divide by the value in the row with total or
the previous row.

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
With .Range("A:A")
Set FoundCell = .Cells.Find(What:="total", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

End With

If FoundCell Is Nothing Then
MsgBox "Total not found in column B!"
Exit Sub
End If


.Range("C2:C" & FoundCell.Row - 1).FormulaR1C1 _
= "=rc[-1]/r" & FoundCell.Row & "c[-1]"
'or??
'= "=rc[-1]/r" & FoundCell.Row - 1 & "c[-1]"

End With
End Sub


Stan wrote:

Unfortunately it didn't because data appears after the word "Total" in column
A. I'm still trying to find out how to lock a cell by using code. You know
the $B$24.

"Dave Peterson" wrote:

Yesterday's response didn't help?

Stan wrote:

I'm trying to divide Cell C2 by the data in the last row in Column B. Can
anyone help me with the VB coding for this?

ActiveCell.FormulaR1C1 = "=RC[-1]/ "

Also, after I've done the division I'm trying to write code to auto-fill
from cell C2 down to the row that contains the word "Total" in column A. In
other words, if the word "Total" appears in Cell A41 then I would like to
have code that auto-fills cells C2:C41. The problem I'm having is the word
"Total" will appear only once but in different rows within column A each day.
Any thoughts?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Glad we got it working.

Stan wrote:

Dave, you're the best! You write really good code!

Many thanks for all your help!

"Dave Peterson" wrote:

Do you want all of column C formatted as a percent--or just the range that got
the formula?

.range("C:c").numberformat = "0.00%"
or
.Range("C2:C" & FoundCell.Row).numberformat = "0.00%"

Right before the "end with" line.

Stan wrote:

Dave,

Perfect sir!!! That works exactly how I need it to!!!

One thing I failed to mention is that column C needs to be formated as a
percent. Is there any easy way to do this with code after column C
auto-fills?

"Dave Peterson" wrote:

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim wks As Worksheet
Dim LastRowInB As Long

Set wks = ActiveSheet

With wks
With .Range("A:A")
Set FoundCell = .Cells.Find(What:="total", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

End With

If FoundCell Is Nothing Then
MsgBox "Total not found in column A!"
Exit Sub
End If

LastRowInB = .Cells(.Rows.Count, "B").End(xlUp).Row

.Range("C2:C" & FoundCell.Row).FormulaR1C1 _
= "=rc[-1]/r" & LastRowInB & "c[-1]"

End With
End Sub


Stan wrote:

Sorry about that. Let me try again.

In cell C2 I want the value of cell B2 divided by the last cell in column B.
I have to use the last cell in column B because the number of rows in column
B will change each time.

Once the division is done and the value is in cell C2, I then want to lock
the value in the last row of column B and Auto-fill from cell C2 to the row
that has the word 'Total' in it which resides in column A.

"Dave Peterson" wrote:

I'm confused about what you want.

If you want the last value in column B:

dim LastCellInB as range
with activesheet
set lastcellinB = .cells(.rows.count,"B").end(xlup)
.range("c2").formula = "=B2/" & lastcellinb.address(0,0)
end with

Then you can use the other code to determine the row with Total in it.

Stan wrote:

Dave,

Many thanks for all your help! I'm new to VB. I'm actually looking at 2
different problems. Want I would like to do is divide cell B2 by the last
last cell with data in it in column B. The value of the division would be
placed in cell C2.

Then I'm trying to auto-fill column C beginning at cell C2 and going down
until I reach the row in column C that is the same row as column A that has
the word Total in it.

"Dave Peterson" wrote:

So instead of starting multiple new threads, why not just follow up with more
information?

I'm confused about if you want to divide by the value in the row with total or
the previous row.

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
With .Range("A:A")
Set FoundCell = .Cells.Find(What:="total", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

End With

If FoundCell Is Nothing Then
MsgBox "Total not found in column B!"
Exit Sub
End If


.Range("C2:C" & FoundCell.Row - 1).FormulaR1C1 _
= "=rc[-1]/r" & FoundCell.Row & "c[-1]"
'or??
'= "=rc[-1]/r" & FoundCell.Row - 1 & "c[-1]"

End With
End Sub


Stan wrote:

Unfortunately it didn't because data appears after the word "Total" in column
A. I'm still trying to find out how to lock a cell by using code. You know
the $B$24.

"Dave Peterson" wrote:

Yesterday's response didn't help?

Stan wrote:

I'm trying to divide Cell C2 by the data in the last row in Column B. Can
anyone help me with the VB coding for this?

ActiveCell.FormulaR1C1 = "=RC[-1]/ "

Also, after I've done the division I'm trying to write code to auto-fill
from cell C2 down to the row that contains the word "Total" in column A. In
other words, if the word "Total" appears in Cell A41 then I would like to
have code that auto-fills cells C2:C41. The problem I'm having is the word
"Total" will appear only once but in different rows within column A each day.
Any thoughts?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson