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.

 

[Duffman4576]

Column H has the formula =CELL("address",INDEX(G:G,MATCH(Adjustments!I8,Report!$A$1:$A$59,0))) with the result $G$17. The formula continues through the range from I8 on with different reference results.

I hope this helps to clarify. I may be missing what you are asking.
And thanks for all the help

 

[Andrew Poulsom]

What's Counter when you get the error? And what's in column H on the row that equals Counter?

 

[Duffman4576]

I have gotten it to work...... kind of, but now am encourtering an error. Using the following

Sub More_Adjustments5()
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 + 2
End If
Next cell
End Sub
________


Don't know why, but chaning the counter to + 2 worked

It makes the updates, but I then get a Run-Time error '1004' Application-defined or object defined error. while highlighting (Sheets("Report").Range(Sheets("Inputs").Range("H" & i).Value).PasteSpecial xlPasteValues)

Any ideas? Should this be a new thread?

 

[Andrew Poulsom]

I guess there isn't a valid range address in column H on the row that equals Counter.

 

[Duffman4576]

Figured it out!

Sub More_Adjustments5()
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 + 2
On Error Resume Next
End If
Next cell
End Sub
__________________________________________

Added the On Error Resume Next- Works like a charm.

Thanks for all the help.