For all values in a column/Row Part 2

Question

I am extreemly new to VBA so excuse any issues with my question.

My process steps are as follows;

Check all dates within row 3 (at times there are several same dates in the row, i wish to check all) and see if it matches cell A1

if the date matches, check the name in row 4 (directly under the date) matches cell B1.

if both match then write "Yes" directly under, i.e. row 5

if no name found. then add a column with the date and name and write yes

if no date is found, add a column with the date and name and write yes

This will then be looped for all dates and names in column A and B

Example Pre Macro

Example post Macro

My Issues

I can't seem to figure out a way to check all dates before i move on to the next if statement - therefore i end up in a continuous loop and my excel crashes.

Example of what i've done so far


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

You might want to start with some basics - strip out all the blank rows and indent your code properly. It will make it easier to read and debug. — SJR, Nov 05 '20 at 18:40
And read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — SJR, Nov 05 '20 at 18:42
Please retitle to better reflect a more direct question, now that you have finished writing your problem statement. Also, welcome to SO! — Elysiumplain, Nov 05 '20 at 19:07
i was unsure what to title the question as the question topic is quite broad — Newbie, Nov 05 '20 at 19:34
Can there be duplicates of dates and names (i.e. can 02/09/2020 Mark exist two times in column A and B)? — Wizhi, Nov 05 '20 at 22:07
@wizhi - nope there can’t be duplicates of the same date and same person. Currently my thought process is; first I should check if the date exists, if not add a column. Second I should check if the date and name are equal if not add a column for that. Finally check if the date and name match if so write yes — Newbie, Nov 05 '20 at 23:44

score 0 · Accepted Answer · answered Nov 06 '20 at 14:58

I believe i have answered my question using the following code - this works, however, doesn't loop through columns A and 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

For all values in a column/Row Part 2

1 Answers1