ExcelBanter - how does a macro use an inputbox ?

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - how does a macro use an inputbox ? (https://www.excelbanter.com/excel-programming/365779-how-does-macro-use-inputbox.html)

Roger[_22_]

how does a macro use an inputbox ?

I have a table which varies in length from 2 rows to 650 rows. There are
always 14 columns. I have a macro which formats the columns and adds a total
line to the last line of the table + 1. The columns have different formats,
so they need to be formatted one by one so using the macro is quicker and
easier. If I use RowNr = InputBox ("input the row number for the totals) in
the macro I can get the RowNr into the code, but how can I use it, Rownr, to
define a particular row or cell, please ?

Roger

Duncan[_5_]

how does a macro use an inputbox ?

RowNr = InputBox("Row Number")
Rows(RowNr).Columns(2).Interior.ColorIndex = 3

and so on...

Duncan

Roger wrote:

I have a table which varies in length from 2 rows to 650 rows. There are
always 14 columns. I have a macro which formats the columns and adds a total
line to the last line of the table + 1. The columns have different formats,
so they need to be formatted one by one so using the macro is quicker and
easier. If I use RowNr = InputBox ("input the row number for the totals) in
the macro I can get the RowNr into the code, but how can I use it, Rownr, to
define a particular row or cell, please ?

Roger

Piotr Lipski

how does a macro use an inputbox ?

On Thu, 29 Jun 2006 13:33:50 +0200, Roger wrote:

I have a table which varies in length from 2 rows to 650 rows. There are
always 14 columns. I have a macro which formats the columns and adds a total
line to the last line of the table + 1. The columns have different formats,
so they need to be formatted one by one so using the macro is quicker and
easier. If I use RowNr = InputBox ("input the row number for the totals) in
the macro I can get the RowNr into the code, but how can I use it, Rownr, to
define a particular row or cell, please ?

For example:

Range("A1").Offset(RowNr,0).Value = SomeValue

--
PL

Don Guillett

how does a macro use an inputbox ?

Why would you do it that way? If you have a line that creates the totals
then excel will already know the row.. Perhaps you should copy/paste your
code here for improvements.

look in vba help for inputbox

--
Don Guillett
SalesAid Software

"Roger" wrote in message
...
I have a table which varies in length from 2 rows to 650 rows. There are
always 14 columns. I have a macro which formats the columns and adds a
total line to the last line of the table + 1. The columns have different
formats, so they need to be formatted one by one so using the macro is
quicker and easier. If I use RowNr = InputBox ("input the row number for
the totals) in the macro I can get the RowNr into the code, but how can I
use it, Rownr, to define a particular row or cell, please ?

Roger

Roger[_22_]

how does a macro use an inputbox ?

That's the problem, the spreadsheet (or code) doesn't know which line is the
last line +1 for the totals so up to now I have been setting the macro to
put the totals on line 650 irrespective of the number of rows in the table,
and then (afterwards) manually deleting the blank rows before I print the
table. I know how to count the rows, and how to input the number using
InputBox, but I don't know how to use it in a line of code which needs to
refer to a particular cell or several columns (but not all in the line)

thanks ... Roger

"Don Guillett" wrote in message
...
Why would you do it that way? If you have a line that creates the totals
then excel will already know the row.. Perhaps you should copy/paste your
code here for improvements.

look in vba help for inputbox

--
Don Guillett
SalesAid Software

"Roger" wrote in message
...
I have a table which varies in length from 2 rows to 650 rows. There are
always 14 columns. I have a macro which formats the columns and adds a
total line to the last line of the table + 1. The columns have different
formats, so they need to be formatted one by one so using the macro is
quicker and easier. If I use RowNr = InputBox ("input the row number for
the totals) in the macro I can get the RowNr into the code, but how can I
use it, Rownr, to define a particular row or cell, please ?

Roger

Duncan[_5_]

how does a macro use an inputbox ?

Does this help?

Dim LastRw
LastRw = Range("A1").End(xlDown).Offset(1, 0).Select
'Activecell is now Column A of the Last Row + 1 (blank line)
ActiveCell.Value = "This is the last empty cell in Column A"

Regards

Duncan

Roger wrote:

That's the problem, the spreadsheet (or code) doesn't know which line is the
last line +1 for the totals so up to now I have been setting the macro to
put the totals on line 650 irrespective of the number of rows in the table,
and then (afterwards) manually deleting the blank rows before I print the
table. I know how to count the rows, and how to input the number using
InputBox, but I don't know how to use it in a line of code which needs to
refer to a particular cell or several columns (but not all in the line)

thanks ... Roger

"Don Guillett" wrote in message
...
Why would you do it that way? If you have a line that creates the totals
then excel will already know the row.. Perhaps you should copy/paste your
code here for improvements.

look in vba help for inputbox

--
Don Guillett
SalesAid Software

"Roger" wrote in message
...
I have a table which varies in length from 2 rows to 650 rows. There are
always 14 columns. I have a macro which formats the columns and adds a
total line to the last line of the table + 1. The columns have different
formats, so they need to be formatted one by one so using the macro is
quicker and easier. If I use RowNr = InputBox ("input the row number for
the totals) in the macro I can get the RowNr into the code, but how can I
use it, Rownr, to define a particular row or cell, please ?

Roger

Duncan[_5_]

how does a macro use an inputbox ?

But you really really should post your code and perhaps we could help
further?, its hard when you cant see the code to suggest the solution.

Duncan

Duncan wrote:

Does this help?

Dim LastRw
LastRw = Range("A1").End(xlDown).Offset(1, 0).Select
'Activecell is now Column A of the Last Row + 1 (blank line)
ActiveCell.Value = "This is the last empty cell in Column A"

Regards

Duncan

Roger wrote:

That's the problem, the spreadsheet (or code) doesn't know which line is the
last line +1 for the totals so up to now I have been setting the macro to
put the totals on line 650 irrespective of the number of rows in the table,
and then (afterwards) manually deleting the blank rows before I print the
table. I know how to count the rows, and how to input the number using
InputBox, but I don't know how to use it in a line of code which needs to
refer to a particular cell or several columns (but not all in the line)

thanks ... Roger

"Don Guillett" wrote in message
...
Why would you do it that way? If you have a line that creates the totals
then excel will already know the row.. Perhaps you should copy/paste your
code here for improvements.

look in vba help for inputbox

--
Don Guillett
SalesAid Software

"Roger" wrote in message
...
I have a table which varies in length from 2 rows to 650 rows. There are
always 14 columns. I have a macro which formats the columns and adds a
total line to the last line of the table + 1. The columns have different
formats, so they need to be formatted one by one so using the macro is
quicker and easier. If I use RowNr = InputBox ("input the row number for
the totals) in the macro I can get the RowNr into the code, but how can I
use it, Rownr, to define a particular row or cell, please ?

Roger

Don Guillett

how does a macro use an inputbox ?

I repeat, you are probably making this harder than it needs to be. Care to
post your code?

--
Don Guillett
SalesAid Software

"Roger" wrote in message
...
That's the problem, the spreadsheet (or code) doesn't know which line is
the last line +1 for the totals so up to now I have been setting the macro
to put the totals on line 650 irrespective of the number of rows in the
table, and then (afterwards) manually deleting the blank rows before I
print the table. I know how to count the rows, and how to input the number
using InputBox, but I don't know how to use it in a line of code which
needs to refer to a particular cell or several columns (but not all in the
line)

thanks ... Roger

"Don Guillett" wrote in message
...
Why would you do it that way? If you have a line that creates the totals
then excel will already know the row.. Perhaps you should copy/paste your
code here for improvements.

look in vba help for inputbox

--
Don Guillett
SalesAid Software

"Roger" wrote in message
...
I have a table which varies in length from 2 rows to 650 rows. There are
always 14 columns. I have a macro which formats the columns and adds a
total line to the last line of the table + 1. The columns have different
formats, so they need to be formatted one by one so using the macro is
quicker and easier. If I use RowNr = InputBox ("input the row number for
the totals) in the macro I can get the RowNr into the code, but how can I
use it, Rownr, to define a particular row or cell, please ?

Roger

Roger[_22_]

how does a macro use an inputbox ?

Sorry if I've been difficult. The code is below. The table is comprised of
41 columns of which some contain dates, some text, and some numbers. Plus at
strategic points in the table the columns have borders. Because I do not
know in advance how many rows are involved the borders are done as part of
the macro on a column basis without defining the number of rows. But then
some of the borders have to be removed so that the print looks okay after
the total line has been inserted. In the macro below I have arbitrarily
selected row 460 to be the header row, because I don't know how to make it
an inputbox or, alternative, to get the spreadsheet to calculate the last
row itself. So I have been running the macro, and then manually deleting
blank rows between the last data row and the header row 460. So now I'm
trying to do it properly and learn as well !!!

thanks for all of your help, it is appreciated ... Roger

Sub MakeTotals()
'
' Make Totals for Report (zonderArb) Macro
' Macro recorded 15/06/02 by RAO
'
' Keyboard Shortcut: Ctrl+Shift+T
' clears borders from rows below total line (row 460) for clean printing
' creates SUM formula and then copies across total line
' creates borders and formats 0.00 where appropriate
' saves worksheet

Worksheets("Report (zonderArb)").Activate
Rows("460:660").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Sheets("Report (zonderArb)").Select
Range("H460").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-457]C:R[-1]C)"
Range("H460").Select
Selection.Copy
Range("I460:AE460").Select
ActiveSheet.Paste
Range("A460:G460").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("H460:O460").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("P460:W460").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("X460:AE460").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("AF460").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("M460:N460").Select
Selection.NumberFormat = "0.00"
Range("U460:V460").Select
Selection.NumberFormat = "0.00"
Range("AC460:AD460").Select
Selection.NumberFormat = "0.00"
Range("G460").Select
ActiveCell.FormulaR1C1 = "Totaals"
With ActiveCell.Characters(Start:=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Sheets("Instructions").Select
Range("K61").Select
ActiveWorkbook.Save
End Sub

"Duncan" wrote in message
oups.com...
But you really really should post your code and perhaps we could help
further?, its hard when you cant see the code to suggest the solution.

Duncan

Duncan wrote:

Does this help?

Dim LastRw
LastRw = Range("A1").End(xlDown).Offset(1, 0).Select
'Activecell is now Column A of the Last Row + 1 (blank line)
ActiveCell.Value = "This is the last empty cell in Column A"

Regards

Duncan

Roger wrote:

That's the problem, the spreadsheet (or code) doesn't know which line
is the
last line +1 for the totals so up to now I have been setting the macro
to
put the totals on line 650 irrespective of the number of rows in the
table,
and then (afterwards) manually deleting the blank rows before I print
the
table. I know how to count the rows, and how to input the number using
InputBox, but I don't know how to use it in a line of code which needs
to
refer to a particular cell or several columns (but not all in the line)

thanks ... Roger

"Don Guillett" wrote in message
...
Why would you do it that way? If you have a line that creates the
totals
then excel will already know the row.. Perhaps you should copy/paste
your
code here for improvements.

look in vba help for inputbox

--
Don Guillett
SalesAid Software

"Roger" wrote in message
...
I have a table which varies in length from 2 rows to 650 rows. There
are
always 14 columns. I have a macro which formats the columns and adds
a
total line to the last line of the table + 1. The columns have
different
formats, so they need to be formatted one by one so using the macro
is
quicker and easier. If I use RowNr = InputBox ("input the row number
for
the totals) in the macro I can get the RowNr into the code, but how
can I
use it, Rownr, to define a particular row or cell, please ?

Roger

Tim Marsh

how does a macro use an inputbox ?

just to turn the problem upside down and confuse everyone, would it be
better to have your totals row at the top of the table and base each of
those totals on dynamic ranges for each of the columns below?

if you always have 14 columns i can't understand why you need vba to set
their formatting. why not pre-set it or use conditional formatting?

i agree, posting your code would help alot!

"Don Guillett" wrote in message
...
I repeat, you are probably making this harder than it needs to be. Care to
post your code?

--
Don Guillett
SalesAid Software

"Roger" wrote in message
...
That's the problem, the spreadsheet (or code) doesn't know which line is
the last line +1 for the totals so up to now I have been setting the
macro to put the totals on line 650 irrespective of the number of rows in
the table, and then (afterwards) manually deleting the blank rows before
I print the table. I know how to count the rows, and how to input the
number using InputBox, but I don't know how to use it in a line of code
which needs to refer to a particular cell or several columns (but not all
in the line)

thanks ... Roger

"Don Guillett" wrote in message
...
Why would you do it that way? If you have a line that creates the totals
then excel will already know the row.. Perhaps you should copy/paste
your code here for improvements.

look in vba help for inputbox

--
Don Guillett
SalesAid Software

"Roger" wrote in message
...
I have a table which varies in length from 2 rows to 650 rows. There are
always 14 columns. I have a macro which formats the columns and adds a
total line to the last line of the table + 1. The columns have different
formats, so they need to be formatted one by one so using the macro is
quicker and easier. If I use RowNr = InputBox ("input the row number for
the totals) in the macro I can get the RowNr into the code, but how can
I use it, Rownr, to define a particular row or cell, please ?

Roger

Don Guillett

how does a macro use an inputbox ?

Roger[_22_]

how does a macro use an inputbox ?

Sorry if I've been difficult. The code is below. The table is comprised of
41 columns of which some contain dates, some text, and some numbers. Plus at
strategic points in the table the columns have borders. Because I do not
know in advance how many rows are involved the borders are done as part of
the macro on a column basis without defining the number of rows. But then
some of the borders have to be removed so that the print looks okay after
the total line has been inserted. In the macro below I have arbitrarily
selected row 460 to be the totals row, because I don't know how to make it
an inputbox or, alternatively, to get the spreadsheet to calculate the last
row itself. So I have been running the macro, and then manually deleting
blank rows between the last data row and the header row 460. So now I'm
trying to do it properly and learn as well !!!

thanks for all of your help, it is appreciated ... Roger

Sub MakeTotals()
'
' Make Totals for Report (zonderArb) Macro
' Macro recorded 15/06/02 by RAO
'
' Keyboard Shortcut: Ctrl+Shift+T
' clears borders from rows below total line (row 460) for clean printing
' creates SUM formula and then copies across total line
' creates borders and formats 0.00 where appropriate
' saves worksheet

Worksheets("Report (zonderArb)").Activate
Rows("460:660").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Sheets("Report (zonderArb)").Select
Range("H460").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-457]C:R[-1]C)"
Range("H460").Select
Selection.Copy
Range("I460:AE460").Select
ActiveSheet.Paste
Range("A460:G460").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("H460:O460").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("P460:W460").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("X460:AE460").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("AF460").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("M460:N460").Select
Selection.NumberFormat = "0.00"
Range("U460:V460").Select
Selection.NumberFormat = "0.00"
Range("AC460:AD460").Select
Selection.NumberFormat = "0.00"
Range("G460").Select
ActiveCell.FormulaR1C1 = "Totaals"
With ActiveCell.Characters(Start:=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Sheets("Instructions").Select
Range("K61").Select
ActiveWorkbook.Save
End Sub

"Don Guillett" wrote in message
...
I repeat, you are probably making this harder than it needs to be. Care to
post your code?

--
Don Guillett
SalesAid Software

"Roger" wrote in message
...
That's the problem, the spreadsheet (or code) doesn't know which line is
the last line +1 for the totals so up to now I have been setting the
macro to put the totals on line 650 irrespective of the number of rows in
the table, and then (afterwards) manually deleting the blank rows before
I print the table. I know how to count the rows, and how to input the
number using InputBox, but I don't know how to use it in a line of code
which needs to refer to a particular cell or several columns (but not all
in the line)

thanks ... Roger

"Don Guillett" wrote in message
...
Why would you do it that way? If you have a line that creates the totals
then excel will already know the row.. Perhaps you should copy/paste
your code here for improvements.

look in vba help for inputbox

--
Don Guillett
SalesAid Software

"Roger" wrote in message
...
I have a table which varies in length from 2 rows to 650 rows. There are
always 14 columns. I have a macro which formats the columns and adds a
total line to the last line of the table + 1. The columns have different
formats, so they need to be formatted one by one so using the macro is
quicker and easier. If I use RowNr = InputBox ("input the row number for
the totals) in the macro I can get the RowNr into the code, but how can
I use it, Rownr, to define a particular row or cell, please ?

Roger

Roger[_22_]

how does a macro use an inputbox ?

Dear Don

thanks for the code, I assume that it replaces what I originally wrote
completely. The only problem is that it won't run. When I run the macro I
get an error message "application-defined or object-defined error" and it
does to the line

..Range(Cells(lr, "h"), Cells(lr, "o")).Borders.LineStyle = xlContinuous

and after as the problem code.

.... Roger

"Don Guillett" wrote in message
...
lr=cells(rows.count,"H").end(xlup).row+1

Should have stopped with this info but it just needed cleaning up

Sub MakeTotals()'try this
'
lr=cells(rows.count,"H").end(xlup).row+1
' Make Totals for Report (zonderArb) Macro
' Macro recorded 15/06/02 by RAO
' Keyboard Shortcut: Ctrl+Shift+T
' clears borders from rows below total line (row 460) for clean printing
' creates SUM formula and then copies across total line
' creates borders and formats 0.00 where appropriate
' saves worksheet
Worksheets("Report (zonderArb)").Rows("460:660").Borders.linestyle =
xlNone
with workSheets("Report (zonderArb)")
. Range("H" & lr).formula="=sum(h1:h" & lr & ")"
. Range("H" & lr).Copy Range(cells(lr,"I"),cells(lr,"ae"))
. Range(cells(lr,"A"),cells(lr,"g")).borders.LineSty le = xlContinuous
. Range(cells(lr,"h"),cells(lr,"o")).borders.LineSty le = xlContinuous
. Range(cells(lr,"p"),cells(lr,"w")).borders.LineSty le = xlContinuous
. Range(cells(lr,"x"),cells(lr,"ae")).borders.LineSt yle = xlContinuous
. Range("AF" & lr).").borders.LineStyle = xlContinuous

. Range(cells(l4,"M"),cells(lr,"n")).NumberFormat = "0.00"
. Range(cells(l4,"u"),cells(lr,"v")).NumberFormat = "0.00"
. Range(cells(l4,"ac"),cells(lr,"ad")).NumberFormat = "0.00"

. Range("G" & LR)= "Totals"

With . Range("G" & LR).Characters(Start:=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With

end with

'Sheets("Instructions").Select
' Range("K61").Select
ActiveWorkbook.Save
End Sub

--
Don Guillett
SalesAid Software

"Roger" wrote in message
...
Sorry if I've been difficult. The code is below. The table is comprised
of 41 columns of which some contain dates, some text, and some numbers.
Plus at strategic points in the table the columns have borders. Because I
do not know in advance how many rows are involved the borders are done as
part of the macro on a column basis without defining the number of rows.
But then some of the borders have to be removed so that the print looks
okay after the total line has been inserted. In the macro below I have
arbitrarily selected row 460 to be the header row, because I don't know
how to make it an inputbox or, alternative, to get the spreadsheet to
calculate the last row itself. So I have been running the macro, and then
manually deleting blank rows between the last data row and the header row
460. So now I'm trying to do it properly and learn as well !!!

thanks for all of your help, it is appreciated ... Roger

Sub MakeTotals()
'
' Make Totals for Report (zonderArb) Macro
' Macro recorded 15/06/02 by RAO
'
' Keyboard Shortcut: Ctrl+Shift+T
' clears borders from rows below total line (row 460) for clean printing
' creates SUM formula and then copies across total line
' creates borders and formats 0.00 where appropriate
' saves worksheet

Worksheets("Report (zonderArb)").Activate
Rows("460:660").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Sheets("Report (zonderArb)").Select
Range("H460").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-457]C:R[-1]C)"
Range("H460").Select
Selection.Copy
Range("I460:AE460").Select
ActiveSheet.Paste
Range("A460:G460").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("H460:O460").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("P460:W460").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("X460:AE460").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("AF460").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("M460:N460").Select
Selection.NumberFormat = "0.00"
Range("U460:V460").Select
Selection.NumberFormat = "0.00"
Range("AC460:AD460").Select
Selection.NumberFormat = "0.00"
Range("G460").Select
ActiveCell.FormulaR1C1 = "Totaals"
With ActiveCell.Characters(Start:=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Sheets("Instructions").Select
Range("K61").Select
ActiveWorkbook.Save
End Sub

"Duncan" wrote in message
oups.com...
But you really really should post your code and perhaps we could help
further?, its hard when you cant see the code to suggest the solution.

Duncan

Duncan wrote:

Does this help?

Dim LastRw
LastRw = Range("A1").End(xlDown).Offset(1, 0).Select
'Activecell is now Column A of the Last Row + 1 (blank line)
ActiveCell.Value = "This is the last empty cell in Column A"

Regards

Duncan

Roger wrote:

That's the problem, the spreadsheet (or code) doesn't know which line
is the
last line +1 for the totals so up to now I have been setting the
macro to
put the totals on line 650 irrespective of the number of rows in the
table,
and then (afterwards) manually deleting the blank rows before I print
the
table. I know how to count the rows, and how to input the number
using
InputBox, but I don't know how to use it in a line of code which
needs to
refer to a particular cell or several columns (but not all in the
line)

thanks ... Roger

"Don Guillett" wrote in message
...
Why would you do it that way? If you have a line that creates the
totals
then excel will already know the row.. Perhaps you should
copy/paste your
code here for improvements.

look in vba help for inputbox

--
Don Guillett
SalesAid Software

"Roger" wrote in message
...
I have a table which varies in length from 2 rows to 650 rows.
There are
always 14 columns. I have a macro which formats the columns and
adds a
total line to the last line of the table + 1. The columns have
different
formats, so they need to be formatted one by one so using the macro
is
quicker and easier. If I use RowNr = InputBox ("input the row
number for
the totals) in the macro I can get the RowNr into the code, but how
can I
use it, Rownr, to define a particular row or cell, please ?

Roger

All times are GMT +1. The time now is 05:29 PM.