GaryHolc


Prob very simple to do but I cant figure it out. All I want to do is enetr a formula in every cell in a column until I reach the end of the dataset (the data can vary in size and rows).

So lets say I start in cell J2, how do I enter a formula in J2, then move down to J3, J4 etc. until it reaches the end Many thanks.





Re: Excel help needed

MyLady


Hey,

Try this one...

Sub EnterFormulaInCells()
Dim myRng As Range
Dim lastRw As Long
'get the last row with an entry
lastRw = Worksheets("Sheet1").Range("J2").End(xlDown).Row
With Worksheets("Sheet1").Range("J2")
.Formula = "=SUM(A1:B1)"
.AutoFill Destination:=Worksheets("Sheet1") _
.Range("J2:J" & lastRw&)
End With
End Sub

Best Regards

Cathrine






Re: Excel help needed

magicalclick

Manually, you can do,

Select First Cell, Shift and select last cell. To locate last cell, press END and then DOWN on keyboard.

Type your formula

Press Ctrl + Enter to enter the same thing in every selected cell.

This only works with the constant formula. You should use autofill if you want your formula changes repsected to your row possition. Also note that there is a limited amount of formula you can insert. Try not to use them too much.






Re: Excel help needed

GaryHolc

Thanks, this works, BUT, it doesnt stop at the last row, it goes right down to the very last row of Excel!!! How can I get it to stop once it reaches the last row of the data. Does it make any difference I am using a dataset which is being imported direct from SQL...

Gary






Re: Excel help needed

magicalclick

This will give you the last row you are thinking about.

Code Snippet

Function LastRow(Optional rTest As Range) As Long
If IsNull(rTest) Then Set rTest = ActiveSheet.UsedRange
If rTest Is Nothing Then Set rTest = ActiveSheet.UsedRange
Dim lTest As Long
Dim iCol As Range
For Each iCol In rTest.Columns
With rTest.Parent.Cells(Rows.Count, iCol.Column)
If .Value <> "" Then LastRow = .Row: Exit Function
lTest = IIf(.End(xlUp).Row > lTest, .End(xlUp).Row, lTest)
End With
Next
LastRow = lTest
End Function