Home |
Search |
Today's Posts |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
VB Code
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert a Number Code to a Text Code | Excel Discussion (Misc queries) | |||
Unprotect Code Module in Code | Excel Discussion (Misc queries) | |||
Need 2 add second then third code with first code in the Tab View | Excel Worksheet Functions | |||
copying vba code to a standard code module | Excel Discussion (Misc queries) | |||
Write a code by code | Excel Discussion (Misc queries) |