If / Then VBA statment that moves through range where relative reference of formula changes

[Duffman4576]

Hey Forum:
I have gotten stuck and am hoping for some help (admittly still learniing VBA, so be kind). So far I have written the below code:


Sub More_Adjustments()
Dim rng As Range
Set rng = Sheets("Adjustments").Range("I8:I50")

For Each cell In rng
If Sheets("Adjustments").Range("I8") = Sheets("Adjustments").Range("I9") Then
Sheets("Adjustments").Range("K9:P9").Copy
Sheets("Report").Activate
Range(Sheets("Inputs").Range("H5").Value).Select
Selection.PasteSpecial xlPasteValues
Else:
End If

Next

End Sub


What I am tring to accomplish is this:
in the Adjustments Sheets I8:I50 will have information. Essentially old and new. The logic I have used is if I9=I8 (these are names) then we have entered an adjustment.
That adjustment needs to be updated into the Report sheet.

So I have gotten this to work for 1 name, but can't seem to get it to move through the rest of the range.

I think I am going wrong using direct cell refeneces in If formula, but really I have no idea where to go next.

Thanks for any an all help.

 

[Andrew Poulsom]

It looks like you need:

If cell.Value = cell.Offset(1).Value Then
cell.Offset(1,2).Resize(1, 6).Copy

By the way, you don't need all that Activating/Selecting.

 

[Duffman4576]

Thanks Andrew: That works for the first part moving the If through the range. How would I get the second part:
Range(Sheets("Inputs").Range("H5").Value).Select
Selection.PasteSpecial xlPasteValues
To move through a corresponding range in another sheet.

What this is a cell location to direct the paste. This is a fluid report where the ranking will move day to day. I created the "Inputs" to give me a refence point, IE C4.
I am trying to get the above to move in the same manner as the first. One by one through the range the reference also moves down.

Thanks too for letting me know about the selecting. Makes things cleaner

 

[Andrew Poulsom]

Once you've used H5 what should the next paste use?

 

[Duffman4576]

H5 is the beginning to a "mirror" range to the first set. So H5- next H6 - H7 and so on. just straight down the range

 

[Andrew Poulsom]

Then maybe:

Sub More_Adjustments()
    Dim rng As Range
    Dim Counter As Long
    Dim cell As Range
    Set rng = Sheets("Adjustments").Range("I8:I50")
    Counter = 5
    For Each cell In rng
        If cell.Value = cell.Offset(1).Value Then
            cell.Offset(1, 2).Resize(1, 6).Copy
            Sheets("Report").Range(Sheets("Inputs").Range("H" & Counter).Value).PasteSpecial xlPasteValues
            Counter = Counter + 1
        End If
    Next cell
End Sub

 

[Duffman4576]

Thanks for the quick reply: I am getting a runtime error '1004' Application-defined or object-defined error. Still getting hung up on:
Sub More_Adjustments4()
Dim rng As Range
Dim Counter As Long
Dim cell As Range
Set rng = Sheets("Adjustments").Range("I8:I50")
Counter = 5
For Each cell In rng
If cell.Value = cell.Offset(1).Value Then
cell.Offset(1, 2).Resize(1, 6).Copy
Sheets("Report").Range(Sheets("Inputs").Range("H" & Counter).Value).PasteSpecial xlPasteValues
Counter = Counter + 1
End If
Next cell
End Sub

Am I asking for too much by having the reference cells on another sheet? I am trying to keep it seperate so the users don't see the reference, but if need be I can have it placed on the same sheet then use the offset of the originally referenced range.

 

[Andrew Poulsom]

What have you got in H5, H6, H7 etc?

 

[Duffman4576]

Sorry for the delay: in H5/H6 (and so on) there is a formula that returns a cell adress:
=CELL("address",INDEX(G:G,MATCH(Adjustments!I10,Report!$A$1:$A$59,0))) - resulting in ex $G$15
The names on the Report will change ranking, so I am using this to find WHERE to instruct the VBA to paste the values. I have used this formula in other places with success instructing the VBA to use the .value.

 

[Andrew Poulsom]

So what's in column H when you get the error?