This code works as is, but pastes the formula values. I need it to paste the actual values:
[VBA]
lastrow = Range("D" & Rows.Count).End(xlUp).Row
Range("E2:E" & lastrow).FormulaR1C1 = "=PROPER(TRIM(RC[-1]))"
[/VBA]
thanks
YLP
This code works as is, but pastes the formula values. I need it to paste the actual values:
[VBA]
lastrow = Range("D" & Rows.Count).End(xlUp).Row
Range("E2:E" & lastrow).FormulaR1C1 = "=PROPER(TRIM(RC[-1]))"
[/VBA]
thanks
YLP
It's not the fastest, but:
[VBA]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[/VBA]
By the way, welcome to the forums
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
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...
No problemOriginally Posted by YellowLabPro
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...You will learn a lot. And I hope I can help you out with whatever you need
By the way, you'll find that there are people here that know WAY more than meso 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!
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
Thanks Joseph.
YLP
You can do this in one operation
[VBA]
lastrow = Range("D" & Rows.Count).End(xlUp).Row
For i = 2 To lastrow
Range("E" & i) = Application.WorksheetFunction.Proper(Trim(Range("D" & i)))
Next
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
MD,
Might I induldge upon you to offer how the two operations differ?
thanks,
YLP
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
[vba]
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
[/vba]
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
Can you post a sample of your data?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Sure,
how do I do this?
Use Manage Attachments in the Go Advanced section. Full details here if required http://vbaexpress.com/forum/showthre...newpost&t=8258
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Ok, thanks.... Here it is.
Assuming you don't neet your data in columns C & D, try the attached.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
MD,
BTW, how did you identify the correct range in my sheet to run this on? I see no references at all.
[vba]
For Each Cel In Intersect(Columns(3), ActiveSheet.UsedRange)
Cel = Application.WorksheetFunction.Proper(Trim(Cel))
Next
[/vba]
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
[VBA]
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)
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
Reply With Quote
