Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old March 3rd, 2008, 04:44 PM
Authorized User
 
Join Date: Mar 2008
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default VBA function help

Hello,
I want to create a function in vba for excel that uses the equivalent of sumif() function. Here is what I have as an example:

- - 1 - 2 - 3 - 4 -5
1 - A - A - C - D - F
2 - B - B - B - A - A
3 - C - B - A - B - A
4 - D - A - B - D - D
5 - A - C - B - A - D


What I have here are column (1-5) and rows (1-5) in excel that with letter grades. The value of the letter grades are A = 4, B = 3, C = 2, D = 1, F = 0. So i want to get a sum of these values accros for each row. How would I go about making this in vba.
Also, just to make life harder, lets say column 2 had different values for the letter grades such as (A = 12, B = 9, C = 6, D = 3, F = 0) but all the other columns were as before. How would i sum this up. Any help would be great. I am new to vba. Thanks in advance


Carbon_13
__________________
Carbon_13
Reply With Quote
  #2 (permalink)  
Old March 4th, 2008, 08:49 PM
Authorized User
Points: 193, Level: 3
Points: 193, Level: 3 Points: 193, Level: 3 Points: 193, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2007
Location: North East Pennsylvania, USA.
Posts: 48
Thanks: 0
Thanked 4 Times in 4 Posts
Default

carbon_13,

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

Code:
Option Explicit
Function SumColumns(r As Range) As Long
'
' Function created 03/04/2008 by stanleydgromjr
'
' for carbon_13
' http://p2p.wrox.com/post.asp?method=...90&FORUM_ID=72
'
' Formula in cell F1 (copied down):
' =SumColumns($A1:$E1)
'
'
    Dim c
    Dim lngLoopCtr As Long
    Dim lngTotal As Long
    lngTotal = 0
    lngLoopCtr = 1
    For Each c In r.Cells
        Select Case lngLoopCtr
            'value of the letter grades are A = 4, B = 3, C = 2, D = 1, F = 0
            Case 1, 3, 4, 5
                Select Case c
                    Case "A"
                        lngTotal = lngTotal + 4
                        lngLoopCtr = lngLoopCtr + 1
                    Case "B"
                        lngTotal = lngTotal + 3
                        lngLoopCtr = lngLoopCtr + 1
                    Case "C"
                        lngTotal = lngTotal + 2
                        lngLoopCtr = lngLoopCtr + 1
                    Case "D"
                        lngTotal = lngTotal + 1
                        lngLoopCtr = lngLoopCtr + 1
                    Case "F"
                        lngTotal = lngTotal + 0
                        lngLoopCtr = lngLoopCtr + 1
                End Select
            'column 2 had different values for the letter grades such as (A = 12, B = 9, C = 6, D = 3, F = 0)
            Case 2
                Select Case c
                    Case "A"
                        lngTotal = lngTotal + 12
                        lngLoopCtr = lngLoopCtr + 1
                    Case "B"
                        lngTotal = lngTotal + 9
                        lngLoopCtr = lngLoopCtr + 1
                    Case "C"
                        lngTotal = lngTotal + 6
                        lngLoopCtr = lngLoopCtr + 1
                    Case "D"
                        lngTotal = lngTotal + 3
                        lngLoopCtr = lngLoopCtr + 1
                    Case "F"
                        lngTotal = lngTotal + 0
                        lngLoopCtr = lngLoopCtr + 1
                End Select
        End Select
    Next c
    SumColumns = lngTotal
End Function

With your data beginning in row 1, cell A1, enter the following formula in cell F1 (then copy it down):
=SumColumns($A1:$E1)


Have a great day,
Stan





stanleydgromjr

Windows Vista Business and Excel 2003, 2007.
Reply With Quote
  #3 (permalink)  
Old March 5th, 2008, 04:17 PM
Authorized User
 
Join Date: Mar 2008
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you so much for the help. The code was just perfect. I must say you are pretty good with vba. Just wanted to say thanks again.


Carbon_13
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
"Wait" function in VBA wscheiman Access VBA 15 December 18th, 2010 04:16 PM
MS Access VBA Dateddiff Function SKZ Access VBA 5 November 18th, 2008 07:38 AM
Excel 2003 VBA Function RollingWoodFarm Excel VBA 15 August 2nd, 2006 04:24 PM
Create text pop-up in VBA function jimmy_chu Excel VBA 0 December 10th, 2003 11:15 AM



All times are GMT -4. The time now is 11:02 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.