Help with code

[The Idea Dude]

I thought I did a good job conjuring this up, lol, appears that some syntax is still wrong........any pointers

Thanks

Sub eighteenawake()

    Dim c As Range
    
    For Each c In Worksheets("Sheet2").Range("I2:I49").Cells
        If c.Value - c.Offset(-4, 0).Value = c.Value - 2 Then Range("N7").Value = c.Offset(-36 + (c.Offset(-5, 0).Value * 2), -2).Value
    Next

End Sub

 

[SteveO59L]

Doesnt the first argument in an offset statement refer to the row ?.

If it does, and you start at I2, you cant offset by -4 rows.

 

[The Idea Dude]

Yeah....ooops.....was going minus for down rows. Have adjusted but it is still giving me an error from the then statement.

Sub eighteenawake()

    Dim c As Range
    
    For Each c In Worksheets("Sheet2").Range("I2:I49").Cells
        If c.Value - c.Offset(4, 0).Value = c.Value - 2 Then Range("N7").Value = c.Offset(36 + (c.Offset(5, 0).Value * 2), -2).Value
    Next
End Sub

 

[jindon]

Hi

Range("N7").Value = c.Offset(36 + (c.Offset(5, 0).Value * 2), -2).Value

what are you tryign to do with this line and what value in c.Offset(5,0)?

anyway tyr

Sheets("Sheet2").Range("N7").Value=c.Offset(36+(c.offset(5,0).value * 2),-2).value

 

[Richard Schollar]

Hi

Is Sheet2 the activesheet when you run the code? If not, precede the Range("N7") etc after the Then with Sheets("Sheet2").

Richard

 

[The Idea Dude]

Yes sheet 2 is the active sheet

The value in c.Offset(5, 0) is just a number

The code is stopping at Sheets("Sheet2")...

Sub eighteenawake()

    Dim c As Range
    
    For Each c In Worksheets("Sheet2").Range("I2:I49").Cells
        If c.Value - c.Offset(4, 0).Value = c.Value - 2 Then Sheets("Sheet2").Range("N7").Value = c.Offset(36 + (c.Offset(5, 0).Value * 2), -2).Value
    Next
End Sub

 

[The Idea Dude]

Geepers......another bit of code had pw protected the sheet...got it sorted...cheers for the help