PDA

View Full Version : Solved: VBA summation errors in macro



arunlgt
02-06-2010, 03:03 PM
Hi Everyone i have a VBA question:

I have a column with 1 and 0.5's and I am trying to sum them up,the 1's are getting added and the 0.5's dont seem to,here is my code,can someone please guide me :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Each Click event

If (Target.Row > 2 And Target.Row < (GetColumnCount() + 1)) Then
Dim iColumnCount As Integer

Dim Department As String


Dim iVacationDays1 As Integer
Dim iPersonalDays1 As Integer
Dim iSickDays As Integer
Dim iDeath As Integer
Dim iComp As Integer



Dim sCurrentName As String

sCurrentName = GetTrimCase(Cells(Target.Row, 2))
Department = Cells(Target.Row, 1)

iColumnCount = GetColumnCount

'Loop start after the blue column
For i = 3 To iColumnCount

If GetTrimCase(Cells(i, 2)) = sCurrentName Then
'Simple summation
iVacationDays1 = iVacationDays1 + Cells(i, 7)
iPersonalDays1 = iPersonalDays1 + Cells(i, 8)
iSickDays = iSickDays + Cells(i, 9)
iDeath = iDeath + Cells(i, 10)
iComp = iComp + Cells(i, 11)
End If
Next i
'Assign Value in blue row
Range("A2").FormulaR1C1 = Department
Range("B2").FormulaR1C1 = Cells(Target.Row, 2)
Range("G2").FormulaR1C1 = iVacationDays1
Range("H2").FormulaR1C1 = iPersonalDays1
Range("I2").FormulaR1C1 = iSickDays
Range("J2").FormulaR1C1 = iDeath
Range("K2").FormulaR1C1 = iComp

End If

End Sub

'For String comparison Same case and trimmed value is required
Private Function GetTrimCase(sTemp As String) As String

GetTrimCase = UCase(sTemp)
GetTrimCase = Trim(GetTrimCase)

End Function


'For getting the used columns
Private Function GetColumnCount() As Integer
GetColumnCount = Range("A" & Rows.Count).End(xlUp).Row
End Function

lucas
02-06-2010, 05:32 PM
maybe, if you will post an example workbook with data.

arunlgt
02-06-2010, 05:46 PM
2727


2727


Hi Steve,

Thanks for the reply:

the workbook i am working on attached!

arunlgt
02-06-2010, 05:54 PM
when u select a employee name all his vacations are summed up and diplayed in the blue row.my problem is that the '1' values are getting added and the '0.5' are not.hoping someone can tell me why? i am no VBA expert.

Thanks

lucas
02-06-2010, 06:10 PM
When I click on the name arun on row 39 I get nothing but a -6 under Column G or vacation days taken.

Is this correct and what is it based on?

lucas
02-06-2010, 06:11 PM
that return is in the blue row at row 2. All the rest of the columns to the right of G are 0

arunlgt
02-06-2010, 06:58 PM
the value you see in the blue row: is the summation of all the 1's and 0.5's in column G for all the rows with the employee ame arun(column B)

the blue i am using for summation: in employee arun:in column G:as you can see 6 '-1' : it is adding them up to -6.the values of the 5 '0.5' are not showing up which is the problem i face:

in other words i expect : 6(1) + 5(0.5) = -8.5 to show up under column G in the blue row when i click on 'arun'

all the columns to the right are similar: add all the 1+0.5 and show the summation in the blue row when a employee name is selected.


Please let me know if any clarifications is needed.

Thanks

lucas
02-06-2010, 07:17 PM
I understand now. I'm time crunched now but will look at this when my head clears......

I think anyone can understand the problem now that might come along....

markman123
02-06-2010, 09:49 PM
Hi There,

The reason it doesn't add the decimals is becaus the data type "Integer" only deals with full numbers, see simple example.
Sub test()
Dim r As Variant

r = 0.5 + 3
Debug.Print r
End Sub

A different datatype is required - I've always been told decimal, but this isn't in VBA... 'Variant' will work, but i'm sure someone will tell us the 'most accurate'.

Regards,

Mark

Aussiebear
02-06-2010, 10:07 PM
Changing to variant will sum decimals however the value in G2 still rounds up to nine rather than -8.5

arunlgt
02-07-2010, 05:08 AM
Hi Everyone:

Thanks for your replies,

markman and aussie:

both of you are right,changing it to Variant datatype does the decimal summation,but it rounds it up to the nearest full integer: eg:8.5~9.

Unfortunately for me this is a problem.Is there anyway for me to retain the decimal value in the summation?

Thanks again

arunlgt
02-07-2010, 05:18 AM
ok guys: i got it:
I changed the cell format that display the value to number with precision for that.
To do this you have to:
select that cell and press ctrl+1 and chose number tab in it .
select number in the category press ok .

Thanks for your time!

