Can someone explain why this code is writing yes in column 27 instead of column 14?

[supermom28115]

Public Sub birthLoops()
'this code checks for record and birth location notes and copies if needed

Dim x As Range, y As Range, record As Variant, birth As Variant
Dim startrow As Integer, i As Integer, endrow As Integer

    endrow = Cells(Rows.Count, 14).End(xlUp).Row
 
    For i = 3 To endrow
    Set y = Cells(i, 14)
    Set x = Cells(i, 13)
    'for true answers it should evaluate the next line? for false it finds else or end and then the next line?
            If IsEmpty(y(i, 14).Value2) Then    'this is supposed to look at the cell and say true to empty?
                         If LCase(x(i, 13).Value2) = "yes" Then  'this is supposed to look at the value of the cell and if they match say true?
                              Set birth = Range(y(i, 14).Offset(0, 2), y(i, 14).Offset(0, 4))
                              Set record = Range(y(i, 14).Offset(0, -4), y(i, 14).Offset(0, -2))
                              birth.Value = record.Value
                        End If
                        If LCase(y(i, 14).Value2) = "yes" Then
                        Else
                        y(i, 14).Value2 = "yes"  'this is the line, i thought it was not doing this step even though when i stepped through it was doing what i expected
                        End If                             'and was evaluating and, i thought, processing the code. It was after about the 5th time of stepping through and the 
                                                             '2nd time of running the code that I found yes printed in several rows on column AA. 
         End If
    Next i
            
End Sub

I know it has other problems, specifically me having it start at the beginning of the column every time it runs but I just got so frustrated with trying to get the count rows code to work right. I still don't quite understand how the code process works for that particular issue. I don't mind it running repeatedly right now since I only have 10 rows of data right now. I think my row count is also adding a row every time it counts as well. I guess I should also say that I have this set as a table. I thought it would help with coding to be able to name ranges and have them automatically adjust as new records were added, however I ended up throwing the book across my house yesterday when I tried to use named ranges in the code. This is the dropbox link to the file as I am working on it now.

Birth Document Records.xlsm

Shared with Dropbox

www.dropbox.com

 

[Fluff]

This y(i, 14) is looking at column 27 not column 14, it should just be y If IsEmpty(y.Value2) Then

 

[jolivanes]

Re: "throwing the book across my house yesterday"

This is not the way a Supermom should behave. Bad example for the kids.

 

[supermom28115]

This y(i, 14) is looking at column 27 not column 14, it should just be y If IsEmpty(y.Value2) Then

I had all of my code lines with only the variable but the system did not seem to be acknowledging the value in the variable so I did it this way. I have seen other examples of code with it written this way so I must be missing some rule. Are they all wrong? Should I be using this combination of variable and cell address only for specific situations? And this may be the dumb question but why is it looking at 27? 28 I would have maybe pretended to understand but have I created some weird math expression because I was trying to use the immediate window and locals window to try to figure out why it was not putting yes into that cell.

 

[supermom28115]

Re: "throwing the book across my house yesterday"

This is not the way a Supermom should behave. Bad example for the kids.

yes, and my 14 year old son was quick to tell me that I shouldn't behave that way. But he isn't trying to learn code at ?? age either. I was just a little upset with Bill J last night.

 

[Fluff]

y(i,1) is looking at col 14 so you are offsetting that 13 columns which takes you to 27

Are they all wrong?

Depends on what they should be looking at.

 

[supermom28115]

y(i,1) is looking at col 14 so you are offsetting that 13 columns which takes you to 27

I thought you had to use the .offset for that. I use offset a lot and did not know you could do an offset without specifying.

 

[jolivanes]

Re: "why is it looking at 27? 28 I would have maybe pretended to understand"
Difference between Offset and Resize.
Offset(, 1) is the next cell to the right while Resize(, 1) is the same cell in the reference.

 

[MARK858]

I thought you had to use the .offset for that.

See if the link below helps you understand what is happening

Cells Within Ranges

 

[supermom28115]

See if the link below helps you understand what is happening

Cells Within Ranges

I need his book, where is that available at? Thank you so much for the link.