OFFSET

[Emm]

Hi All,

Can someone help with this ...

I want to Offset cell b5 by the value in (k5 minus 1) multiplied by 18

in other words

say b5 =8

then the offset should be (8-1) x 18

= 126

I have tried setting x as a variable ... but keep getting errors..


thanks ....

 

[venkat1926]

did you try

=OFFSET($B$5,($K$5-1)*18,0,1,1)

that means it will gives value in B131

is this what you want.

 

[Emm]

No .. want I want to do is use "offset" in VBA to take me to the cell ... not return the value from that cell..

Thanks..

 

[Peter_SSs]

Emm

You haven't said whether you want to offset rows or columns, but I am assuming rows. Try the code below, remembering that usually you do not need to actually select a cell to work with it.

This code will select B131 if K5 contains 8 (I am assuming that your example of B5 containing 8 was meant to be K5). That is it selects the cell offset from B5 by 126 rows. Is that what you want?

Range("B5").Offset((Range("K5").Value - 1) * 18).Select

 

[Emm]

Hi Peter..

just tried it ... comes up with error ..

Application or Object defined error

??

keith

 

[Peter_SSs]

Hi Peter..

just tried it ... comes up with error ..

Application or Object defined error

??

keith

Perhaps you had better tell us more about your sheet(s) and what else you are trying to do. Also posting all of your existing code might be useful - providing it isn't tooooo long.

 

[Emm]

Ok... got it Peter..

Thanks for your help ...

( i didn't have a value in K5 in my test sheet)


Doh!

Works OK now ...

Keith

 

[Scott Huish]

This will get you the same thing without Offset:

Range("B" & (Range("K5") - 1) * 18 + 5).Select

Although depending on what you are trying to do, you may not even need to Select, ranges can be worked with directly in VBA.