PDA

View Full Version : Solved: How to Paste.Special.Values w/ this line of code



YellowLabPro
06-10-2006, 02:49 PM
This code works as is, but pastes the formula values. I need it to paste the actual values:


lastrow = Range("D" & Rows.Count).End(xlUp).Row
Range("E2:E" & lastrow).FormulaR1C1 = "=PROPER(TRIM(RC[-1]))"


thanks

YLP

malik641
06-10-2006, 03:02 PM
It's not the fastest, but:

Sub test()
Dim cell
Dim lastrow As Integer

lastrow = Range("D" & Rows.Count).End(xlUp).Row
Range("E2:E" & lastrow).FormulaR1C1 = "=PROPER(TRIM(RC[-1]))"

For Each cell In Range("E2:E" & lastrow)
cell.Value = cell.Value
Next
End Sub

By the way, welcome to the forums :hi:

YellowLabPro
06-10-2006, 03:11 PM
Joseph,
Thanks. Worked right away-
Also thanks for the welcoming to the neighborhood. I am just learning, signed up for the training here, so hoping to learn a lot. But is very overwhelming right now... so many things... So I see you are mentor, if you ever feel the need for a mentoree- I am your guy...:friends:

malik641
06-10-2006, 03:17 PM
Joseph,
Thanks. Worked right away-
Also thanks for the welcoming to the neighborhood. I am just learning, signed up for the training here, so hoping to learn a lot. But is very overwhelming right now... so many things... So I see you are mentor, if you ever feel the need for a mentoree- I am your guy...:friends: No problem :thumb You will learn a lot. And I hope I can help you out with whatever you need :friends:

By the way, you'll find that there are people here that know WAY more than me :yes so keep an eye out, you will learn a lot just from reading their posts. And if they get the articles section working again soon, you'll find a lot of info there too.

Good luck on your journey!

YellowLabPro
06-10-2006, 03:19 PM
Thanks Joseph.

YLP

mdmackillop
06-11-2006, 04:04 AM
You can do this in one operation

lastrow = Range("D" & Rows.Count).End(xlUp).Row
For i = 2 To lastrow
Range("E" & i) = Application.WorksheetFunction.Proper(Trim(Range("D" & i)))
Next

YellowLabPro
06-11-2006, 05:29 AM
MD,
Might I induldge upon you to offer how the two operations differ?

thanks,

YLP

mdmackillop
06-11-2006, 05:38 AM
Joseph's code writes the formula in the required range, then loops through that range replacing the formula with the value.

My code uses the worksheet function within VBA to create the value and write it into each cell.

Try stepping through each version and you'll see the difference.

In a small application like this, there won't be any real performance issues, but it's usually quicker if you avoid unneccessary steps.

YellowLabPro
06-11-2006, 08:05 AM
MD,
Thanks for the explanation, exactly what I was looking for.
Ok, I ran into an error implementing your code:
Compile error: Variable not deined. This does not compute w/ me, I thought the way you wrote the code that this was already handled. Can you have a look and see? Code pasted below- the error happens Line 55 Col 9


Sub FilterTildeRecords()
'
' RemoveTilde Macro
' Macro recorded 6/4/2006 by YLP
'
' Keyboard Shortcut: Ctrl+Shift+T
'Application.ScreenUpdating = False
Dim Cell
Dim Lastrow As Integer


Columns("C").Select
Selection.AutoFilter Field:=1, Criteria1:="<>~~*", Operator:=xlAnd
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete
Selection.AutoFilter Field:=1

'Removing "~"

Columns("D").Select
Selection.Replace What:="~~P ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="~~C ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="~* ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="~~", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Columns("E").EntireColumn.Clear

'Proper & Trim


'http://www.vbaexpress.com/forum/showthread.php?p=66057#post66057
'Lastrow = Range("D" & Rows.Count).End(xlUp).Row
'Range("E2:E" & Lastrow).FormulaR1C1 = "=PROPER(TRIM(RC[-1]))"
'For Each Cell In Range("E2:E" & Lastrow)
'Cell.Value = Cell.Value
'Next
Lastrow = Range("D" & Rows.Count).End(xlUp).Row
For I = 2 To Lastrow
Range("E" & I) = Application.WorksheetFunction.Proper(Trim(Range("D" & I)))
Next

Columns("D").EntireColumn.Delete



Range("A1:H1") = Array("Store", "Item#", "OG Records", "~ Removed / Proper & Trim", _
"Qty.", "Dept.", "Cat.", "Price")
Rows("1:1").Font.Bold = True
Rows("1:1").HorizontalAlignment = xlCenter
Cells.Columns.AutoFit


'Home
Range("A1").Select

End Sub


Another thing, I am not receiving email notifications when a response has been posted. And I should clarify, sometimes I do and sometimes I don't.

Thanks,

YLP

mdmackillop
06-11-2006, 08:16 AM
Can you post a sample of your data?

YellowLabPro
06-11-2006, 08:18 AM
Sure,
how do I do this?

mdmackillop
06-11-2006, 09:09 AM
Use Manage Attachments in the Go Advanced section. Full details here if required http://vbaexpress.com/forum/showthread.php?goto=newpost&t=8258

YellowLabPro
06-11-2006, 09:20 AM
Ok, thanks.... Here it is.

mdmackillop
06-11-2006, 10:56 AM
Assuming you don't neet your data in columns C & D, try the attached.

YellowLabPro
06-11-2006, 01:03 PM
That works. In my real worksheet I have two identical columns, D and E just for this reason, to run the function on D, copy to and delete E.

In your version, you ActiveCell.SpecialCells
I am planning on incorporating this to other ranges in this sheet, F:I.
So my thought is we need to name these ranges specifically.
Your thought?

thanks,

ylp

YellowLabPro
06-11-2006, 01:05 PM
MD,
BTW, how did you identify the correct range in my sheet to run this on? I see no references at all.

mdmackillop
06-11-2006, 01:09 PM
For Each Cel In Intersect(Columns(3), ActiveSheet.UsedRange)
Cel = Application.WorksheetFunction.Proper(Trim(Cel))
Next

Intersect uses the intersection of the 3rd column and the "used" area of the spreadsheet. It saves the exercise of determining the last row, which seemed suitable in this case.

mdmackillop
06-11-2006, 01:43 PM
I am planning on incorporating this to other ranges in this sheet, F:I.
So my thought is we need to name these ranges specifically.


A couple of thoughts,
If you are running the code on different columns on the same sheet, you could add the column numbers in an array and loop through them.
If you're not sure where the column will be located, you could use the find method to determine the column and use that answer in the code.
eg

MyCol = Rows(1).Find(What:="OG Records", MatchCase:=False).Column

Columns(MyCol).AutoFilter Field:=1, Criteria1:="<>~~*", Operator:=xlAnd
Range([A1], ActiveCell.SpecialCells(xlLastCell)).EntireRow.Delete
'Removing "~"
Chk = Array("~~P ", "~~C ", "~* ", "~~")
With Columns(MyCol)