vba need help referencing other cells

[Kelly05201]

Hello all.. learning this vba stuff one google search at a time is tough! I'm knee deep into a project that's well beyond my skill set.. but learning fast. The trouble I'm having is how to reference other cells in proper syntax.. and understanding how to specify a range... can someone get me on the right track?
Workbook for my horse club.
My code so far:

Private Sub Worksheet_SelectionChange(ByVal target As Range)
On Error Resume Next
If Not Intersect(target, Range("A2:L70")) Is Nothing Then
    Dim SheetTarget As Worksheet
    Dim Registration As Worksheet
    Dim HorseNumber As Range
    Dim cell As Range
    Dim EntryVerified As Boolean
    
    Const RegistrationName = "Registration"
    
    Dim LastRow As Long
    LastRow = TargetSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    
        For Each cell In Registration.Range("F2:L70") 'test each cell for exisitng matches on TargetSheet
            SheetTarget = ""
            If IsEmpty(cell) = False Then 'rider has entered that event, cell value indicates division
                SheetTarget = cell.Value & " " '& [value of row 1 @ same column]
                'CHECK FOR AN EXACT MATCH ON TargetSheet OF ALL: HORSE NUMBER ("D"), FIRST NAME ("A") AND LAST NAME ("B")
                'For Each HorseNumber in range(SheetTarget("D2:D70"))
                    EntryVerified = False
                    'If HorseNumber = Registration.cell("E" & Row()) And
                    'HorseNumber("A" & Row()) = cell("A" & Row()) And
                    'HorseNumber("B" & Row()) = cell("B" & Row()) Then
                    EntryVerified = True
                    Exit For
                    End If
                
                    If EntryVerified = False Then
                    cell.Offset("A" & Row()).Copy (SheetTarget("A" & LastRow)) 'rider's first name
                    cell.Offset("B" & Row()).Copy (SheetTarget.Range("B" & LastRow)) 'rider's last name
                    cell.Offset("C" & Row()).Copy (SheetTarget.Range("C" & LastRow)) 'horse name
                    cell.Offset("E" & Row()).Copy (SheetTarget.Range("D" & LastRow)) 'horse ID number
                    End If
                Next HorseNumber
            End If
        Next cell

End If
End Sub

Sheet name: "Registration" (The sub will reside in this sheet module)
*note: Cells A1, B1, C1, D1, and E1 are the only cells in the whole sheet that aren't dynamic. All other cell values change daily.

A

B

C

D

E

F

G

H

I

J

K

L

1

FIRST NAME

LAST NAME

HORSE NAME

......

HORSE TAG #

POLES

BARRELS

FIGURE 8

SPEAR RINGS

BALL AND CHAIN

KEYHOLE

ZIG ZAG

2

SUSIE

SMITH

VIPER

55

Adult

Adult

Adult

3

SALLY

SMITH

SABER

41

Adult

Adult

Adult

Adult

Adult

4

KAREN

MILLER

KARMA

29

Youth

Youth

5

BILL

BARKER

ACE

22

Jack Benny

Jack Benny

Jack Benny

6

KAREN

MILLER

ACE

22

Youth

Youth

Youth

Youth

....

70

JOE

JOHNSON

JACKO

88

Jack Benny

<tbody>
</tbody>

The purpose of the sub() is to check the event sheets for the existence of a proper rider and horse entry... kind of self explanatory, but I'm showing the sheets after the sub runs for clarity...

Sheet name: "Adult POLES" (one of 21 possible division & event sheet names)

	A	B	C	D	E	F	G
1	FIRST NAME	LAST NAME	HORSE NAME	HORSE TAG #	...	...	...
2	SUSIE	SMITH	VIPER	55
3	SALLY	SMITH	SABER	41
...
70

<tbody>
</tbody>

Sheet name: "Youth BALL AND CHAIN" (Another of 21 possible division & event sheet names)

	A	B	C	D	E	F	G
	FIRST NAME	LAST NAME	HORSE NAME	HORSE TAG #	...	...	...
1	KAREN	MILLER	KARMA	29	...	...	...
2	KAREN	MILLER	ACE	22
3
...
70

<tbody>
</tbody>

Many thanks to those who took the time to read, and bigger thanks to anyone who can help get me on track !

 

[Kelly05201]

oops and my apology, I got a little dyslexic defining my LastRow... it should be "SheetTarget"... not "TargetSheet".

 

[Norie]

What exactly do you need help with?

 

[Kelly05201]

I'm at a loss on how to refer to:
"the cell at row 1, same column as where my loop is at this moment"
"the cell at column A of the row where my loop is at this moment"
and any other epic fails you spot in my code that could help. Syntax is so tricky to learn!

 

[Norie]

To refer to the cell in row 1 same column use Cells(1, cell.Column).

To refer to the cell in column A same row use Cells(cell.Row, "A").

 

[Kelly05201]

i should be more clear:
the value of the cell at row 1, same column as where my loop is at this moment"
the value of the cell at column A, same row as where my loop is at this moment"

 

[Kelly05201]

ah... that does help. Thanks!

 

[Norie]

In your outer loop you use cell for loop variable.

In the inner loop you use HorseNumber for the loop variable.

I posted the answers based on the outer loop, if you want it for the outer loop replace cell with HorseNumber.

 

[Kelly05201]

Thanks for that observation, Norie. I think I've made the correct adaptation... hit the compile button and didn't get any errors.. going to hit the run button next.

Private Sub Worksheet_Change(ByVal target As Range)
On Error Resume Next
If Not Intersect(target, Range("A2:L70")) Is Nothing Then
    Dim SheetTarget As Worksheet
    Dim Registration As Worksheet
    Dim HorseNumber As Range
    Dim cell As Range
    Dim EntryVerified As Boolean
    
    Const RegistrationName = "Registration"
    
    Dim LastRow As Long
    LastRow = SheetTarget.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    
        For Each cell In Registration.Range("F2:L70") 'test each cell for exisitng matches on SheetTarget
            SheetTarget = ""
            If IsEmpty(cell) = False Then 'rider has entered that event, cell value indicates division
                SheetTarget = cell.Value & " " & cell(1, cell.Column)
                'CHECK FOR AN EXACT MATCH ON TargetSheet OF ALL: HORSE NUMBER ("D"), FIRST NAME ("A") AND LAST NAME ("B")
                For Each HorseNumber In Range(SheetTarget("D2:D70"))
                    EntryVerified = False
                    If HorseNumber = Registration.Cells(cell.Row, "E") And HorseNumber.Cells(HorseNumber.Row, "A") = Cells(cell.Row, "A") And HorseNumber.Cells(HorseNumber.Row, "B") = Cells(cell.Row, "B") Then
                    EntryVerified = True
                    Exit For
                    End If
                
                    If EntryVerified = False Then 'this rider/horse combination hasn't been copied to the event sheet yet.
                    Cells(cell.Row, "A").Copy (SheetTarget("A" & LastRow)) 'rider's first name
                    Cells(cell.Row, "B").Copy (SheetTarget("B" & LastRow)) 'rider's last name
                    Cells(cell.Row, "C").Copy (SheetTarget("C" & LastRow)) 'horse name
                    Cells(cell.Row, "E").Copy (SheetTarget("D" & LastRow)) 'horse ID number
                    End If
                Next HorseNumber
            End If
        Next cell

End If
End Sub

 

[Kelly05201]

Guess I'm not out of the woods yet... no errors, but no results. The sub does nothing.