setting variable direction in visual basic
 

I'm attempting to build an excel workbook for a staff member to use. The
situation is that raw data will come in daily and the number of rows will
vary from day to day. I would like to build a macro that will be able to
select all rows.

For example on day #1, the raw data comes in with 5 colums of data and 100
rows. The macro will format the colums and add column 6. Column 6 is a
formula that must be copied to each of the 100 rows. On day #2, the raw data
comes in with 5 columns of data but 150 rows.

Without a macro, you can easily copy the cell. I would "ctrl C", move left,
"end" down, move right, "end-shift-up", "ctrl V" to fill in the rows.
However, with a macro, it seems to set the range as fixed the first time you
record it. It won't hold the variability of "end".

How can I program the macro to select the entire range regardless of the
number of rows?



Thanks

setting variable direction in visual basic
 

Post the recorded macro I we will make the changes necessary.

"blkane" wrote:

I'm attempting to build an excel workbook for a staff member to use. The
situation is that raw data will come in daily and the number of rows will
vary from day to day. I would like to build a macro that will be able to
select all rows.

For example on day #1, the raw data comes in with 5 colums of data and 100
rows. The macro will format the colums and add column 6. Column 6 is a
formula that must be copied to each of the 100 rows. On day #2, the raw data
comes in with 5 columns of data but 150 rows.

Without a macro, you can easily copy the cell. I would "ctrl C", move left,
"end" down, move right, "end-shift-up", "ctrl V" to fill in the rows.
However, with a macro, it seems to set the range as fixed the first time you
record it. It won't hold the variability of "end".

How can I program the macro to select the entire range regardless of the
number of rows?



Thanks

setting variable direction in visual basic
 

Dim LastRow As Long
Dim LastCol As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Cells(1, LastCol + 1).FormulaR1C1 = "=the_formula_in_R1C1" 'or
'.Cells(1, LastCol + 1).Formula = "=the_formula_in_A1"
.Cells(1, LastCol + 1).AutoFill .Cells(1, LastCol +
1).Resize(LastRow)
End With


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"blkane" wrote in message
...
I'm attempting to build an excel workbook for a staff member to use. The
situation is that raw data will come in daily and the number of rows will
vary from day to day. I would like to build a macro that will be able to
select all rows.

For example on day #1, the raw data comes in with 5 colums of data and 100
rows. The macro will format the colums and add column 6. Column 6 is a
formula that must be copied to each of the 100 rows. On day #2, the raw
data
comes in with 5 columns of data but 150 rows.

Without a macro, you can easily copy the cell. I would "ctrl C", move
left,
"end" down, move right, "end-shift-up", "ctrl V" to fill in the rows.
However, with a macro, it seems to set the range as fixed the first time
you
record it. It won't hold the variability of "end".

How can I program the macro to select the entire range regardless of the
number of rows?



Thanks

setting variable direction in visual basic
 

Here it is. It's probably not a work of art but any input would be helpful.
The section I am referring to starts at
" Range("F2").Select" and ends at " ActiveSheet.Paste"

In the meantime, I'm going to play with the Bob's routine.

Thank you for your input.



Sub worksheet_setup()
'
' worksheet_setup Macro
' Macro recorded 1/21/2008 by D070264
'

'
Cells.Select
Cells.EntireColumn.AutoFit
Columns("B:B").Select
Application.CutCopyMode = False
Selection.NumberFormat = "0"
Columns("D:D").Select
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Selection.Insert Shift:=xlToRight
Columns("C:C").Select
Selection.TextToColumns Destination:=Range("C1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(3, 1)), TrailingMinusNumbers:=True
Range("C1").Select
ActiveCell.FormulaR1C1 = "TRANS CODE"
Range("D1").Select
ActiveCell.FormulaR1C1 = "TRANS DESCR"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Convert Amount"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[-4],1)<""3"",RC[-2]*1,RC[-2]*-1)"
Columns("G:G").Select
Selection.NumberFormat = "#,##0.00_);[Red](#,##0.00)"
Columns("G:G").EntireColumn.AutoFit
Range("G2").Select
Selection.Copy
Range("F2").Select
Selection.End(xlDown).Select
Range("G67").Select
Range(Selection, Selection.End(xlUp)).Select
Range("G3:G67").Select
Range("G67").Activate
ActiveSheet.Paste
Range("G2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("E2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("G:G").Select
Range("G2").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select
End Sub


"Joel" wrote:

Post the recorded macro I we will make the changes necessary.

"blkane" wrote:

I'm attempting to build an excel workbook for a staff member to use. The
situation is that raw data will come in daily and the number of rows will
vary from day to day. I would like to build a macro that will be able to
select all rows.

For example on day #1, the raw data comes in with 5 colums of data and 100
rows. The macro will format the colums and add column 6. Column 6 is a
formula that must be copied to each of the 100 rows. On day #2, the raw data
comes in with 5 columns of data but 150 rows.

Without a macro, you can easily copy the cell. I would "ctrl C", move left,
"end" down, move right, "end-shift-up", "ctrl V" to fill in the rows.
However, with a macro, it seems to set the range as fixed the first time you
record it. It won't hold the variability of "end".

How can I program the macro to select the entire range regardless of the
number of rows?



Thanks

setting variable direction in visual basic
 

Bob, Thanks. I'm something of a novice at these things. I'm a solid power
user of excel but the VB is something else. I'm going to play around with
this and see if I can get it to work.



"Bob Phillips" wrote:

Dim LastRow As Long
Dim LastCol As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Cells(1, LastCol + 1).FormulaR1C1 = "=the_formula_in_R1C1" 'or
'.Cells(1, LastCol + 1).Formula = "=the_formula_in_A1"
.Cells(1, LastCol + 1).AutoFill .Cells(1, LastCol +
1).Resize(LastRow)
End With


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"blkane" wrote in message
...
I'm attempting to build an excel workbook for a staff member to use. The
situation is that raw data will come in daily and the number of rows will
vary from day to day. I would like to build a macro that will be able to
select all rows.

For example on day #1, the raw data comes in with 5 colums of data and 100
rows. The macro will format the colums and add column 6. Column 6 is a
formula that must be copied to each of the 100 rows. On day #2, the raw
data
comes in with 5 columns of data but 150 rows.

Without a macro, you can easily copy the cell. I would "ctrl C", move
left,
"end" down, move right, "end-shift-up", "ctrl V" to fill in the rows.
However, with a macro, it seems to set the range as fixed the first time
you
record it. It won't hold the variability of "end".

How can I program the macro to select the entire range regardless of the
number of rows?



Thanks