0

I am trying to write vba code that writes a formula to a cell. The formula is to hlookup with the array being fixed, on another worksheet, and with size depending on variables already defined.

Here is the relevant part of the code (the variables have already been defined and are integers):

Range("C2").Select
ActiveCell.Formula = "=hlookup(A2,Visits!Range("C2",Cells(" & rowsforlook & ", " & rowsforauto & " + 1))," & rowsforlook & " - 1)"

I have checked that there are no problems with the variables. I have realised that I haven't $fixed the array because I am not sure how to do this. I also think the page reference "Visits!" is probably not correct, but the code did not select an array even without this. The array I am trying to use is the selected region that would appear if I typed

Range("C2",Cells(" & rowsforlook & ", " & rowsforauto & " + 1)).Select

Thanks.

Community
  • 1
  • 1
user28711
  • 3
  • 2
  • First thing I've noticed: You've tried to use a double quote (") within double quotes. (Notice that C2 has appeared in black instead of red). To do this you should use double double quotes i.e. ""C2"" – Danzomida Aug 07 '13 at 16:38

1 Answers1

1

To fix your range I would suggest - instead of using the Range() method - that you build the range in a string. I imagine you want to produce a result looking something like:

"=hlookup(A2,Visits!C2:D10)"

(An example - You might need to pass the 3rd parameter false and change rows/columns)

To code this dynamically it could look something like:

startRow = 2 'Generate some dynamic string variables
endRow = 10
Range("C2").Formula = "=hlookup(A2,Visits!" & "C" & startRow & ":" & "D" & endRow & ")"

You may need to dynamically generate the 'C' and 'D' parts too, but this format should work for you.

Danzomida
  • 475
  • 2
  • 5
  • 11
  • Thanks, the problem I am having is that the column number (in place of the D above) is a variable. So I don't think I can use the string format as above, but need to write it using Cells. Any ideas? Thanks. – user28711 Aug 08 '13 at 08:15
  • If you check out [this](http://stackoverflow.com/questions/12796973/vba-function-to-convert-column-number-to-letter) question it should help you get the column letter from the number – Danzomida Aug 08 '13 at 09:50
  • Thanks for the link, it just seems like a long way round. I thought it would be possible to define the array in R1C1 format? – user28711 Aug 08 '13 at 10:07
  • It is possible, but remember that you are setting a cell formula. You are emulating what the user does in the cell when they type in '=VLookup(....)'. I'm sure they may well be a way of doing this otherwise, but I think the method I've given is actually quite quick. Use the function that the accepted answer suggests on the given link. – Danzomida Aug 08 '13 at 10:16
  • I tried using the format above and it doesn't work. I tried using the exact code (on a fresh spreadsheet, getting rid of "Visits!") above and it doesn't work! – user28711 Aug 08 '13 at 14:15
  • Are you saying that you can't get a HLookup to work in a cell? – Danzomida Aug 08 '13 at 14:23
  • It's ok, didn't realise that you used hlookup incorrectly in your answer. – user28711 Aug 08 '13 at 14:38