Change Position in Excel Spreadsheet

[voskouee]

I am finding the last cell with data with the xlLastCell.

from there i want to move 3 cells on the left and do a sum there.

i know how to do the sum and i can get the code from excel.
the problem is that am exporting different tables with more or less rows so the last cell with date is not the same.. from the xllastcell how do i move left 3 cells?

any ideas?

am searching the definition of Excel object but i cant find anything.. anyone did this before?

thanks in advance...

 

[Moniker]

Have you considered the Offset method, as in:

Range.xlLastCell.Offset( , -3)

Lookup the Offset property in Excel Help.

 

[voskouee]

Thank you so much for the direction!

You are right!

 

[Moniker]

Glad you figured it out.

 

[voskouee]

Monilker...

I va moved to the cell i want. How do i define the cells so i can do the sum function. Every sheet is different number of rows. so i always go to the last cell. offset 3 and do sum there.

thanks

 

[voskouee]

Any ideas why this does not work? any suggestions?

r = ActiveCell.Offset(rowOffset:=3, columnOffset:=-3)
ActiveCell.FormulaR1C1 = "=SUM(R[" & -r & "]C:R[-1]C)"

 

[Moniker]

Monilker...

I va moved to the cell i want. How do i define the cells so i can do the sum function. Every sheet is different number of rows. so i always go to the last cell. offset 3 and do sum there.

thanks

I'm not sure I understand the issue here, but since you can get to the cell you want, use a .Formula = "=SUM(YourRange)" to put the formula in.

 

[voskouee]

The problem is that in order to put .Formula i need the range.

the range is not static cause in every sheet the rows are more or less.
so i offset 3 cells on the left and there is there a way to do sum withouth setting the cells... like do SUM from the cell i am to up

u understand? i dont have static cells for the column i want to add

 

[Moniker]

If you're always going up to the same row -- for example, you're on A500 in one sheet and you want to sum A2:A499 and in another sheet you want to sum A2:A999 -- then just write the range that way:

.Formula = "=SUM(A2:A" & YourCurrentRow-1) & ")"

If the top range is varying then it will require a little more programming. I'd imagine the top range is static but the lower range is not.

 

[voskouee]

the first case is exactly what i want... but how i define the "currentrow"?

 

[Moniker]

Umm, did you search Excel help on that at all? Perhaps .Row?

Straight out of the help file:

Row Property
Returns the number of the first row of the first area in the range. Read-only Long.

Example
This example sets the row height of every other row on Sheet1 to 4 points.

For Each rw In Worksheets("Sheet1").Rows
If rw.Row Mod 2 = 0 Then
rw.RowHeight = 4
End If
Next rw

Since your "range" is a single cell, the first (and only) row returned will be your current row.

 

[voskouee]

i tried the rows property it doesnt work..

this code tries to calculate it but there is an error #NAME on the field after execution..

r = ActiveCell.Activate
ActiveCell.FormulaR1C1 = "=SUM(Q1:Q " & r - 1 & ")"

any ideas?

 

[Bat17]

It will be a lot easier to see what you are doing if you post the whole sub, using a code box to keep the formating.

peter

 

[voskouee]

This is part of my SUb... this is one table only of the 24 i have in the database

Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlWorkbook As Excel.Workbook
Dim acQuery As QueryDef
Dim objRST As Recordset
Dim lvlColumn As Integer
Dim i As Integer
Dim f As Variant
Dim startrange As Excel.Range
Dim lngColumn As Long
Dim r As Long
Dim CrRow As Long

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWorkbook = xlApp.Workbooks.Add

Set xlSheet = xlWorkbook.Sheets(3)
Set xlSheet = xlWorkbook.Sheets.Add
Set xlSheet = xlWorkbook.Sheets(4)
Set xlSheet = xlWorkbook.Sheets.Add
Set xlSheet = xlWorkbook.Sheets(5)
Set xlSheet = xlWorkbook.Sheets.Add
Set xlSheet = xlWorkbook.Sheets(6)
Set xlSheet = xlWorkbook.Sheets.Add
Set xlSheet = xlWorkbook.Sheets(7)
Set xlSheet = xlWorkbook.Sheets.Add
Set xlSheet = xlWorkbook.Sheets(8)
Set xlSheet = xlWorkbook.Sheets.Add
Set xlSheet = xlWorkbook.Sheets(9)
Set xlSheet = xlWorkbook.Sheets.Add
Set xlSheet = xlWorkbook.Sheets(10)
Set xlSheet = xlWorkbook.Sheets.Add
Set xlSheet = xlWorkbook.Sheets(11)
Set xlSheet = xlWorkbook.Sheets.Add
Set xlSheet = xlWorkbook.Sheets(12)
Set xlSheet = xlWorkbook.Sheets.Add
Set xlSheet = xlWorkbook.Sheets(13)
Set xlSheet = xlWorkbook.Sheets.Add
Set xlSheet = xlWorkbook.Sheets(14)
Set xlSheet = xlWorkbook.Sheets.Add
Set xlSheet = xlWorkbook.Sheets(15)
Set xlSheet = xlWorkbook.Sheets.Add
Set xlSheet = xlWorkbook.Sheets(16)
Set xlSheet = xlWorkbook.Sheets.Add
Set xlSheet = xlWorkbook.Sheets(17)
Set xlSheet = xlWorkbook.Sheets.Add
Set xlSheet = xlWorkbook.Sheets(18)
Set xlSheet = xlWorkbook.Sheets.Add
Set xlSheet = xlWorkbook.Sheets(19)
Set xlSheet = xlWorkbook.Sheets.Add
Set xlSheet = xlWorkbook.Sheets(20)
Set xlSheet = xlWorkbook.Sheets.Add
Set xlSheet = xlWorkbook.Sheets(21)
Set xlSheet = xlWorkbook.Sheets.Add
Set xlSheet = xlWorkbook.Sheets(22)
Set xlSheet = xlWorkbook.Sheets.Add
Set xlSheet = xlWorkbook.Sheets(23)
Set xlSheet = xlWorkbook.Sheets.Add



