Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Variable VBA Max Formula
Excel2000 I am trying to insert a formula in a cell after inserting values in other cells from a userform. ex: Do While LFound = False 'Encountered a blank record number (assuming end of list on Temp Data Collection Sheet) If IsEmpty(Range("B" & LRow).Value) = True Then LFound = True End If LRow = LRow + 1 Loop LRowNumber = LRow - 1 Range("K" & LRowNumber).Value = tbUV1.Value Range("L" & LRowNumber).Value = tbUV2.Value Range("M" & LRowNumber).Value = tbUV0.Value Range("N" & LRowNumber).Value = tbUV3.Value Range("O" & LRowNumber).Value = tbUV4.Value I want to insert "=Max(K:O & LRowNumber)" into "W & LRowNumber" I have tried: Cells(LRowNumber, 17).Formula = "=MAX(K:O" & LRowNumber & ")" but the cell (W & LRowNumber) on the sheet has formula "=Max(K:O11)" 11 being the current row number. Please help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Variable VBA Max Formula
from
Cells(LRowNumber, 17).Formula = "=MAX(K:O" & LRowNumber & ")" to Cells(LRowNumber, 17).Formula = "=MAX(K" & LRowNumber & _ ":O" & LRowNumber & ")" "Gizmo" wrote: Excel2000 I am trying to insert a formula in a cell after inserting values in other cells from a userform. ex: Do While LFound = False 'Encountered a blank record number (assuming end of list on Temp Data Collection Sheet) If IsEmpty(Range("B" & LRow).Value) = True Then LFound = True End If LRow = LRow + 1 Loop LRowNumber = LRow - 1 Range("K" & LRowNumber).Value = tbUV1.Value Range("L" & LRowNumber).Value = tbUV2.Value Range("M" & LRowNumber).Value = tbUV0.Value Range("N" & LRowNumber).Value = tbUV3.Value Range("O" & LRowNumber).Value = tbUV4.Value I want to insert "=Max(K:O & LRowNumber)" into "W & LRowNumber" I have tried: Cells(LRowNumber, 17).Formula = "=MAX(K:O" & LRowNumber & ")" but the cell (W & LRowNumber) on the sheet has formula "=Max(K:O11)" 11 being the current row number. Please help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Variable VBA Max Formula
Hi all,
For what its worth you can simplify the code to Cells(LRowNumber, 17) = "=MAX(K" & LRowNumber & ":O" & LRowNumber & ")" Which technically means that the code will run faster, which probably is not a big deal. -- Cheers, Shane Devenshire Microsoft Excel MVP Join http://setiathome.berkeley.edu/ and download a free screen saver to help seach for life out there... "Joel" wrote: from Cells(LRowNumber, 17).Formula = "=MAX(K:O" & LRowNumber & ")" to Cells(LRowNumber, 17).Formula = "=MAX(K" & LRowNumber & _ ":O" & LRowNumber & ")" "Gizmo" wrote: Excel2000 I am trying to insert a formula in a cell after inserting values in other cells from a userform. ex: Do While LFound = False 'Encountered a blank record number (assuming end of list on Temp Data Collection Sheet) If IsEmpty(Range("B" & LRow).Value) = True Then LFound = True End If LRow = LRow + 1 Loop LRowNumber = LRow - 1 Range("K" & LRowNumber).Value = tbUV1.Value Range("L" & LRowNumber).Value = tbUV2.Value Range("M" & LRowNumber).Value = tbUV0.Value Range("N" & LRowNumber).Value = tbUV3.Value Range("O" & LRowNumber).Value = tbUV4.Value I want to insert "=Max(K:O & LRowNumber)" into "W & LRowNumber" I have tried: Cells(LRowNumber, 17).Formula = "=MAX(K:O" & LRowNumber & ")" but the cell (W & LRowNumber) on the sheet has formula "=Max(K:O11)" 11 being the current row number. Please help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DDE formula with variable | Excel Worksheet Functions | |||
Variable within a formula | Excel Worksheet Functions | |||
Using a variable in a formula | Excel Discussion (Misc queries) | |||
Using a variable in a VBA formula | Excel Discussion (Misc queries) | |||
Using a second variable within a formula... | Excel Discussion (Misc queries) |