writing sum formula code in VBA Excel 2007

[Camima]

I am trying to write a macro for a time card. I have a column for total hours and I want to write a code that puts a formula in the total hours column for the number of hours work each day. My question is I wrote some code for it and it works for all sheets except for sheet 1.

My code is:

Range("G6:G10").Formula = "=C6-B6+E6-D6+F6-E6)*24"

Why doesn't it work in sheet 1? In sheet 1 it shows "########" in the cells but shows the formula in the formula bar.

Help

 

[Michael M]

Hi Camima and welcome to the Board
Try making the column a little bit wider, going up to the column header ( where the letters are) and grabbing the RH side divider and dragging it to the right until the ##### disappears.

 

[Michael M]

Also your formula won't work because it's missing a bracket

Range("G6:G10").Formula = "=(C6-B6+E6-D6+F6-E6)*24"

 

[Camima]

I do have the first bracket in my formula in my code. I just typed it wrong when I submitted my thread.

I did try the column adjustment and still didn't work. I works in the subsequent sheets after sheet 1, but doesn't work in sheet 1. That is what I am trying to figure out. Why it doesn't work in sheet 1.

Here is my entire code for my time card:

Sub Timecard()
'
' Timecard Macro
' AppleOne Timecard for personal record
'

'
ActiveCell.FormulaR1C1 = "Name"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Client Name"
Range("F2").Select
ActiveCell.FormulaR1C1 = "Client Address"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Date"
Range("B5").Select
ActiveCell.FormulaR1C1 = "Time In"
Range("C5").Select
ActiveCell.FormulaR1C1 = "Time Out"
Range("D5").Select
ActiveCell.FormulaR1C1 = "Time In"
Range("E5").Select
ActiveCell.FormulaR1C1 = "Time Out"
Range("F5").Select
ActiveCell.FormulaR1C1 = "O/T"
Range("G5").Select
ActiveCell.FormulaR1C1 = "Total Hrs"
Range("G6").Select
Range("G6:G10").Formula = "=(C6-B6+E6-D6+F6-E6)*24"
Range("D11").Select
ActiveCell.FormulaR1C1 = "Total Hrs for W/E"
Range("G11").Formula = "=sum(G6:G10)"
Range("A6").Select
End Sub

Anyone have any suggestions as to why it doesn't work in sheet 1?

Thank you.

 

[Michael M]

works fine for me, with a minor change

Sub Timecard()
Range("A1").FormulaR1C1 = "Name"
Range("F1").FormulaR1C1 = "Client Name"
Range("F2").FormulaR1C1 = "Client Address"
Range("A5").FormulaR1C1 = "Date"
Range("B5").FormulaR1C1 = "Time In"
Range("C5").FormulaR1C1 = "Time Out"
Range("D5").FormulaR1C1 = "Time In"
Range("E5").FormulaR1C1 = "Time Out"
Range("F5").FormulaR1C1 = "O/T"
Range("G5").FormulaR1C1 = "Total Hrs"
Range("G6:G10").Formula = "=(C6-B6+E6-D6+F6-E6)*24"
Range("D11").FormulaR1C1 = "Total Hrs for W/E"
Range("G11").Formula = "=sum(G6:G10)"
End Sub

 

[Peter_SSs]

A few comments:

1. My guess about the ### is that on Sheet1 if you open out column G a lot wider you may find that the cells display #VALUE!
If so, that would indicate that somewhere in columns B:F there is a text value rather than numerical, possibly just a space character in a cell.
To check, in one of the ### rows select all the cells in that row from B:F and hit the Delete key. Does the ### disappear from column G and become 0?

2. Michael has already effectively pointed out that you do not need to actually select cells to work with them. Selecting is generally not required and slows your code. However, in addition, since you are not actually putting formulas in most of the cells, why use .FormulaR1C1? My suggestion would be to use the .Value property.
eg

Range("A1").Value = "Name"
Range("F1").Value = "Client Name"
' etc

3.

Range("G6:G10").Formula = "=(C6-B6+E6-D6+F6-E6)*24"

In the above formula you are adding E6 but also subtracting it, so why have that in the formula at all?
This should do just as well

Range("G6:G10").Formula = "=(C6-B6-D6+F6)*24"

 

[Camima]

Michael and Peter,

Thank you for the help in this code. I appreciate it. I took Michael's code and tried it in a new workbook and it works in all sheets including sheet 1. Can you tell me what was wrong with my original code? I am trying to learning VBA code. I am trying to learn all I can in Excel to increase my skills to advance.



Do either of you know of an excellent book to learn VBA code?

 

[Peter_SSs]

Can you tell me what was wrong with my original code?

I suspect nothing was wrong with your code, I think it was the sheet.
Did you try the things I suggested in my point 1. above?

I am trying to learning VBA code. I am trying to learn all I can in Excel to increase my skills to advance.
Do either of you know of an excellent book to learn VBA code?

Another board member has put together a comprehensive list. You should find some useful things here.

 

[Michael M]

I noticed that the code started with

ActiveCell.FormulaR1C1 = "Name"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Client Name"

without a cell selection so, I changed that to Range("A1") because I didn't know if sheet 1 was the active sheet...