Excel宏重复IF和其他 [英] Excel Macro Repetitive IF and Else
问题描述
我目前正在使用Excel VBA宏脚本，它将对活动单元格进行简单的TRUE或False测试。我的问题是，我不能让这个工作，直到列表的结尾。它只运行一次并结束程序。我需要这个VB脚本来执行IF& ELSE测试到列表的底部。
I am currently working on an Excel VBA Macro script where in it will do a simple TRUE or False test to the active cell. My problem is, i cannot make this working until the end of the list. It only run once and ends the program. I need this VB script to perform the IF & ELSE test up to the bottom of the list.
问题描述：
让我们说我有一个A1到A9999及其旁边的日期列表（F1：F9999）还有一个列表，其上有一个文本。 F1：F9999列表仅包含两个值。 （a）同一日期和（b）不是相同的。
Let's say i have a list of dates in A1 to A9999 and beside it (F1:F9999) there's also a list that has a text on it. the F1:F9999 list contains two values only. (a)SAME DATE and (b) NOT THE SAME.
-
在列表F1中执行True或False测试：F9999 。
Perform a True or False test in the List F1:F9999.
如果活动单元格值等于文本SAME DATE（TRUE），它将忽略并移动到列表中的下一个项目，然后执行如果活动单元格值等于文本SAME DATE（FALSE），它将在其上方插入一行，然后移动到下一个列表中的项目然后再执行1号
If the active cell value is equal to the text "SAME DATE" (TRUE), it will ignore and move to the next item in the list then perform again number 1.
这是我的代码！
Sub IFandElseTest()
If ActiveCell.Value = "Same Date" Then
Range(Selection, Cells(ActiveCell.Row, 1)).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(1, 0).Select
Else:
ActiveCell.Offset(1, 0).Select
Range(Selection, Cells(ActiveCell.Row, 1)).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If
End Sub
欣赏你是否可以帮助我。
Appreaciate if you could help me on this.
推荐答案
请试试。
- 您应该避免使用
。选择/ ActiveCell等可能希望看到这个 LINK - 当使用最后一行时，最好不要硬编码值，而是动态地找到最后一行。您可能希望看到此 LINK
- 使用对象，如果当前工作表不是要使用的工作表怎么办？
- 以下FOR循环将从下面遍历该行并向上移动。
- You should avoid using
.Select/ActiveCelletc. You might want to see this LINK - When working with the last row, it's better not to hard code values but dynamically find the last row. You might want to see this LINK
- Work with Objects, what if the current sheet is not the sheet with which you want to work with?
- The below FOR loop will traverse the row from below and move up.
代码
Sub Sample()
Dim ws As Worksheet
Dim LRow As Long, i As Long
Dim insertRange As Range
'~~> Chnage this to the relevant sheet
Set ws = ThisWorkbook.Sheets("Sheet1")
'~~> Work with the relevant sheet
With ws
'~~> Get the last row of the desired column
LRow = .Range("E" & .Rows.Count).End(xlUp).Row
'~~> Loop from last row up
For i = LRow To 1 Step -1
'~~> Check for the condition
'~~> UCASE changes to Upper case
'~~> TRIM removes unwanted space from before and after
If UCase(Trim(.Range("E" & i).Value)) = "SAME DATE" Then
'~~> Insert the rows
.Rows(i).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If
Next i
End With
End Sub
截图：
评论
真的很有效！但是，最后一个修改。在你的代码中：设置ws = ThisWorkbook.Sheets（Sheet1）可以将WS设置为活动工作表。原因是因为工作表的名称是唯一的，也不一致。
It really worked! BUT, one final modification. in your code: Set ws = ThisWorkbook.Sheets("Sheet1") Is it possible is you can set the WS as the Active worksheet. The reason of this is because the name of the worksheet unique and not consistent also.
像我所提到的，在上面的第一个链接在评论中，不要使用 Activesheet 。使用不更改的工作表的 CodeNames 。请参见下面的屏幕截图。
Like I mentioned, in the first link above as well in the comment, do not use Activesheet. Use CodeNames of the sheet which do not change. See the screenshot below.
Blah Blah 是您在工作表选项卡中看到的工作表的名称，但 Sheet1 是 CodeName ，不会改变。即，您可以从 Blah Blah 更改表格的名称，以说 Kareen ，但在VBA编辑器中，您会注意到 Codename 不会更改：）
Blah Blah is the name of the sheet which you see in the worksheet tab but Sheet1 is the CodeName which will not change. i.e. you can change the name of the sheet from Blah Blah to say Kareen but in the VBA editor, you will notice that the Codename doesn't change :)
更改代码
Set ws = ThisWorkbook.Sheets("Sheet1")
to
'~~> Replace Sheet1 with the relevant Code Name
Set ws = [Sheet1]
这篇关于Excel宏重复IF和其他的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
