excel vba - VBA: Selecting array which depends on variables for use in lookup formula -

i trying write vba code writes formula cell. formula hlookup array being fixed, on worksheet, , size depending on variables defined.

here relevant part of code (the variables have been defined , integers):

range("c2").select activecell.formula = "=hlookup(a2,visits!range("c2",cells(" & rowsforlook & ", " & rowsforauto & " + 1))," & rowsforlook & " - 1)" 

i have checked there no problems variables. have realised haven't $fixed array because not sure how this. think page reference "visits!" not correct, code did not select array without this. array trying use selected region appear if typed

range("c2",cells(" & rowsforlook & ", " & rowsforauto & " + 1)).select 

thanks.

to fix range suggest - instead of using range() method - build range in string. imagine want produce result looking like:

"=hlookup(a2,visits!c2:d10)" 

(an example - might need pass 3rd parameter false , change rows/columns)

to code dynamically like:

startrow = 2 'generate dynamic string variables endrow = 10 range("c2").formula = "=hlookup(a2,visits!" & "c" & startrow & ":" & "d" & endrow & ")" 

you may need dynamically generate 'c' , 'd' parts too, format should work you.