Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Copying and Pasting Values From One Column To Multiple Columns Using

  Asked By: Howard    Date: Oct 28    Category: MS Office    Views: 1739
  

I need some code that will enable me to do the following:

1. On Sheet 1 I have a list of office building tenants and the date
on which each respective lease expires. Some tenant's leases have
already expired and these tenants continue to occupy their space on
a month-to-month basis. Month-to-month tenant's lease expiration is
identified by the string "MTM", all other tenant's lease expiration
date is identified by a date in the mm-dd-yy format.

2. On Sheet 2 I have the list of the tenants with their lease
expiration date and the number of sf that the tenant leases.

3.I need some code that reads the number of sf for each tenant from
Sheet2 and pastes the value into the cell for each tenant under in
the column that corresponds to the year that the tenant's lease
expires. The result of the code would be the same as using the INDEX
(MATCH(),MATCH()) worksheet function where the Row lookup would be
the tenant name or suite number and the Column lookup would be the
year in which the tenant's lease expires.

4.The number of tenants and expiration dates is dynamic. The number
of unique years in which tenant's leases expires is dynamic as well.

5. I have an example file that demonstrates the way the model must
look and the way I have the source data range set up.

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Jack Williams     Answered On: Oct 28


The task of putting the information into the right column  is only a small part
(and potentially the easiest part) of this exercise. The harder part is
deciding how to run the code  and how to detect the extents of the data  areas.

From the way you describe it, this sounds like a commercial business, and I
think you are probably well out of your depth with the programming for it. I'd
recommend that you get the people who do your company's programming to do this
one, or retain the services of a local contractor.

On the other hand, it would be feasible to put an if statement into each cell  in
the rectangular area that is the leases and the months. This if could look in
the cell in row  1 (using $ to anchor on the row) to find the month/year the
column represents and check this against the month/year from the lease expiry
date (using $ to anchor on the column) and do a VLookup from the second sheet  if
the lease was expiring in that month.

If you use the $ anchors appropriately, it is possible to copy/paste a single
formula to all cells in the rectangular area. You would need to create extra
rows and fill them yourself as new leases start.

 
Answer #2    Answered By: Victoria Hughes     Answered On: Oct 28

I have considered the use of IF
statements via worksheet  cells, but I am trying to avoid using too
many Defined Named Ranges. In regards to determining the extent of
the data  ranges, I have already created code  that identifies the
start and end cells that correspond to the sf ranges in the single
column. I do by looping through the single column, removing
duplicate entries, sorting the resulting unique  list, and then
cutting the MTM and inserting it at the top of the list. From the
list of unique years, I can ascertain the position of the first sf
cell and last sf cell  in the list  by use of the OFFSET and COUNTIF
functions via code.

Assuming I can already identify the first and last cells in the
single column  that contain the sf corresponding for each expiration
year, how do I structure a For loop that copies the range  of SF
cells that correspond to each particular unique year  header, and
then procedes to the next year header?

I still have quite a bit to learn about VBA, as you noted!
Any assistance would be appreciated. I have posted another message
that illustrates the way the data is set  up if you would like to see
it.

 
Answer #3    Answered By: Ramond Fischer     Answered On: Oct 28

 example  of what I need
to be able to do:

Source Data on Sheet2:

SF Year
732 MTM
446 MTM
1,827 MTM
353 MTM
1,060 MTM
373 MTM
728 MTM
286 MTM
254 MTM
271 MTM
271 MTM
550 MTM
286 MTM
430 2006
732 2006
4,141 2006
210 2006
526 2006
1,469 2007
4,524 2007
991 2007
880 2007
1,596 2007
2,548 2007
1,500 2007
776 2007
3,958 2007
3,486 2007
1,040 2007
3,989 2007
758 2007
3,868 2007
480 2008
0 2008
468 2008
6,737 2008
7,800 2009
2,912 2010
4,889 2010
916 2010
3,530 2010

The code  I need would result  in the following on Sheet1:

MTM 2006 2007 2008 2009 2010
732
446
1,827
353
1,060
373
728
286
254
271
271
550
286
430
732
4,141
210
526
1,469
4,524
991
880
1,596
2,548
1,500
776
3,958
3,486
1,040
3,989
758
3,868
480
0
468
6,737
7,800
2,912
4,889
916
3,530
Any help would be greatly appreciated!

 




Tagged: