Help me Changing the value to string using quote!!

[stakar]

I have the following code

Code
-------------------

Set rng = Range([BH4], [A65536].End(xlUp)(1, 60))
Set checkboxrange = [A1:BG1].SpecialCells(xlCellTypeConstants, 2)

For Each ThisCell In checkboxrange
s = s & "&" & ThisCell(4).Address(False, False)
Next ThisCell

'Turn off screen
Application.ScreenUpdating = False

rng.ClearContents
[BH4] = "=" & Mid(s, 2, Len(s) - 1)
[BH4].Copy rng
rng.Copy
rng.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

-------------------


The above code concatenate checked cells and copy them till the end o
the range "BH" and then using the copy paste special, copies only th
values.
What I want is , for each value to add the quote (eg '01 or '0 o
'00010 etc) because i want to change them to strings

I found this:
Range("BH4").Value = "'" & CStr([BH4])
-----------------------------------------------
But i cant do anything more!
If its possible the code not to work cell by cell because the range o
rows is huge and it will become very slow

Thanks in advance!!
*********************************
Stathi

 

[Bernie Deitrick]

Stathis,

Change

rng.Copy
rng.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

to
Dim myCell As Range
For Each myCell In rng
myCell.Value = "'" & myCell.Value
Next myCell

HTH,
Bernie
MS Excel MVP

 

[stakar]

*Stathis,

Change

rng.Copy
rng.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

to
Dim myCell As Range
For Each myCell In rng
myCell.Value = "'" & myCell.Value
Next myCell

HTH,
Bernie
MS Excel MVP

I have the following code

Code:
--------------------

Set rng = Range([BH4], [A65536].End(xlUp)(1, 60))
Set checkboxrange = [A1:BG1].SpecialCells(xlCellTypeConstants 2)

For Each ThisCell In checkboxrange
s = s & "&" & ThisCell(4).Address(False, False)
Next ThisCell

'Turn off screen
Application.ScreenUpdating = False

rng.ClearContents
[BH4] = "=" & Mid(s, 2, Len(s) - 1)
[BH4].Copy rng
rng.Copy
rng.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

--------------------


The above code concatenate checked cells and copy them till the en of
the range "BH" and then using the copy paste special, copies onl the
values.
What I want is , for each value to add the quote (eg '01 or '0 or
'00010 etc) because i want to change them to strings

I found this:
Range("BH4").Value = "'" & CStr([BH4])
-----------------------------------------------
But i cant do anything more!
If its possible the code not to work cell by cell because the rang of
rows is huge and it will become very slow

Thanks in advance!!
*********************************
Stathis

Thats all i want But its EXTREMELY SLOW
Is there another way to do it faster like using an array or somethin
like that?

 

[Tom Ogilvy]

Turn calculation to manual. 1000 rows is trivial.

--
Regards,
Tom Ogilvy

*Stathis,

Change

rng.Copy
rng.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

to
Dim myCell As Range
For Each myCell In rng
myCell.Value = "'" & myCell.Value
Next myCell

HTH,
Bernie
MS Excel MVP

I have the following code

Code:
--------------------

Set rng = Range([BH4], [A65536].End(xlUp)(1, 60))
Set checkboxrange = [A1:BG1].SpecialCells(xlCellTypeConstants, 2)

For Each ThisCell In checkboxrange
s = s & "&" & ThisCell(4).Address(False, False)
Next ThisCell

'Turn off screen
Application.ScreenUpdating = False

rng.ClearContents
[BH4] = "=" & Mid(s, 2, Len(s) - 1)
[BH4].Copy rng
rng.Copy
rng.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

--------------------


The above code concatenate checked cells and copy them till the end of
the range "BH" and then using the copy paste special, copies only the
values.
What I want is , for each value to add the quote (eg '01 or '0 or
'00010 etc) because i want to change them to strings

I found this:
Range("BH4").Value = "'" & CStr([BH4])
-----------------------------------------------
But i cant do anything more!
If its possible the code not to work cell by cell because the range of
rows is huge and it will become very slow

Thanks in advance!!
*********************************
Stathis

Thats all i want But its EXTREMELY SLOW
Is there another way to do it faster like using an array or something
like that??