0

So I have the index of some cells in my excel doc. I am now trying to use VBA to make a union between two different ranges.

So far I have...

Function FiveYTwoY()
 Worksheets("India Data").Activate
 index5_90 = Cells(10, "B").Value '5Y 90 day index
 index5_yes = Cells(9, "B").Value '5Y yesterday index
 index2_90 = Cells(7, "B").Value  '2Y 90 day index
 index2_yes = Cells(6, "B").Value '2Y yesterday index
 range5_90 = "Q" & index5_90
 range5_yes = "Q" & index_yes
 range2_90 = "S" & index2_90
 range2_yes = "S" & index2_yes 
 fullRange5 = Range(range5_90, range5_yes)
 fullRange2 = Range(range2_90, range2_yes)
 FiveYTwoY = Union(fullRange2, fullRange5)

however this isn't working and after debugging... its because my range is not setting properly. how do I fix this? I keep getting #value. I want to print the joined ranges Thanks for the help!

user3757405
  • 51
  • 1
  • 8
  • If you are calling this from a worksheet, it [won't work](http://stackoverflow.com/a/3622544/11683). – GSerg Jul 16 '14 at 19:27
  • I am calling the cell value from the work sheet. Are you saying I need a sub? I want to basically paste two different ranges into one column – user3757405 Jul 16 '14 at 19:34

1 Answers1

0

1- Cells() works with numbers only (as far as I know.)

2- Why don't you just enter your references in your cells directly..

(In Cell 5 90)
= B10

You can easily do everything you just did with inCell commandLines.

Sifu
  • 1,082
  • 8
  • 26
  • What do you mean by the second comment? I don't really understand it – user3757405 Jul 16 '14 at 19:35
  • Instead of programming a macro in VBA to refer a cell to another cell, type in one cell `=`, then click on the cell you want to refer it to. See [this for exemple](http://www.youtube.com/watch?v=NmVMjQzseLA). – Sifu Jul 16 '14 at 19:36
  • I get that. I am working with live data so I the cell number will update everyday. For example, I want to grab A5:A12 and B4:B13 and combine them into one list. However those numbers change by one every day because it goes to the latest date inputed. – user3757405 Jul 16 '14 at 19:38
  • Do you mean that if today you want to grab `A5:A12 and B4:B13`, then tomorrow you will need to grab `A6:A13 and B5:B14`? – Sifu Jul 16 '14 at 19:40
  • Yes. Thats a theoretical example, but yes you are right. And I want to combine the two lists so they form one list in Column C – user3757405 Jul 16 '14 at 19:42
  • Did you try my Number 1 in my answer? Did it work? See [This for getting programatically the last cell in a column](http://stackoverflow.com/questions/5441885/last-non-empty-cell-in-a-column). – Sifu Jul 16 '14 at 19:45
  • No, it seemed to have made no difference. I printed both Cells(10,2) and Cells(10, "B") and they both return fine – user3757405 Jul 16 '14 at 19:47
  • Do you call the function from the worksheet? How do you call the function? I would suggest you to call the `sub` from a button press, otherwise it might not work (see GSerg comment earlier). Otherwise I don't have any other idea on what causes the error. – Sifu Jul 16 '14 at 19:52