Loop function!

[Lbarron]

Hi all,

I'm trying to write some code in excel vba that will use the loop command to print numbers to cells, say from 1 to 100. I can get the numbers to print to cells however what I also need is each number to be wrapped in symbols like this.

"X" ,

Including the comma.

I've tried a few things but loose the effect due to the compiler seeing the comma as a next expression.

My last code was basically ......value = i ± 1
Next i

Any idea please?

 

[Norie]

Can you post your current code and some examples of expected results?

 

[My Aswer Is This]

Is this what you want?

Here are two versions:

Sub Count_Me()
Dim i As Long
    For i = 1 To 20
        Cells(i, 1).Value = i & "X"
    Next
End Sub

Sub Count_Two()
Dim i As Long
    For i = 1 To 20
        Cells(i, 2).Value = "*" & i & "X"
    Next
End Sub

 

[Lbarron]

The bottom one looks very promising and very similar to my current code

In each cell I need it to read "1" , then "2" , and so on till "100" ,

I need the cells to display the characters so the quotation symbol and the comma.

 

[Lbarron]

Thanks for the help BTW!

 

[My Aswer Is This]

Is this what you want:

Sub Count_Me()
Dim i As Long
Dim ans As String
ans = """"
    For i = 1 To 20
        Cells(i, 1).Value = ans & i & ans
    Next
End Sub

 

[FormR]

I need the cells to display the characters so the quotation symbol and the comma.

Hi, welcome to the forum, here is a different non-looping option you can also try:

Sub m()
With Range("A1").Resize(100)
    .Value = Evaluate("CHAR(34)&ROW(1:" & .Rows.Count & ")&CHAR(34)& "" ,""")
End With
End Sub

 

[Lbarron]

I've tried something similar. Not sure it will show the comma but thank you. I will try this!

 

[Lbarron]

Thanks formR never seen anything like this but will try it! Thanks again!

 

[Rick Rothstein]

Thanks formR never seen anything like this but will try it! Thanks again!

Then you might not have seen anything like this either Since your range and output to it is fixed (that is, not dependent on variables), there is this compact one-liner available...

Sub Counter()
  [A1:A100] = [""""&ROW(1:100)&""","]
End Sub