MaximS
02-07-2010, 05:20 AM
try using Double datatype

arunlgt
02-07-2010, 07:08 AM
Guys: thanks a lot i got the summation to work now: on a related question can someone help me format the information in the blue row:

that is the user clicks a button -->data goes into a new worksheet

and the data in the blue row is displayed in seperate rows in the new worksheet

one row for each cell

any pointers much appreciated

Thanks

lucas
02-07-2010, 11:10 AM
Try this:

Sub Copytosheet3()
Dim i As Long
i = 0
Sheets("Data").Rows(2).Copy
Do
If Sheets("Sheet3").Range("A" & 2 + i).Value <> "" Then
i = i + 1
End If
Loop Until Sheets("Sheet3").Range("A" & 2 + i).Value = ""
Sheets("Data").Rows(2).Copy Destination:=Sheets("Sheet3").Range("A" & 2 + i)
Sheets("Sheet3").Cells.Interior.ColorIndex = xlNone
End Sub

See attached.

lucas
02-07-2010, 11:13 AM
One row for each cell?

What do you mean by that. I missed it the first time I read your post.

arunlgt
02-07-2010, 12:51 PM
lucas :

Thanks for the code.I was interested in displaying the 2 rows i am sending to the new worksheet as columns on them

eg : Employee name code etc ..

arun 800 etc ..

in the new worksheet it looks like this :

column 1 column 2

Employee name arun

code 800

.

.

.



Any pointers you might have?

lucas
02-07-2010, 12:56 PM
You just want to display one selection at a time on the sheet3?

arunlgt
02-07-2010, 01:04 PM
yep,
The data in row 2 i send to a template worksheet 3 and then send that worksheet as an attachment in the mail to those employees who ask me for a record of their vacations,hence in sheet 3 it is always per employee.

But thinking about it,i realised your code does the job for me.

I just created a button on 'Data' worksheet and assigned your sub to it which sends 2nd row data to Sheets 3 and i have a sub that sends the active worksheet as an attachment in the mail.

So the job is done :)

Thanks a lot to you and the others for the effort

much much appreciated from a VBA beginner

Thanks again

lucas
02-07-2010, 01:07 PM
We can put it in columns if you desire. I have to leave for a while but if you change you mind post here and I will assist when I get back.

We can use a template sheet that gets copied each time so you can email the copy of the template.

Using a template, you can format it to look any way you want and the data can go wherever you want it.

Let me know.

arunlgt
02-07-2010, 01:43 PM
Thanks Lucas,

If you have an attractive template that would be fantastic! no worries,whenever you get the time =)

lucas
02-08-2010, 03:43 PM
I don't have anything special but you can see from the code in module2 of the attached that you can make your template look any way you want it.

The template file is hidden and may have data in it when you look at it but that's not a problem because it gets overwritten each time you run it.

just select one so there is data in row two and hit the button.....

arunlgt
02-09-2010, 12:18 PM
Lucas:

Beautiful,thank you! it works well for me: i used this subroutine to send the worksheet data as an email: looks good enough for me :


Sub Send_Range()

Dim SourceWorksheet As Worksheet
Dim ColumnWidth As Double
Dim Column As Long
ActiveWorkbook.EnvelopeVisible = True
' Set the optional introduction field thats adds
' some header text to the email body. It also sets
' the To and Subject lines. Finally the message
' is sent.
Application.DisplayAlerts = False
ThisWorkbook.ActiveSheet.Copy After:=ThisWorkbook.ActiveSheet
Set SourceWorksheet = ActiveSheet
SourceWorksheet.UsedRange.Value = SourceWorksheet.UsedRange.Value
With SourceWorksheet.UsedRange
For Column = .Column To .Column + .Columns.Count - 1
If .Columns(Column).Hidden Then
ColumnWidth = .Columns(Column).Offset(0, 1).ColumnWidth
.Columns(Column).Delete
.Columns(Column).ColumnWidth = ColumnWidth
End If
Next Column
End With
With SourceWorksheet.MailEnvelope
.Introduction = "This is a sample worksheet."
.Item.To = "E-Mail_Address_Here"
.Item.Subject = "My subject"
.Item.Send
End With
SourceWorksheet.Delete
Application.DisplayAlerts = True
End Sub


Thanks a lot for the help!

lucas
02-09-2010, 12:37 PM
Glad to help.

Welcome and I Hope you enjoy our forum.

Couple of things that will make it better for you and everyone else.

When you post code, select it and hit the vba button. That will format the code for the forum as I have done to your post #23.

When you are through and have your solution, be sure to mark your threads solved using the thread tools at the top of the page.

that keeps good people from reading through an entire thread just to find that it's already been resolved.

arunlgt
02-09-2010, 07:52 PM
Done.

Can I say this site has fully lived up to the high recommendation I had heard from my colleagues.

Appreciate the time and effort from everyone,hope I can be useful here sometime as well.

Regards