[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Excel calculation using VBA

Posted on 2016-09-18
10
Medium Priority
?
77 Views
Last Modified: 2016-10-08
I want to add two cells based on the text in two other cells.  

I have this now that works in the VBA editor.  

Function CalcQtys()
Dim intPrev, intNew
Dim strType, strLocation

intPrev = Range("C4").Value
intNew = Range("F3").Value

strType = Range("L3").Value
strLocation = Range("N3").Value

If (strType = "Adj-In") And (strLocation = "Transfer from Final Inspect") Then
    CalcQtys = intPrev + intNew
End If

End Function

In the spreadsheet cell formula I have this: = CalcQtys().
But the cell displays #VALUE!.

This will end up a multi-level if else-if statement.  I'm new at Excel ...

Thanks,
Brooks
0
Comment
Question by:gbnorton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41803783
Why use VBA for calculations? Excel's inbuilt calculations will be more efficient than coding.

Provide an example workbook
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41803788
The function you posted should work without an issue though it doesn't seems to be useful as this can be achieved with simple IF formula on the sheet.
What's your intention behind this? What are you trying to achieve?
Also what do you mean by multiple If else statement here, how would you incorporate them in the function.
I guess you need a sub-routine rather than a Function.
Better upload a sample workbook with some dummy data and lets know the steps you are trying to automate.
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41803809
If you want to improve what you have then change it to this.

'  Without the "As Integer", the function returns a Variant, which will work
'  but Variants are larger and slower than any other type of variable.
Function CalcQtys() As Integer

' Unless you explicitly declare a type, the variables are Variants
' as described above.
Dim intPrev As Integer, intNew As Integer
Dim strType As String, strLocation As String

intPrev = Range("C4").Value
intNew = Range("F3").Value

strType = Range("L3").Value
strLocation = Range("N3").Value

If (strType = "Adj-In") And (strLocation = "Transfer from Final Inspect") Then
    CalcQtys = intPrev + intNew
End If

' You don't really need any of your variables. Instead of the above you could do 
' the following instead of lines 5 through 18
If (Range("L3").Value = "Adj-In") And (Range("N3").Value = "Transfer from Final Inspect") Then
    CalcQtys =  Range("C4").Value + Range("F3").Value
End If

End Function

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 22

Expert Comment

by:Roy Cox
ID: 41803823
I still cannot see the advantage of VBA here, especially as the ranges are hard coded.
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41803838
I still cannot see the advantage of VBA here, especially as the ranges are hard coded.
I agree (unless there's more to it) and I was just trying point out a few coding improvements.
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41804293
martin, I'm not faulting your code, I just think it is totally unnecessary. So many people seem to think VBA is the answer to all problems.
0
 

Author Comment

by:gbnorton
ID: 41804727
Thanks for your comments.  At this point, I'm just trying to get some VBA to work.  I've seen that VBA is not the way to go in this case.  I just want to learn how to use it.  The example is uploaded.  The function is in cell C3.  My goal is to add F3 and C4 and display the result in C3.
Thanks,
Brooks
Excel-vba.xlsm
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41804763
It seems that you need the formulas for C3, C5 and C7 based on the conditions for columns L and N. Right?
So as per your description if C3 = F3 + C4, what formula do you want for C5 and C7 then?
0
 
LVL 49

Accepted Solution

by:
Martin Liss earned 2000 total points (awarded by participants)
ID: 41804797
Function CalcQtys() As Integer
Dim lngCurRow As Long
lngCurRow = Application.Caller.Row
If (Range("L" & lngCurRow).Value = "Adj-In") And (Range("N" & lngCurRow).Value = "Transfer from Final Inspect") Then
    CalcQtys = Range("C" & lngCurRow + 1).Value + Range("F" & lngCurRow).Value
End If
End Function

Open in new window

0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41834898
The OP insists that he wants VBA so Martin's final Function does as asked.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question