Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Excel Macro/VB question


  • Please log in to reply

#1
diggeryo

diggeryo

    Member

  • Member
  • PipPipPip
  • 343 posts
I use Excel 2007. I hope how I describe this makes sense. I am by no mean a VB authority. Much of the lines of code I created for my Excel VB macro was done though trial and error and a lot of googling. With that being said, I just can't seem to get one thing right.

I'm trying to create a section in my macro that will copy the contents of cell AE2 (which will contain a formula) into the remaining cells of column AE (AE3 through whatever) that have data in another column ©. However, the last cell in column AE changes depending upon the file I use, so the last rown is not always the same number.

I was able to edit the macro so that it copies to the entire column (all 1,048,576 cells). But this is way too much. I don't want it to copy the formula to every cell in a column, but rather every cell in a column AE that has data column C.

Any suggestions would be appreciated.
  • 0

Advertisements


#2
diggeryo

diggeryo

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 343 posts
***UPDATE***

I figured out how to use the VB macro to calculate the last row in the worksheet.

Dim LR As Long
LR = ActiveSheet.UsedRange.Rows.Count

However, I'm not sure how to now use this variable to select the cells in the range. I tried the following, but none seem to work:

Range("AE3:AE[LR]").Select
Range("AE3:Indirect(R[LR]C)").Select
Range("AE3:Indirect(R[LR]C, FALSE)").Select
  • 0

#3
diggeryo

diggeryo

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 343 posts
After three four days of trial and error and going to every google page imaginable, I think I finally figured it out.


Range(Cells(2, 31), Cells(LR, 31)).Select
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP