VBA Case Statement

[jayb3369]

I am trying to teach myself VBA for Excel. I am stumped by the following problem:

I want to create a function that will look at the contents of a cell passed as an argument, eg: cprob(A1)

If A1 contains "textstring1", then set B1=C1
If A1 contains "textstring2", then set B1 =D1
If A1 contains "textstring3:, then set B1=.9
etc for other conditions

So I want to look at a text string, and based on that, reference an existing percentage in another cell, or just set the percentage directly.

My problem is I can't figure out how to make the function work for whatever row it's working in. If I am passing the contents of, say, A1 as an argument, how do I tell the VBA code that means we're going to be working with B1, C1, etc? If I pass A2, now we will refer to B2, C2 etc.

Here's what I have:

Function CProb()

Select Case Range("Stage").Value

Case "Approval Expected"

Range("CustomProb").Value = Range("StageProb")

Case "Discussion"

Range("CustomProb").Value = Range("StageProb")

Case "Proposed"

Range("CustomProb").Value = Range("AMProb")

Case "Negotiations"

Range("CustomProb").Value = 0.9

Case "Potential"

Range("CustomProb").Value = 0.2

End Select

End Function

And yes, I am a rank beginner. The last time I wrote a case statement, Pascal was a new language.

 

[dave3009]

Hi and Welcome

Do you need this in VBA or could it be a better option to use a small table and VLOOKUP?

 

[EdFerrero]

A VBA function can take parameters like this;

' Stage = rngInput
' CustomProb = rngOutput
' StageProb = rngSProb
' AMProb = rngAmProb
Function CProb(rngInput As Range, _
              rngOutput As Range, _
              rngSProb As Range, _
              rngAmProb As Range) As Variant
Select Case rngInput.Value
  Case "Approval Expected"
    rngOutput.Value = rngSProb.Value
  Case "Discussion"
    rngOutput.Value = rngSProb.Value
  Case "Proposed"
    rngOutput.Value = rngAmProb.Value
  Case "Negotiations"
    rngOutput.Value = 0.9
  Case "Potential"
    rngOutput.Value = 0.2
  Case Else
    rngOutput.Value = "Error"
End Select
End Function

 

[lenze]

This is not really an atempt to answer your query, but here is some generic code to help illustrate Case Select through a Range

Sub Test()
Dim cl As Range
For Each cl In Range("$A$2:$A" & Cells(Rows.Count, "A").End(xlUp).Row)
Select Case cl
Case "A": Cells(cl.Row, "B") = Cells(cl.Row, "C")
Case "B": Cells(cl.Row, "B") = Cells(cl.Row, "D")
'etc
Case Else:
End Select
End Sub

lenze