vba need help referencing other cells

K

Kelly05201

New Member
Joined
Jun 17, 2016
Messages
29
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:
Code: 
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.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]FIRST NAME[/TD]
[TD]LAST NAME[/TD]
[TD]HORSE NAME[/TD]
[TD]......[/TD]
[TD]HORSE TAG #[/TD]
[TD]POLES[/TD]
[TD]BARRELS[/TD]
[TD]FIGURE 8[/TD]
[TD]SPEAR RINGS[/TD]
[TD]BALL AND CHAIN[/TD]
[TD]KEYHOLE[/TD]
[TD]ZIG ZAG[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]SUSIE[/TD]
[TD]SMITH[/TD]
[TD]VIPER[/TD]
[TD][/TD]
[TD]55[/TD]
[TD]Adult[/TD]
[TD]Adult[/TD]
[TD][/TD]
[TD][/TD]
[TD]Adult[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]SALLY[/TD]
[TD]SMITH[/TD]
[TD]SABER[/TD]
[TD][/TD]
[TD]41[/TD]
[TD]Adult[/TD]
[TD]Adult[/TD]
[TD]Adult[/TD]
[TD]Adult[/TD]
[TD][/TD]
[TD][/TD]
[TD]Adult[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]KAREN[/TD]
[TD]MILLER[/TD]
[TD]KARMA[/TD]
[TD][/TD]
[TD]29[/TD]
[TD]Youth[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Youth[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]BILL[/TD]
[TD]BARKER[/TD]
[TD]ACE[/TD]
[TD][/TD]
[TD]22[/TD]
[TD]Jack Benny[/TD]
[TD]Jack Benny[/TD]
[TD]Jack Benny[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]KAREN[/TD]
[TD]MILLER[/TD]
[TD]ACE[/TD]
[TD][/TD]
[TD]22[/TD]
[TD]Youth[/TD]
[TD]Youth[/TD]
[TD][/TD]
[TD]Youth[/TD]
[TD]Youth[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]....[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]70[/TD]
[TD]JOE[/TD]
[TD]JOHNSON[/TD]
[TD]JACKO[/TD]
[TD][/TD]
[TD]88[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jack Benny[/TD]
[/TR]
</tbody>[/TABLE]

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)
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]FIRST NAME[/TD]
[TD]LAST NAME[/TD]
[TD]HORSE NAME[/TD]
[TD]HORSE TAG #[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]SUSIE[/TD]
[TD]SMITH[/TD]
[TD]VIPER[/TD]
[TD]55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]SALLY[/TD]
[TD]SMITH[/TD]
[TD]SABER[/TD]
[TD]41[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]70[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Sheet name: "Youth BALL AND CHAIN" (Another of 21 possible division & event sheet names)
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FIRST NAME[/TD]
[TD]LAST NAME[/TD]
[TD]HORSE NAME[/TD]
[TD]HORSE TAG #[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]KAREN[/TD]
[TD]MILLER[/TD]
[TD]KARMA[/TD]
[TD]29[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]KAREN[/TD]
[TD]MILLER[/TD]
[TD]ACE[/TD]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]70[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
oops and my apology, I got a little dyslexic defining my LastRow... it should be "SheetTarget"... not "TargetSheet".
 
Upvote 0
What exactly do you need help with?
 
Upvote 0
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!
 
Upvote 0
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").
 
Last edited:
Upvote 0
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"
 
Upvote 0
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.
 
Upvote 0
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.

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Need help with VBA copy if condition is met and clear range error message
Replies
6
Views
803
Yongle
Yongle
K
  • Question Question
UserForm code not playing well with sheet code- OnChange
Replies
3
Views
1K
Kelly05201
K
M
  • Question Question
Need help converting offset formula for dynamic range in vba
2
Replies
14
Views
3K
michaelsmith559
M
K
  • Question Question
Simplify VBA Code (Loop through a column range and assign variables to cell address values)
Replies
2
Views
6K
kathendy
K

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top