'Table RN00144GE3RFG
Set objRST = Application.CurrentDb.OpenRecordset("SELECT * FROM RN00144GE3RFG;")
Set xlSheet = xlWorkbook.Sheets(1)

With xlSheet
.Range("A2").CopyFromRecordset objRST
.Name = "JO-RN00144GE3"

i = 1

'create the column headings in cells
With objRST
For Each f In .Fields
With xlSheet
.Cells(1, i).Value = f.Name
i = i + 1
End With
Next
End With
End With
'Change the font to bold for the header row
xlSheet.Columns.AutoFit
xlSheet.Range(xlSheet.Cells(1, 1), _
xlSheet.Cells(1, objRST.Fields.Count)).Font.Bold = True
ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(rowOffset:=3, columnOffset:=-3).Activate
r = ActiveCell.Activate
ActiveCell.FormulaR1C1 = "=SUM(Q1:Q " & r - 1 & ")"


Sheets("JO-RN00144GE3").Select
Range("A1:T1").Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With


Hope this is clear.. thanks

 

[Bat17]

Not sure I have the Sum in the right place but I'm sure you can play with it to get it whre you need it.
using this format you should be able to loop through all of your tables if the naming is consitant

Dim lngRows As Long
Dim lngCols As Long

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWorkbook = xlApp.Workbooks.Add
Set xlSheet = xlWorkbook.Sheets.Add(, xlWorkbook.Worksheets(xlWorkbook.Worksheets.Count), 20, xlWorksheet)


'Table RN00144GE3RFG
Set objRST = Application.CurrentDb.OpenRecordset("SELECT * FROM RN00144GE3RFG;")
Set xlSheet = xlWorkbook.Sheets(1)
With xlSheet
    .Range("A2").CopyFromRecordset objRST
    .Name = "JO-RN00144GE3"
    i = 1
    'create the column headings in cells
    With objRST
        For Each f In .Fields
            With xlSheet
                .Cells(1, i).Value = f.Name
                i = i + 1
            End With
        Next
    End With

    'Change the font to bold for the header row
    xlSheet.Columns.AutoFit
    xlSheet.Range(xlSheet.Cells(1, 1), _
    xlSheet.Cells(1, objRST.Fields.Count)).Font.Bold = True
    lngRows = .Range("a2").CurrentRegion.Rows.Count
    lngCols = .Range("a2").CurrentRegion.Columns.Count
    .Cells(lngRows, lngCols - 3).Formula = "=SUM(Q1:Q" & lngRows & ")"
    With .Range("A1:T1").Interior
        .ColorIndex = 15
        .Pattern = xlSolid
    End With
End With

HTH

Peter

 

[voskouee]

the loop for the creation of the sheet works fine.

the formula for the Sum doesnt work.

.range is giving me an error but when i remove the dot its ok..

the sum doesnt work whatever i try. you want to explain to me what u are trying to do?

is there any other way?

 

[Bat17]

.Cells(lngRows, lngCols - 3).Formula = "=SUM(Q1:Q" & lngRows & ")"

this should add to the last row of the region (lngRows) and 3 columns in from the right (lngCols - 3)
"=SUM(Q1:Q" & lngRows & ")" should sum the q column down to lngRows

When you say it doesn't work, what does happen? wrong formula? wrong place? some sort of error?

Peter

 

[voskouee]

it gives an error in excel about circular reference and the code stops there..

 

[Bat17]

which probably means that the offset is not right and the formula is placing itself in the Sum range.

try .Cells(lngRows + 3, lngCols - 3).Formula = "=SUM(Q1:Q" & lngRows & ")"

which should place you clear of existing data and adjust it from there.

Peter

 

[voskouee]

thanks buddy you were right...

it worked after changing the offset to find the empty cell