CODEWITHSUNDEEP
vbaexcel
sarcasm24
sarcasm24

Reputation: 47

Write to a range of variable size

I'm trying to build a macro that involves the user inputting some variables. As a result of this I need to be able to select (and autofill) a range of varying size. Below is the code I want to use to autofill this variable range. As you can see, I want to autofill the range A2:Axxx, where xxx = 1 + patientprofiles. The code below works if patientprofiles = 50, for example, but I don't want to have to edit the macro every time; I want the range to adjust itself based on the user inputs.

Dim patientprofiles As Integer

Worksheets("Inputs").Select
patientprofiles = Cells(2, "B").Value

Worksheets("Work").Activate
Cells(2, 1) = 101
Cells(3, 1) = 102

Set SourceRange = Worksheets("Work").Range("A2:A3")
Set fillRange = Worksheets("Work").Range("A2:A51")
SourceRange.AutoFill Destination:=fillRange

Upvotes: 0

Views: 128

Answers (1)

Scott Craner
Scott Craner

Reputation: 152495

One method of the Range() object criterion is a string denoting the beginning and end cells separated by :.

We can concatenate strings using the &. We start with the set items of the string, those that will not change: "A2:A" Then add to that the variable: patientprofiles +1.

The final string is "A2:A" & patientprofiles +1

So:

Set fillRange = Worksheets("Work").Range("A2:A51")

would be:

Set fillRange = Worksheets("Work").Range("A2:A" & patientprofiles +1)

Upvotes: 2

Related Questions