Excel Vba getting value of cell and setting a string

Question

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

seems to work here. Can you show how you set wash and P1/P2? — 99moorem
– 99moorem, Commented Aug 13, 2015 at 15:23
Dim wash As Range. P1 and P2 are just values in the cell, I didn't set them as anything — VBAnoob
– VBAnoob, Commented Aug 13, 2015 at 15:25
I think you mean "P1" rather than P1. But -- what do you want to do in other cases (e.g. "P4")? — John Coleman
– John Coleman, Commented Aug 13, 2015 at 15:25
Yes, I just haven't got to it yet, I tried "P1" it also didn't work. — VBAnoob
– VBAnoob, Commented Aug 13, 2015 at 15:26

John Coleman · Accepted Answer · 2015-08-13 16:49:24Z

2

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

edited Aug 13, 2015 at 16:49

answered Aug 13, 2015 at 15:39

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

VBAnoob Over a year ago

This keeps giving me the value "b" for products2 regardless of what products is. Thank you though, this is getting me closer to what I had imagined

John Coleman Over a year ago

I can't reproduce your problem. If D2 (which is wash.Offset(1,3) if wash is A1) is P4, I see "Products2 = d" displayed. If I change the value in that cell to P5 and rerun the sub I see "Products2 = e" displayed. Are you sure that you have the right cell? Maybe test it by adding the line `MsgBox wash.Offset(1,3).Address'

VBAnoob Over a year ago

Nevermind, GOT IT TO WORK! Thank you very much, I had to set i as something else (I was already using that variable) and forgot to change one part.

Cohan · Accepted Answer · 2015-08-13 15:46:24Z

1

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.

edited Aug 13, 2015 at 15:46

answered Aug 13, 2015 at 15:24

Cohan

4,5942 gold badges25 silver badges40 bronze badges

2 Comments

Cohan Over a year ago

Setting Products = "P2" and running the code gives me a msgbox saying products2 = b

99moorem Over a year ago

maybe the value isnt coming in as a string to compare try Products = cstr(trim(wash.offset(1, 3).Value))

brettdj · Accepted Answer · 2015-08-14 03:21:48Z

0

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

answered Aug 14, 2015 at 3:21

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Collectives™ on Stack Overflow

Excel Vba getting value of cell and setting a string

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related