VBA Newbie Problem

[steviejy2k]

I am a complete newbie to VBA and am trying to make a simple (supposed to be) spreadsheet for people to file expenses with.

I have managed to formulate the spreadsheet so that it looks up £ to € exchange rates for particular days automatically, but I am now trying to make a macro which allows users to insert a new expense into the form.

I require the macro to move a selection labelled TOTALS down a row, and then copy the formulae from other rows down to the new row below. That's not particularly well explained.

Basically i have one row which is formatted correctly with formulae etc, and i want when the user clicks the macro button for the totals (which are two lines beneath) to be moved down a row, and the formatted row with formulae to be copied to the next row below. The important formulae in cells have labels attached so if i can use these it may be easier?? I'm guessing here.

Sorry for the poor explanation.

(P.S. I haven't written any code yet to give you a start as i didn't know where to start.)

Thanks for any help.

 

[onlyadrafter]

Hello,

why not try using the macro recorder, then posting any problems you may have. You may be surprised how quick you pick up VBA.

 

[steviejy2k]

I have tried using the macro recorder but the problem is when i select the cells containing the formulae to be copied, the code references these cell addresses. Therefore when i try to repeat the process for the second time (i.e. a second expense input) noting happens as it overwrites into those cell location again.

When i try with the recorder this is what I get:

Sub Test()
'
' Test Macro
'

'
Application.Goto Reference:="TOTALS"
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Application.Goto Reference:="INTEREST"
Selection.AutoFill Destination:=Range("I7:I8"), Type:=xlFillDefault
Range("I7:I8").Select
Application.Goto Reference:="VAT"
Selection.AutoFill Destination:=Range("M7:M8"), Type:=xlFillDefault
Range("M7:M8").Select
Range("B8").Select
End Sub


The text in red is where i think the problem lies, instead of copying particular cell references, i just want the formula from that cell to be copied down a cell.

Thanks

 

[Jon von der Heyden]

Hello Stevie

Try this:

<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> lRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br>lRow = Range("TOTALS").Row<br><br>Rows(lRow).Insert<br><br>Range("INTEREST").Copy Range("I" & lRow)<br>Range("VAT").Copy Range("M" & lRow)</FONT>

 

[onlyadrafter]

Hello,

What cells do your named areas refer to, at the beginning?

 

[steviejy2k]

Hi Jon,

Thanks very much that is almost perfect. There is one thing however, at the moment when i run that it leaves a line gap after the first row where the formulae are, and then fills them in. So basically i am left with a gap, but for repeats of the process the formulae are placed in the correct place, i.e. the row directly underneath the new one created by your code.

Thanks for your time.

 

[Jon von der Heyden]

Hello Stevie

Assuming that I understand you (not likely due to severe lack of coffee today):


You could change:
lRow = Range("TOTALS").Row

to:
lRow = Range("TOTALS").Offset(-1).Row

and change the -1 to accommodate whatever the space is that you have between your data range and TOTALS range.

 

[steviejy2k]

Thanks very much Jon,

That does exactly what i need it to.

 

[steviejy2k]

One further question,

How can I control the formatting of the copied cells?

i.e. I have the table formatted with borders etc and I want these to remain properly formatted when the new line is inserted. This is how the table looks before the new line is inserted,

expenses_template.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2											Start:	End:
3		Today'sDate:	10/12/2008			Name:	Enteryournamehere			ExpensesPeriod:	08/11/2008
4
5		Date:		ExpenseType:	ExpenseItem:			Amount:	ExchangeRate:		Amount:		VAT:
6
7		08/11/2008			Hotel			50	1.23693				0
8
9							Total:	50		Total:	0	TotalVAT:	0.00
10
Sheet1

This is how it looks afterwards:

expenses_template.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2											Start:	End:
3		Today'sDate:	10/12/2008			Name:	Enteryournamehere			ExpensesPeriod:	08/11/2008
4
5		Date:		ExpenseType:	ExpenseItem:			Amount:	ExchangeRate:		Amount:		VAT:
6
7		08/11/2008			Hotel			50	1.23693				0
8													0
9
10							Total:	50		Total:	0	TotalVAT:	0.00
11
12
Sheet1

As you can see the formatting has not been kept.

Any ideas?

Thanks for your help.

 

[Jon von der Heyden]

Try this:

<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> lRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br>lRow = Range("TOTALS").Offset(-1).Row<br><br>Rows(lRow).Insert<br><br><SPAN style="color:#00007F">With</SPAN> Range("INTEREST")<br>    .Copy Range("I" & lRow)<br>    .EntireRow.Copy<br>    Rows(lRow).PasteSpecial xlPasteFormats<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>Range("VAT").Copy Range("M" & lRow)</FONT>