How do I edit this macro?

G

Guest

The value of L1 is the row number. I am copying info from vertical to a new
sheet that is horizontal like a database list. This macro is not working an
I need help. Thanks in advance.

ActiveCell.FormulaR1C1 = "=Sheet1!R[-1]C[1]"
Range("B(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC"
Range("C(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[3]C[-1]"
Range("D(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[4]C[-2]"
Range("E(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[5]C[-3]"
Range("F(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[6]C[-4]"
Range("G(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[7]C[-5]"
Range("H(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[8]C[-6]"
Range("I(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[9]C[-7]"
Range("J(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[10]C[-8]"
Range("A3").Select
 
B

Bob Phillips

Range"A11").FormulaR1C1 = "=Sheet1!R[-1]C[1]"
Range("Bl1)").FormulaR1C1 = "=Sheet1!RC"
Range("Cl1").FormulaR1C1 = "=Sheet1!R[3]C[-1]"
Range("Dl1").FormulaR1C1 = "=Sheet1!R[4]C[-2]"
Range("E11").FormulaR1C1 = "=Sheet1!R[5]C[-3]"
Range("Fl1").FormulaR1C1 = "=Sheet1!R[6]C[-4]"
Range("Gl1").FormulaR1C1 = "=Sheet1!R[7]C[-5]"
Range("Hl1").FormulaR1C1 = "=Sheet1!R[8]C[-6]"
Range("Il1").FormulaR1C1 = "=Sheet1!R[9]C[-7]"
Range("Jl1").FormulaR1C1 = "=Sheet1!R[10]C[-8]"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
D

Dave Peterson

Just a typo alert:

Range("A11").FormulaR1C1 = "=sheet1!R[-1]C[1]"
Range("B11").FormulaR1C1 = "=sheet1!RC"
Range("C11").FormulaR1C1 = "=sheet1!R[3]C[-1]"
Range("D11").FormulaR1C1 = "=sheet1!R[4]C[-2]"
Range("E11").FormulaR1C1 = "=sheet1!R[5]C[-3]"
Range("F11").FormulaR1C1 = "=sheet1!R[6]C[-4]"
Range("G11").FormulaR1C1 = "=sheet1!R[7]C[-5]"
Range("H11").FormulaR1C1 = "=sheet1!R[8]C[-6]"
Range("I11").FormulaR1C1 = "=sheet1!R[9]C[-7]"
Range("J11").FormulaR1C1 = "=sheet1!R[10]C[-8]"

And to the OP:

You have a mixture of 11 and l1 (one-one's and ell-one's) in those addresses.
You'll want to be more careful with which you want: BL1 or B11???



Bob said:
Range"A11").FormulaR1C1 = "=Sheet1!R[-1]C[1]"
Range("Bl1)").FormulaR1C1 = "=Sheet1!RC"
Range("Cl1").FormulaR1C1 = "=Sheet1!R[3]C[-1]"
Range("Dl1").FormulaR1C1 = "=Sheet1!R[4]C[-2]"
Range("E11").FormulaR1C1 = "=Sheet1!R[5]C[-3]"
Range("Fl1").FormulaR1C1 = "=Sheet1!R[6]C[-4]"
Range("Gl1").FormulaR1C1 = "=Sheet1!R[7]C[-5]"
Range("Hl1").FormulaR1C1 = "=Sheet1!R[8]C[-6]"
Range("Il1").FormulaR1C1 = "=Sheet1!R[9]C[-7]"
Range("Jl1").FormulaR1C1 = "=Sheet1!R[10]C[-8]"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

Mallers said:
The value of L1 is the row number. I am copying info from vertical to a new
sheet that is horizontal like a database list. This macro is not working an
I need help. Thanks in advance.

ActiveCell.FormulaR1C1 = "=Sheet1!R[-1]C[1]"
Range("B(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC"
Range("C(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[3]C[-1]"
Range("D(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[4]C[-2]"
Range("E(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[5]C[-3]"
Range("F(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[6]C[-4]"
Range("G(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[7]C[-5]"
Range("H(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[8]C[-6]"
Range("I(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[9]C[-7]"
Range("J(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[10]C[-8]"
Range("A3").Select
Click to expand...
Click to expand...
 
B

Bob Phillips

Thanks Dave, I didn't spot that l1 11 differentation, and must admit to not
undesrtanding the OPs point about L1, so in the lightt of your observation I
think he must mean

Range("A" & L1).FormulaR1C1 = "=sheet1!R[-1]C[1]"
Range("B" & L1).FormulaR1C1 = "=sheet1!RC"
Range("C" & L1).FormulaR1C1 = "=sheet1!R[3]C[-1]"
Range("D" & L1).FormulaR1C1 = "=sheet1!R[4]C[-2]"
Range("E" & L1).FormulaR1C1 = "=sheet1!R[5]C[-3]"
Range("F" & L1).FormulaR1C1 = "=sheet1!R[6]C[-4]"
Range("G" & L1).FormulaR1C1 = "=sheet1!R[7]C[-5]"
Range("H" & L1).FormulaR1C1 = "=sheet1!R[8]C[-6]"
Range("I" & L1).FormulaR1C1 = "=sheet1!R[9]C[-7]"
Range("J" & L1).FormulaR1C1 = "=sheet1!R[10]C[-8]"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

Dave Peterson said:
Just a typo alert:

Range("A11").FormulaR1C1 = "=sheet1!R[-1]C[1]"
Range("B11").FormulaR1C1 = "=sheet1!RC"
Range("C11").FormulaR1C1 = "=sheet1!R[3]C[-1]"
Range("D11").FormulaR1C1 = "=sheet1!R[4]C[-2]"
Range("E11").FormulaR1C1 = "=sheet1!R[5]C[-3]"
Range("F11").FormulaR1C1 = "=sheet1!R[6]C[-4]"
Range("G11").FormulaR1C1 = "=sheet1!R[7]C[-5]"
Range("H11").FormulaR1C1 = "=sheet1!R[8]C[-6]"
Range("I11").FormulaR1C1 = "=sheet1!R[9]C[-7]"
Range("J11").FormulaR1C1 = "=sheet1!R[10]C[-8]"

And to the OP:

You have a mixture of 11 and l1 (one-one's and ell-one's) in those addresses.
You'll want to be more careful with which you want: BL1 or B11???



Bob said:
Range"A11").FormulaR1C1 = "=Sheet1!R[-1]C[1]"
Range("Bl1)").FormulaR1C1 = "=Sheet1!RC"
Range("Cl1").FormulaR1C1 = "=Sheet1!R[3]C[-1]"
Range("Dl1").FormulaR1C1 = "=Sheet1!R[4]C[-2]"
Range("E11").FormulaR1C1 = "=Sheet1!R[5]C[-3]"
Range("Fl1").FormulaR1C1 = "=Sheet1!R[6]C[-4]"
Range("Gl1").FormulaR1C1 = "=Sheet1!R[7]C[-5]"
Range("Hl1").FormulaR1C1 = "=Sheet1!R[8]C[-6]"
Range("Il1").FormulaR1C1 = "=Sheet1!R[9]C[-7]"
Range("Jl1").FormulaR1C1 = "=Sheet1!R[10]C[-8]"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

Mallers said:
The value of L1 is the row number. I am copying info from vertical to
Click to expand...
a
new
sheet that is horizontal like a database list. This macro is not
Click to expand...
working
an
I need help. Thanks in advance.

ActiveCell.FormulaR1C1 = "=Sheet1!R[-1]C[1]"
Range("B(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC"
Range("C(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[3]C[-1]"
Range("D(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[4]C[-2]"
Range("E(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[5]C[-3]"
Range("F(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[6]C[-4]"
Range("G(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[7]C[-5]"
Range("H(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[8]C[-6]"
Range("I(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[9]C[-7]"
Range("J(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[10]C[-8]"
Range("A3").Select
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

Ahhhh.

I didn't read the original post. (I usually can get enough info from the
reply--not always, obviously!)

I think you got it!

Bob said:
Thanks Dave, I didn't spot that l1 11 differentation, and must admit to not
undesrtanding the OPs point about L1, so in the lightt of your observation I
think he must mean

Range("A" & L1).FormulaR1C1 = "=sheet1!R[-1]C[1]"
Range("B" & L1).FormulaR1C1 = "=sheet1!RC"
Range("C" & L1).FormulaR1C1 = "=sheet1!R[3]C[-1]"
Range("D" & L1).FormulaR1C1 = "=sheet1!R[4]C[-2]"
Range("E" & L1).FormulaR1C1 = "=sheet1!R[5]C[-3]"
Range("F" & L1).FormulaR1C1 = "=sheet1!R[6]C[-4]"
Range("G" & L1).FormulaR1C1 = "=sheet1!R[7]C[-5]"
Range("H" & L1).FormulaR1C1 = "=sheet1!R[8]C[-6]"
Range("I" & L1).FormulaR1C1 = "=sheet1!R[9]C[-7]"
Range("J" & L1).FormulaR1C1 = "=sheet1!R[10]C[-8]"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

Dave Peterson said:
Just a typo alert:

Range("A11").FormulaR1C1 = "=sheet1!R[-1]C[1]"
Range("B11").FormulaR1C1 = "=sheet1!RC"
Range("C11").FormulaR1C1 = "=sheet1!R[3]C[-1]"
Range("D11").FormulaR1C1 = "=sheet1!R[4]C[-2]"
Range("E11").FormulaR1C1 = "=sheet1!R[5]C[-3]"
Range("F11").FormulaR1C1 = "=sheet1!R[6]C[-4]"
Range("G11").FormulaR1C1 = "=sheet1!R[7]C[-5]"
Range("H11").FormulaR1C1 = "=sheet1!R[8]C[-6]"
Range("I11").FormulaR1C1 = "=sheet1!R[9]C[-7]"
Range("J11").FormulaR1C1 = "=sheet1!R[10]C[-8]"

And to the OP:

You have a mixture of 11 and l1 (one-one's and ell-one's) in those addresses.
You'll want to be more careful with which you want: BL1 or B11???



Bob said:
Range"A11").FormulaR1C1 = "=Sheet1!R[-1]C[1]"
Range("Bl1)").FormulaR1C1 = "=Sheet1!RC"
Range("Cl1").FormulaR1C1 = "=Sheet1!R[3]C[-1]"
Range("Dl1").FormulaR1C1 = "=Sheet1!R[4]C[-2]"
Range("E11").FormulaR1C1 = "=Sheet1!R[5]C[-3]"
Range("Fl1").FormulaR1C1 = "=Sheet1!R[6]C[-4]"
Range("Gl1").FormulaR1C1 = "=Sheet1!R[7]C[-5]"
Range("Hl1").FormulaR1C1 = "=Sheet1!R[8]C[-6]"
Range("Il1").FormulaR1C1 = "=Sheet1!R[9]C[-7]"
Range("Jl1").FormulaR1C1 = "=Sheet1!R[10]C[-8]"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

The value of L1 is the row number. I am copying info from vertical to a
new
sheet that is horizontal like a database list. This macro is not working
an
I need help. Thanks in advance.

ActiveCell.FormulaR1C1 = "=Sheet1!R[-1]C[1]"
Range("B(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!RC"
Range("C(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[3]C[-1]"
Range("D(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[4]C[-2]"
Range("E(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[5]C[-3]"
Range("F(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[6]C[-4]"
Range("G(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[7]C[-5]"
Range("H(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[8]C[-6]"
Range("I(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[9]C[-7]"
Range("J(l1)").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[10]C[-8]"
Range("A3").Select
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Phillips

Dave Peterson said:
Ahhhh.

I didn't read the original post. (I usually can get enough info from the
reply--not always, obviously!)

I think you got it!
Click to expand...

We got it!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Change data with a macro 8
How can i find out the last row when I do autofill? 3
Macro to shift 3
macro add 10
Macro Flexability 7
I need a simple loop with a 4+ row added in. 5
Macro Loop 1
Macros in excel 2000 12

Top