excel - 对于列/行中的所有值第 2 部分

Question

我对 VBA 非常陌生，所以请原谅我的问题。

我的流程步骤如下；

检查第 3 行中的所有日期（有时该行中有几个相同的日期，我希望检查所有日期）并查看它是否与单元格 A1 匹配

如果日期匹配，请检查第 4 行（日期正下方）中的名称是否与单元格 B1 匹配。

如果两者都匹配，则直接在下面写“是”，即第 5 行

如果没有找到名字。然后添加一个包含日期和名称的列并写是

如果没有找到日期，添加一个包含日期和名称的列并写上是

然后将针对 A 列和 B 列中的所有日期和名称进行循环

我的问题

在继续下一个 if 语句之前，我似乎无法找到检查所有日期的方法 - 因此我最终陷入连续循环并且我的 excel 崩溃。

到目前为止我所做的示例

Sub Macro1()





 Dim cel_1 As Range

 Dim cel_2 As Range







For Each cel_1 In Range("3:3")

     If cel_1.Value = Range("A1") Then

     

    cel_1.Range("A2").Select



AddInfo:

'if we find the date then we need to ensure if the team member is already there

     For Each cel_2 In Selection

     



     If cel_2.Value = Range("B1") Then



'if the team member is there we will input all information over current information



     cel_2.Offset(1, 0).Range("A1") = "Yes"



Else

'insert column to the right

ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Insert _

Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove



'label the week number, date and name

ActiveCell.Offset(-1, 1) = ActiveCell.Offset(-1, 0)

ActiveCell.Offset(0, 1) = ActiveCell.Offset(0, 0)

 

 ActiveCell.Offset(1, 1).Select

 

 GoTo AddInfo:





End If

Next cel_2

End If

Next cel_1





End Sub

标签： excelvba

Answer 1

我相信我已经使用以下代码回答了我的问题 - 但是，它不会遍历 A 列和 B 列。

Sub Macro1()

    Dim myPath As String
    Dim myFile As String
    Dim myExtension As String
  
    Dim wb As Workbook
    Dim wb1 As Workbook
    
    Dim i As Variant
    Dim j As Variant
    Dim k As Variant
    
    Dim CountA_Range As Range
    Dim CountB_Range As Range
    Dim n As Range
    Dim cel_1 As Range
    Dim cel_2 As Range
    Dim lookFor As Range
    Dim srchRange As Range

'rerun forces the macro to rerun if it has added a new cloumn of information
Rerun:

'checked if the name and date is already in the table and adds data
For Each cel_1 In Range("3:3")
       If cel_1.Value = Range("A1") And cel_1.Offset(1, 0).Value = Range("B1") Then
         
k = "Match"
i = "Match"
cel_1.Range("A1").Select
End If
Next cel_1

If k = i And k = "Match" Then

ActiveCell.Offset(2, 0).Value = "Yes"

Else

'if the name and data is not in the data then the columns are added
Range("C3").End(xlToRight).Select
Selection.Offset(0, 1) = Range("A1").Value
Selection.Offset(1, 1) = Range("B1").Value

'rerun - now that we've added the column it should populate when we ran
GoTo Rerun:
End If

'sort the dates so the information is in order
Range("C3").End(xlToRight).Select

Range("C3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Sheet5").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet5").Sort.SortFields.Add Key:=ActiveCell.Range _
        ("A1:P1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet5").Sort
        .SetRange ActiveCell.Range("A1:P100")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub