Help with code

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
585
I thought I did a good job conjuring this up, lol, appears that some syntax is still wrong........any pointers :)

Thanks :)

Code:
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
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

Well-known Member
Joined
Aug 15, 2002
Messages
585
Yeah....ooops.....was going minus for down rows. Have adjusted but it is still giving me an error from the then statement.

Code:
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
Code:
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
Code:
Sheets("Sheet2").Range("N7").Value=c.Offset(36+(c.offset(5,0).value * 2),-2).value
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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

Well-known Member
Joined
Aug 15, 2002
Messages
585
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")...

Code:
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

Well-known Member
Joined
Aug 15, 2002
Messages
585
Geepers......another bit of code had pw protected the sheet...got it sorted...cheers for the help :)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,000
Messages
5,526,186
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top