0

Right now i have code like this :

Sub Loop2()


Dim str As String
str = "BLAH"
Dim X As Integer
    For X = 1 To 20
        Range("A" & X).Value = str
    Next X

End Sub

And this fills up the first column with "BLAH", but I want to fill the first row instead, how do I do this?

0

While you can use RANGE in the form of RANGE("A1"), the RANGE is more often use to define a start and end point ie. RANGE("A1:A20") or RANGE("A1","A20"). If you are assigning a single value to a cell you may want to look at a CELLS. CELLS will define it reference location using Row number and Column number ie CELLS(15,4) or CELLS(15,"D") would reference D15. You can combine CELLS with to define the start and end point of your range numerically using:

RANGE(CELLS(1,1),CELLS(1,20))

which would be the equivalent of A1:A20

In your code replace Range("A" & X).Value = str with CELLS(1,X).Value = str

3
  • I think you meant CELLS(1, X), but it works now. Bonus question -- is this the best way to populate rows with particular objects? – Michael Mao Jul 31 '20 at 3:13
  • @MichaelMao thank you and edit has been made – Forward Ed Jul 31 '20 at 3:37
  • @MichaelMao I am not a VBA expert so not sure if its the best way or not. One thing you could try with range and I am not sure if it will work is RANGE(CELLS(1,1),CELLS(1,20)) = str. I am not sure if that will save you looping through each cell or not. – Forward Ed Jul 31 '20 at 3:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.