Calculate Cell Values

[IanT]

Hi

I have found this code which calculates a cell value but the code is restricted to Range("B1:B3"), my problem is that the column range can change depending on data exported to excel, can anyone advise on how to overcome this!


Dim rng As Range
Dim Lraw As Long

'Calculate moving average
Set rng = Range("B1:B3")

For Lraw = 3 To 12
Cells(Lraw, "c").Value = WorksheetFunction.Sum(rng) / 3
Set rng = rng.Offset(1, 0)
Next Lraw

 

[chergh]

When you say the colum range can change do you mean the column itself could be in row A, B, C etc or the number of rows in the column can change or both?

 

[IanT]

Hi

The rows can change not the column.

 

[shades]

Howdy. You can add a couple of steps to find the next row after last entry.

Dim lngNextRow 
   lngNextRow = Work.Cells(Rows.Count, 2).End(xlUp).Row + 1

Set rng = Range("B" & lngNextRow)

________
TOYOTA WISH HISTORY

 

[jyjmsofficeguru]

Find Last Cell

Hi

I have found this code which calculates a cell value but the code is restricted to Range("B1:B3"), my problem is that the column range can change depending on data exported to excel, can anyone advise on how to overcome this!


Dim rng As Range
Dim Lraw As Long

'Calculate moving average
Set rng = Range("B1:B3")

For Lraw = 3 To 12
Cells(Lraw, "c").Value = WorksheetFunction.Sum(rng) / 3
Set rng = rng.Offset(1, 0)
Next Lraw

Dear,

There are lots of rows in excel.
You should left some row blank from the top. These rows help to find out such problems.
You want to know last most row after the used row in any column.
First of all, understand that any data has definite begining.
Put Count() or CountA() function (as per requiement and data type) in Row 1 of the column you want to find out last row.

e.g. I have employee master. Each employee data is being controlled through a user form. For creating new employee, I use following technique:

1: => My employee code column is 'C', in the Sheet named 'Empmast'. Do not change name of the sheet!!! Each-n-every worksheet is treated as Microsoft Excel Object. (This name can be changed in VBA Editor)

2: => Data Statrting point row is 8

3: => In cell 'C1' formula used 'CountA($C$9:$C$25000)'
Clerifications:
a) Row No: 8 contains Data Label (Field Name)
b) Employee code is made of alpha-numeric data

4: => I have written following code to find out last row:

Sub GetLastRow()
Dim TotRow, DataPutRow
Dim Rng As Range
TotRow = Empmast.Range("C1").Value
DataPutRow = TotRow + 9
Set Rng = Empmast.Range("C9:C" & DataPutRow)
.
.
End Sub

 

[chergh]

I would do it like this:

Dim rng As Range
Dim Lraw As Long
dim lastrow as Long
dim lastrow as long
dim ws as worksheet
dim wb as workbook

set wb = workbooks("your_workbook_name")
set ws = wb.worksheets("worksheet_name")

lastrow = ws.range("B1").End(xldown).row

'Calculate moving average
Set rng = ws.Range("B1:B" & lastrow)

For Lraw = 3 To 12
Cells(Lraw, "c").Value = WorksheetFunction.Sum(rng) / 3
Set rng = rng.Offset(1, 0)
Next Lraw

end sub

 

[shades]

The problem with doing this:

lastrow = ws.range("B1").End(xldown).row

is you might run into a blank space, then your code will give incorrect results.
________
ROBERT B. EVANS