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

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

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