Excel Rept Method

Question

I am using Windows 10 and excel 2016.

I am trying to use a variable as the number of times to repeat "." from the end of the text in a cell to the end of the cell width. When I hard wire a number like this:

   cell.Formula = "=" & Chr(34) & cell.Value & Chr(34) & "& Rept(" & Chr(34) & "." & Chr(34) & ",150)"

the formula works perfectly.

However, when a assign a number to variable and replace the hard wired number with the variable like this:

   thenum = 150

   cell.Formula = "=" & Chr(34) & cell.Value & Chr(34) & "& Rept(" & Chr(34) & "." & Chr(34) & ",thenum)"

I get a #Name? error. The Microsoft method description says that the repeating number must be a positive.

Can someone tell me what I am doing wrong? Or is it that the repeating number must be a hard wired number and will not accept a variable?

Answer 1

Like cell.Value, thenum must be outside the quotes:

cell.Formula = "=" & Chr(34) & cell.Value & Chr(34) & "& Rept(" & Chr(34) & "." & Chr(34) & "," & thenum & ")"

Answer 2

variable as the number of times to repeat "." from the end of the text in a cell to the end of the cell width.

As a suggestion to Andreas excellent helpful point that repeating a character 150 times will "exceed" the width of the cell per your description, and that it is hard to calculate... one suggestion would be to just repeat the character via formatting "@*."

Answer 3

I appreciate your attempt at a response to my question but I resent your inappropriate response. I thought this was a place to help. You should find a better way to spend your time.

Answer 4

Thank you Hans. To be honest with you, I should have known that since I do that in my code all the time but for some reason I didn't make that connection. Thanks again

Answer 5

I am trying to use a variable as the number of times to repeat "." from the end of the text in a cell to the end of the cell width. When I hard wire a number like this:

cell.Formula = "=" & Chr(34) & cell.Value & Chr(34) & "& Rept(" & Chr(34) & "." & Chr(34) & ",150)"

the formula works perfectly.

No, that's an illusion. Make a new file, write "x" into A1 and run this code:

Sub Test()
Dim cell
Set cell = Range("A1")
cell.Formula = "=" & Chr(34) & cell.Value & Chr(34) & "& Rept(" & Chr(34) & "." & Chr(34) & ",150)"
End Sub

We get many more points than the cell is wide.

Furthermore, if we look into the XML file structure (what Excel saves on disk) we can this:

<sheetData>
<row r="1" spans="1:1" x14ac:dyDescent="0.25">
<c r="A1" t="str">
<f>"x"& REPT(".",150)</f>
<v>x......................................................................................................................................................</v>
</c>
</row>
</sheetData>

Unfortunately, determining the actual number of points we need for the width of a cell is very complicated and requires a lot of Windows API code.

From my view, your endeavor is questionable. Why do you want to do something like this?

Andreas.