Excel Vba获取单元格的值并设置字符串

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" ...

我想根据单元格的值将字符串设置为某个字母。单元格应该只有值“P1”,“P2”,“P3”,“P4”。如果单元格是“P1”,那么我想将字符串“products2”设置为“a”,如果它的“P2”我想将“products2”设置为“b”...

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

这是代码,它将获得单元格的正确值,但不会用它来设置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

3 个解决方案

#1

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.)

这是一个可以做你想要的版本,并将它扩展到P3等。我不得不将wash设置到某个位置以使代码工作。它假定您访问的单元格中的值是Pi形式,其中i是整数。它得到i的值,向下移1,然后得到字母表中的字母移位i(所以“a”表示0,“b”表示1,等等)

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

#2

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:

扩展它以便轻松覆盖“P1”到“P4”的一种好方法是使用select语句,如下所示:

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.

阅读时扫描更容易。

#3

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

#1

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

#2