×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Writing Macro using R1C1 reference...

Writing Macro using R1C1 reference...

Writing Macro using R1C1 reference...

(OP)
I'm writing a small macro to copy and paste the partial row of data. The data will be updated everyday i.e. row 30 will row 31 tomorrow. I'm using a macro to indentify the poistion of the beginning cell and the end cell but I don't know how to put it in the Range() function.

Range("C458").Select
Selection.End(xlDown).Select ---> Start Cell
Range("AG458").Select
Selection.End(xlDown).Select ---> End Cell


Range("C459:AG459").Select ---> to be replace with start cell address and end cell address

    Selection.AutoFill Destination:=Range("C459:AG460"), Type:=xlFillDefault

I replace C459 with R1C1 and AG459 with R1C32. Execl won't accept them. I also try to use Cell("address") in order to copy and store the Start/End address then use in the Range() but I don't know how to copy them.

Thanks for your help and sorry for this long message.

RE: Writing Macro using R1C1 reference...

I don't think the working of the range object is all clear to you. The ay you use it works for absolute cell reference. For relative cell reference it works a little different.

First of all:

Range("C458").Select
Selection.End(xlDown).Select
Range("AG458").Select
Selection.End(xlDown).Select


I don't understand why you should use the Selection.End(xlDown).Select method if you don't do anything with it.

If you want to store the adress of this cell as a variable for future use you should insert something like:

Address=ActiveCell.Address

Second:

I replace C459 with R1C1 and AG459 with R1C32. Execl won't accept them. I also try to use Cell("address") in order to copy and store the Start/End address then use in the Range() but I don't know how to copy them.

You can not rename absolute adresses to R?C? addresses. The method useing the R?C? command defines relative movement based on the cell you selected before you executed this command.

To do relative movement you should use this command:

ActiveCell.Offset(RowOffset, ColOffset)

Good luck!

Jonathan

RE: Writing Macro using R1C1 reference...

Tho' it's not very clear from your query, I take it that:
1. Your database extends from col 'C' to col. 'AG'
2. Everyday u intend to fill the first non-empty row in this range with Autofill of type xlFillDefault

If my assumption is correct, try the following:

Sub MyAutoFill()
    Range("C65535").End(xlUp).Select
    ActiveCell.EntireRow.Range("C1:AG1").Select
    Selection.AutoFill Destination:=Selection.EntireRow.Range("C1:AG2"), Type:=xlFillDefault
End Sub

It should work...

RE: Writing Macro using R1C1 reference...

(OP)
Thanks a lot...Cactus & Mala...

I will try your ways. They sent me out to the field again.
I'll be back in my desk tonight and I will give it a shot.
I will let you guys know the result. Thanks again.

RE: Writing Macro using R1C1 reference...

(OP)
Mala...Thank you very much...

It works like a charm. I will try to understand those lines at later time.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources