Range from cell value

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:52 PM GMT

Question

QUESTION: Range("K3").Select

   Selection.AutoFill Destination:=Range("K3:S3"), Type:=xlFillDefault
   Range("K3:S3").Select

The above is the formula which I got from creating a macro. The problem that I have is that the Range("K3:S3"), must be taken from the cell value in G3, which will change all the time.


ANSWER: to give you the answer to the question you asked

range(range("g3").value)

will give you what you want - I don't know what K3 contains but an alternative would be

dim holdvar holdvar=range("k3").formular1c1 'was it just the value or a formula for each cell in range(range("g3").value) cell.formular1c1=holdvar next

which has the advantage of not moving the selection

---------- FOLLOW-UP ----------

QUESTION: Firstly thankyou for replying so quickly.

A small detail that I forgot to mention is that the value in k3 would be holding a date value. The value in G3 wold be a number value, so if G3 holds 1 then it must autofill from k3 to k3 and if G3 holds 5 then it must autofill from K3 to O3. Hope that this makes my query a bit more understandable.

Thanking you in advance.

Answer

Sorry for the delay this time - it was my daughters party at the weekend (age 5) and I had my mother visiting - however - try this

dim holdvar holdvar=range("k3").value dim counter for counter=1 to range("G3").value range("K2").offset(0,counter)value=holdvar holdvar=holdvar+1 next


'This assumes that the formatting of the cells is already OK - could be modified to amend the formatting if required.

Advertisement

©2017 eLuminary LLC. All rights reserved.