Find next value, insert string and reference

[cheekbones3]

Hi all, I'm looking to automate a long and tedious manual process with a macro, and I'm not exactly sure the best way to go about it.

My date is arranged thus (simplified for clarity):

a	z			1	2
z	z			3	4
z	a			5	7
a	z			6	8
z	z			9	10
z	a			11	12
z	z			13	15
z	a			14	16
a	z			17	18
z	z			19	20
a	z			21	22
z	z			23	24
a	z			25	26
z	a			27	28
z	z			29	30
z	z			31	32

I wish to:

1) Move to the first occurrence of A (where A is the first variable in a separate look-up table)
2) Note the cell reference of the cell four to the right of A
3) Find the next occurrence of A, make cell active
4) Insert a formula setting the blank cell two to the right of the active cell equal to the reference from step 2 (not just the value).
5) Note the cell reference of the cell four to the right of A.

Repeat until the last A is found, then move on to B or whatever's next in the list.

Edit: (In hindsight, this doesn't sound too tough, but I confused myself by trying to use array formulae or something like it...)

 

[cheekbones3]

Been thinking about this more, and all I'm struggling with really is how to search sequentially through the variables in columns A and B. Is there a more efficient method than simply selecting the first cell in the range, checking whether it matches the variable of interest, and moving on after doing whatever else is necessary?

 

[cheekbones3]

I've written some initial code using the inefficient method I mentioned above. This, however, doesn't appear to be doing anything.

I'd be grateful for any advice as to why this code is ineffective. Thanks in advance!

Sub checkcells()

Dim activeteam As String
Dim cellref As String

Worksheets("Season").Select
Range("A2").Select
activeteam = ActiveCell.Value

    For y = 1 To 16
        For x = 1 To 2

        Range("A10").Select
        ActiveCell.Offset(y - 1, x - 1).Select

        If ActiveCell.Value = activeteam Then insertformula

        Next x
    Next y

End Sub


Sub insertformula()

cellref = ActiveCell.Offset(0, 4).Address(0, 0)
If y > 1 Then ActiveCell.Offset(0, 2).Value = "=" & cellref

End Sub

 

[cheekbones3]

Help?

I'm clueless as to why my code's not working and would greatly appreciate your advice. Looking at it during running (where it appears to do nothing), I noticed the the value of x reached 3. This is odd. And strange...

 

[cheekbones3]

Bump.

Did I put a particularly unappealing title to this thread or something?

 

[cheekbones3]

If anyone's interested, I fixed this thus:

Sub insertformula()

Dim a As String
Dim b As String
Dim c As Integer
Dim d As Integer

Worksheets("xxx").Select 'insert this name manually
c = 210 'insert this value manually
d = 15 'insert this value manually

Application.ScreenUpdating = False

For z = 1 To c
Range("A2").Select
ActiveCell.Offset(z - 1, 0).Select
a = ActiveCell.Value

    For y = 1 To c
        For x = 1 To 3

        Range("D27").Select 'insert starting cell manually
        ActiveCell.Offset(y - 1, x - 1).Select

If ActiveCell.Value = a And x = 1 Then ActiveCell.Offset(0, 4).Value = b
If ActiveCell.Value = a And x = 3 Then ActiveCell.Offset(0, 3).Value = b
If ActiveCell.Value = aAnd x = 1 Then b = "=" & ActiveCell.Offset(0, 13).Address(0, 0)
If ActiveCell.Value = a And x = 3 Then b = "=" & ActiveCell.Offset(0, 12).Address(0, 0)
        
        Next x
    Next y
Next z

Application.ScreenUpdating = True

End Sub