Ivan Paste in VBA

Posted by JACK IN UK on December 26, 2001 3:28 AM

Hi Ivan, i saw your about and i have an issue i working on

ODBC and Unix report some true numbers to LABLES ie left hung not right so Vlookup wont see the data, i need a VBA that will copy a cell and paste as VALUE

IE A1= 100 (Lable aligned to left)
B1 i type =VALUE(A1) press enter copy B1 and paste special and select value to stop the formula, all on the B1 cell, in VBA i need to make that any cell selected ie ActiveCell.Select..... Paste as Value

Record will not give correct syntax, simple enough but i cant slove the paste issue.. any ideas..
In sorting a convertee or numver to format buit also need to get lables to numbers in that formula so UDF will carry the convertion as well

Cheers
Jack in UK

Posted by Scott on December 26, 2001 7:12 AM

I've had this same problem. To solve it, I recorded a macro that copied a zero, and then pasted it over the data that I just imported in using "Paste-Special" and "Add". This seems to re-format back to values.

Posted by Jack on December 26, 2001 8:04 AM

Scott --
Thanks for the tips, coluld you provide some VBS Code?
Say and example!
Cheers!

Posted by Jack on December 26, 2001 8:13 AM

Scott --
Thanks for the tips, coluld you provide some VBS Code?
Say and example!
Cheers!

Posted by Scott on December 26, 2001 12:55 PM

Here is an example of what I was talking about. You have to have a blank cell to use to put the zero for it to copy. In this example I used A1 as a blank cell, and my data was in A3:I95.

Sub Test()

Range("A1").Select
ActiveCell.FormulaR1C1 = "0"
Range("A1").Select
Selection.Copy
Range("A3:I95").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlAdd, SkipBlanks:=False _
, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Selection.ClearContents
End Sub

Hope this works for you.

Scott