vba offset function to write one row below of reference cell

**ozan** · 12-23-2012, 03:24 PM

Hi,

i wrote a function,

function Round_D (z as Variant ........)

for example my z cell is A17 and i need to find A18.

How can i do this with a vba offset function. Tomorrow i have a presentation, and it is mandatory to achive it.

This far, i wrote this but, it didn't work. Please help, thank you.

Please Login or Register  to view this content.

**Richard Buttrey** · 12-23-2012, 03:55 PM

Hi,

You'll need to explain why it "doesn't work" and indeed what "it" is. And what do you mean by "Find Z18"?

Would you also change your location to something meaningful. This often helps those who respond to your requests since it tells them something about what your system locale settings might be and what your date standard might be e.g. dd/mm/yy (or if you're from the other side of the pond mm/dd/yy)

**ozan** · 12-23-2012, 04:07 PM

Hi Richard,

z is a function member, i set this name for the function.

with the function i wrote, i pass it to excel frontend to calculate some values. The only thing i couldn't solve is this problem.

z refers to a range in excel for example A17 (or anything else). Ofcourse when i pull formula to right, it is B17, C17, D17 and goes on.

but in the vba function i need to implicate A18, B18, C18.... so i need to use Offset function.

There are 6 function members (x As Long, y As Variant, col1 As Integer, Col2 As Integer....) and i don' t want to expand it to 7 members.

In my first post, i wrote this function but it didn' t work. Is it possible to write a code that implicates the cell 'one cell below the reference cell, in this case 'z' member? Thank you.

Please Login or Register  to view this content.

**Richard Buttrey** · 12-23-2012, 09:00 PM

Hi,

I still don't understand why you say it doesn't work. It might help if you upload your workbook.

In general Range("A1").Offset(1,0) refers to range A2 and similarly Range("A1").Cells(2,1) refers to A2.

What is the cell ref for z in your example, what's the value in the cell immediately below it and what answer are you getting?

**ozan** · 12-24-2012, 03:24 AM

Thanks so much for respond Richard,

This is my code. In the if statement, i miss something. (If ws1.Range(z).Offset(1, 0) >= 12 Then) : I think i have to fix the code on the left.

Not: z is linked in the "updated" sheet.

Please Login or Register  to view this content.

**:) Sixthsense :)** · 12-24-2012, 03:41 AM

z As Long
If ws1.Range(z).Offset(1, 0) >= 12 Then

Small confusion z is declared as long then how it come referenced in range?

**ozan** · 12-24-2012, 04:29 AM

honestly, i don't know. that is why i asked for help.

**:) Sixthsense :)** · 12-24-2012, 04:53 AM

what is the input value of z?
or
which cell you would like to refer in the range (Range(z))?

**ozan** · 12-24-2012, 04:59 AM

for example z is A17, i write A17 in the formula. With the "offset" in macro i want to implicate A18 to the Index_ark formula. A18 value is for example "6" so, when i am doing this function,

Please Login or Register  to view this content.

I want it to perceive like this;

Please Login or Register  to view this content.

In other words,

Please Login or Register  to view this content.

Thanks in advance.

**:) Sixthsense :)** · 12-24-2012, 05:10 AM

Ok try this...

Change the z As Long to

Formula:

Please Login or Register  to view this content.

Mention the A17 In your Index_ark function.

**ozan** · 12-24-2012, 05:20 AM

still getting an #value error.

I have to use this formula

Please Login or Register  to view this content.

because, in excel, i have to expand the formula to a table (for example i'll write this function in "A19:Z200")

**:) Sixthsense :)** · 12-24-2012, 05:28 AM

#value error will occur when text value operated with numeric value like Text1+7

Or

when string values are added in arithmatical sign like Text1+Text2

**:) Sixthsense :)** · 12-24-2012, 05:42 AM

If ws1.Range(z).Offset(1, 0) >= 12 Then
z = a17
for example z is A17, i write A17 in the formula. With the "offset" in macro i want to implicate A18 to the Index_ark formula

why not z = a18 and the code should be If ws1.Range(z) >= 12 Then?

**ozan** · 12-24-2012, 05:47 AM

because, z must be linked with different colum,

the code i' d sent is the part of the formula, not all of it.

And in the code upside, the cell which i' ll use in "Index_ark" function must be linked to z-1 (in this case for example z: A17, z-1: A18) because the value i have to use in the "Index_ark" function is below the cell which linked to "z"

**:) Sixthsense :)** · 12-24-2012, 06:02 AM

Sorry I am unable to continue this thread... and I hope someone will help you to sortout your problem

**ozan** · 12-24-2012, 06:03 AM

ok, thanks, really appreciate your attention. Hane a good day.

**Richard Buttrey** · 12-24-2012, 06:21 AM

Hi,

In view of SixthSense's response, please upload your workbook as I suggested in an earlier post. I for one often find it much easier to offer a suggestion if I can see the request in the context of its workbook. However this being the Christmas holiday don't expect a response by return it may take a little longer.

vba offset function to write one row below of reference cell

LinkBack

Thread Tools

Rate This Thread

Display

vba offset function to write one row below of reference cell

Re: vba offset function to write one row below of reference cell

Re: vba offset function to write one row below of reference cell

Re: vba offset function to write one row below of reference cell

Re: vba offset function to write one row below of reference cell

Re: vba offset function to write one row below of reference cell

Re: vba offset function to write one row below of reference cell

Re: vba offset function to write one row below of reference cell

Re: vba offset function to write one row below of reference cell

Re: vba offset function to write one row below of reference cell

Re: vba offset function to write one row below of reference cell

Re: vba offset function to write one row below of reference cell

Re: vba offset function to write one row below of reference cell

Re: vba offset function to write one row below of reference cell

Re: vba offset function to write one row below of reference cell

Re: vba offset function to write one row below of reference cell

Re: vba offset function to write one row below of reference cell

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions