gpt4 book ai didi

excel - 带有 AND/OR 函数的 VBA Excel If 语句

转载 作者:行者123 更新时间:2023-12-04 15:06:00 25 4
gpt4 key购买 nike

只是想知道你对这件事的看法。所以问题是我正在尝试使用 MultiPage 控件在 UserForm 中显示信息。我有一个存储数据的 Cert 表。所以在Cert表中,数据的个数可以等于123。我已经弄清楚了 13 的流程,但我遇到了 2 的问题。

我的计划是这样的:

  • 如果 data = 2 那么
    • 如果 box = 1box = 2 那么
      • 在第一个和第二个框中显示数据
    • 如果 box = 1box = 3 那么
      • 在第一个和第三个框中显示数据
    • 如果 box = 2box = 3 那么
      • 在第二个和第三个框中显示数据

这是您的指南的视觉效果。

USERFORM:如果数据等于 23,则数据应显示在各自的框中。

UserForm

工作表数据:

Sheet Data

下面的代码是我目前用于 2 的代码。

For r = 9 To Lastrow

If Application.CountIf(Worksheets(ws_output).Columns(3), LRN) = 2 Then 'data

If _
ws.Cells(r, 3) = CStr( ThisWorkbook.Sheets("HOME").Range("K11").value ) And _
( ws.Cells(r, 12).value = 1 Or ws.Cells(r, 12).value = 2 ) _
Then

If ws.Cells(r, 12).value = 1 Then 'show the data with the value of 1
'FIRST BOX
txtBox_LRN.Text = ws.Cells(r, 3).value
txtBox_name.Text = ws.Cells(r, 4).value
txtBox_grd.Text = ws.Cells(r, 5).value

ElseIf ws.Cells(r, 12).value = 2 Then 'show the data with the value of 2
'SECOND BOX
sb_txtBox_LRN.Text = ws.Cells(r, 3).value
sb_txtBox_name.Text = ws.Cells(r, 4).value
sb_txtBox_grd.Text = ws.Cells(r, 5).value
End If

ElseIf _
ws.Cells(r, 3) = CStr(ThisWorkbook.Sheets("HOME").Range("K11").value) And _
( ws.Cells(r, 12).value = 1 Or ws.Cells(r, 12).value = 3 ) _
Then

If ws.Cells(r, 12).value = 1 Then 'show the data with the value of 1
'FIRST BOX
txtBox_LRN.Text = ws.Cells(r, 3).value
txtBox_name.Text = ws.Cells(r, 4).value
txtBox_grd.Text = ws.Cells(r, 5).value

ElseIf ws.Cells(r, 12).value = 2 Then 'show the data with the value of 3
'THIRD BOX
tb_txtBox_LRN.Text = ws.Cells(r, 3).value
tb_txtBox_name.Text = ws.Cells(r, 4).value
tb_txtBox_grd.Text = ws.Cells(r, 5).value
End If

ElseIf _
ws.Cells(r, 3) = CStr( ThisWorkbook.Sheets("HOME").Range("K11").value ) And _
( ws.Cells(r, 12).value = 2 Or ws.Cells(r, 12).value = 3 ) _
Then

If ws.Cells(r, 12).value = 2 Then 'show the data with the value of 2
'SECOND BOX
sb_txtBox_LRN.Text = ws.Cells(r, 3).value
sb_txtBox_name.Text = ws.Cells(r, 4).value
sb_txtBox_grd.Text = ws.Cells(r, 5).value

ElseIf ws.Cells(r, 12).value = 3 Then 'show the data with the value of 3
'THIRD BOX
tb_txtBox_LRN.Text = ws.Cells(r, 3).value
tb_txtBox_name.Text = ws.Cells(r, 4).value
tb_txtBox_grd.Text = ws.Cells(r, 5).value
End If

End If
End If

Next r

If box = 1 or box = 2 ThenIf box = 1 or box = 3 Then 正在工作,但我遇到了 问题如果 box = 2 或 box = 3 那么:

  • 如果 data = 2,它在第一个 IF 语句 中运行,代码如下:

    (ws.Cells(r, 12).value = 1 Or ws.Cells(r, 12).value = 2)
  • 或者如果data = 3,它在这段代码中运行:

    (ws.Cells(r, 12).value = 1 Or ws.Cells(r, 12).value = 3)
  • 但是我怎样才能让它运行呢?:

    (ws.Cells(r, 12).value = 2 Or ws.Cells(r, 12).value = 3) Then

最佳答案

似乎是您的代码在做什么,但我不确定它是否正确...

Dim box, kValue

If Application.CountIf(Worksheets(ws_output).Columns(3), LRN) = 2 Then 'data

kValue = CStr( ThisWorkbook.Sheets("HOME").Range("K11").value )

For r = 9 To Lastrow

If ws.Cells(r, 3) = kValue Then

box = ws.Cells(r, 12).value

If box = 1 Then
txtBox_LRN.Text = ws.Cells(r, 3).value
txtBox_name.Text = ws.Cells(r, 4).value
txtBox_grd.Text = ws.Cells(r, 5).value
Elseif box = 2 Then
sb_txtBox_LRN.Text = ws.Cells(r, 3).value
sb_txtBox_name.Text = ws.Cells(r, 4).value
sb_txtBox_grd.Text = ws.Cells(r, 5).value
Elseif box = 3 Then
tb_txtBox_LRN.Text = ws.Cells(r, 3).value
tb_txtBox_name.Text = ws.Cells(r, 4).value
tb_txtBox_grd.Text = ws.Cells(r, 5).value
End if

end if

Next r
end if

编辑 - 稍短:

Dim box As Long, kValue, pref As String

If Application.CountIf(Worksheets(ws_output).Columns(3), LRN) = 2 Then 'data

kValue = CStr(ThisWorkbook.Sheets("HOME").Range("K11").Value)

For r = 9 To Lastrow
If ws.Cells(r, 3) = kValue Then
box = ws.Cells(r, 12).Value
If box >= 1 And box <= 3 Then
pref = Array("", "sb_", "tb_")(box - 1) 'get the control name prefix
Me.Controls(pref & "txtBox_LRN").Text = ws.Cells(r, 3).Value 'reference controls by name...
Me.Controls(pref & "txtBox_name").Text = ws.Cells(r, 4).Value
Me.Controls(pref & "txtBox_grd").Text = ws.Cells(r, 5).Value
End If
End If
Next r
End If

关于excel - 带有 AND/OR 函数的 VBA Excel If 语句,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/66096138/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com