CODEWITHSUNDEEP
excelvba
liroch
liroch

Reputation: 59

Simply trying to paste the same string into a range of cells with Excel VBA

I need to paste the same string value into every row of an excel spreadsheet. I have defined the string within a loop, via my vba code:

    StickName = Left(myfile, 9)
    ActiveCell.FormulaR1C1 = StickName

It seems so simple to fill each row with the same value and I could do this via looping, writing into each cell until I reach the bottom row of the data, but there must be a more elegant solution. Is there a way to name a range (which would be a single column of data, from row x to y, within my loop) and fill an entire range with the same string? Then re-define the range and the string value for each iteration of the loop? I have searched on fill range with value and other similar searches and scoured the answers, but I don't see anything that addresses what I'm trying to do. Thank you for your help!

Upvotes: 2

Views: 18281

Answers (3)

Sharunas Bielskis
Sharunas Bielskis

Reputation: 1203

You can declare only your variable StickName and use this code:

StickName = Left(myfile, 9)
Range(Cells(startRow, StartColumn),Cells(LastRow, LastColumn)) = StickName

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

You don't need any loops. Say we want to fill rows 7 through11 with some value:

Sub liroch()
   Dim StickName As String, myfile As String

   myfile = "abcdefghijklmnop"
   StickName = Left(myfile, 9)
   Rows("7:11").Value = StickName
End Sub

Upvotes: 2

Sorceri
Sorceri

Reputation: 8033

Define a range and then set the value of the range.

Dim r As Range
Dim s As String
s = "Hello World"
Set r = Sheets(1).Range("A1:A15")
r.Value = s

Upvotes: 4

Related Questions