Hi,

I am new to all this and learning as I go.

I have created the following macro, which finds the first non empty cell in
a column g, moves one cell to the right (to column h) and then enters a
formula. I then need to copy this fomula into columns i to x. I am having
trouble doing this as the row number that the formula is in will always be
different. I attach what I have done so far:

Range("g24:g65000").Find(Empty).End(xlDown).Select
ActiveCell.Offset(0, 1).Select

ActiveCell.FormulaR1C1 = "=HLOOKUP(RC1,INDIRECT(R5C8),R22C,FALSE)"
Selection.Copy



I'm sure it is very straight forward but really need a helping hand.

Thanks. Jane

Try something like this:

sub test()
dim x as long
dim y as long

for x=24 to 65000
if cells(x,7).value=0 then
for y=9 to 24
cells(x,y).select
activecell.formula="=[your formula here]"
next y
end if
next x
end sub

"JaneCox" wrote in message
...
Hi,

I am new to all this and learning as I go.

I have created the following macro, which finds the first non empty cell
in
a column g, moves one cell to the right (to column h) and then enters a
formula. I then need to copy this fomula into columns i to x. I am
having
trouble doing this as the row number that the formula is in will always be
different. I attach what I have done so far:

Range("g24:g65000").Find(Empty).End(xlDown).Select
ActiveCell.Offset(0, 1).Select

ActiveCell.FormulaR1C1 = "=HLOOKUP(RC1,INDIRECT(R5C8),R22C,FALSE)"
Selection.Copy



I'm sure it is very straight forward but really need a helping hand.

Thanks. Jane

Why not set up a variable that holds the original row number you're in and
then based on where you now are it simply adds to it based on where you now
are?

I'm sorry I can;t be more specific, but I'm not sure which part of the code
you;re having issues with.

HTH.

"JaneCox" wrote:

Hi,

I am new to all this and learning as I go.

I have created the following macro, which finds the first non empty cell in
a column g, moves one cell to the right (to column h) and then enters a
formula. I then need to copy this fomula into columns i to x. I am having
trouble doing this as the row number that the formula is in will always be
different. I attach what I have done so far:

Range("g24:g65000").Find(Empty).End(xlDown).Select
ActiveCell.Offset(0, 1).Select

ActiveCell.FormulaR1C1 = "=HLOOKUP(RC1,INDIRECT(R5C8),R22C,FALSE)"
Selection.Copy



I'm sure it is very straight forward but really need a helping hand.

Thanks. Jane

Thanks Graham. You are a star!

Jane

"Graham Whitehead" wrote:

Try something like this:

sub test()
dim x as long
dim y as long

for x=24 to 65000
if cells(x,7).value=0 then
for y=9 to 24
cells(x,y).select
activecell.formula="=[your formula here]"
next y
end if
next x
end sub

"JaneCox" wrote in message
...
Hi,

I am new to all this and learning as I go.

I have created the following macro, which finds the first non empty cell
in
a column g, moves one cell to the right (to column h) and then enters a
formula. I then need to copy this fomula into columns i to x. I am
having
trouble doing this as the row number that the formula is in will always be
different. I attach what I have done so far:

Range("g24:g65000").Find(Empty).End(xlDown).Select
ActiveCell.Offset(0, 1).Select

ActiveCell.FormulaR1C1 = "=HLOOKUP(RC1,INDIRECT(R5C8),R22C,FALSE)"
Selection.Copy



I'm sure it is very straight forward but really need a helping hand.

Thanks. Jane