CODEWITHSUNDEEP
excelvba
VBAnoob
VBAnoob

Reputation: 163

Excel Vba getting value of cell and setting a string

I want to set a string as a certain letter based on the value of a cell. The cell should only have the values, "P1", "P2", "P3", "P4". If the cell is "P1" then I want to set the string "products2" as "a", if its "P2" I want "products2" set as "b" ...

Here is the code, which will get the correct value for the cell, but wont use it to set the products2.

Dim Products As String
Dim Products2 As String

      Products = wash.offset(1, 3).Value

            If Products = P1 Then
                Products2 = "a"
            ElseIf Products = P2 Then
                MsgBox "we got here"
                Products2 = "b"
            End If

            MsgBox "products2 = " & Products2

Upvotes: 0

Views: 4595

Answers (3)

brettdj
brettdj

Reputation: 55682

My attempt

Function StrOut(strIn As String)
StrOut = Chr(96 + CLng(Replace(strIn, "P", vbNullString)))
End Function

test 

Sub TestDaCode()
Debug.Print StrOut("P1")
Debug.Print StrOut("P2")
End Sub

Upvotes: 0

John Coleman
John Coleman

Reputation: 51998

Here is a version that will do what you want, and extend it to P3 etc. I had to set wash to some location to get the code to work. It assumes that the value in the cell you are accessing is of the form Pi where i is an integer. It gets the value of i, shifted down by 1, then obtains gets the letter in the alphabet shifted by i (so "a" for 0, "b" for 1, etc.)

Sub ProdString()
    Dim Products As String
    Dim Products2 As String
    Dim i As Long
    Dim wash as Range

    Set wash = Range("A1")
    Products = wash.Offset(1, 3).Value
    Products = Mid(Trim(Products), 2) 'strip off the "P"
    i = Val(Trim(Products)) - 1
    Products2 = Chr(i + Asc("a"))
    MsgBox "Products2 = " & Products2

End Sub

Upvotes: 2

Cohan
Cohan

Reputation: 4544

The way it exists right now, it is trying to compare products to a variable rather than a string

Dim Products As String
Dim Products2 As String

Products = cstr(trim(wash.offset(1, 3).Value))

If Products = "P1" Then
    Products2 = "a"
ElseIf Products = "P2" Then
    MsgBox "we got here"
    Products2 = "b"
End If

MsgBox "products2 = " & Products2

A good way to extend it so it easily covers "P1" through "P4" would be to use a select statement as follows:

Products = CStr(Trim(wash.Offset(1, 3).value))

Select Case Products
    Case "P1":
        Products2 = "a"
    Case "P2":
        Products2 = "b"
    Case "P3":
        Products2 = "c"
    Case "P4":
        Products2 = "d"
End Select

MsgBox "products2 = " & Products2

It's a lot easier to scan while reading.

Upvotes: 1

Related Questions