Solved: Help: Adding function to line of code

[YellowLabPro]

Current code:
[VBA]
ActiveCell.FormulaR1C1 = "=TRIM(RC[-1])"
[/VBA]

How to add PROPER to same line of code to perform in one step?


YLP

[mdmackillop]

[VBA]ActiveCell.FormulaR1C1 = "=PROPER(TRIM(RC[-1]))" [/VBA]

[YellowLabPro]

MD,
Instead of using ActiveCell.Formula, how about starting at E2 and down to last cell in E that has
data?

YLP

[Norie]

Which column is the formula to go in?

Try this.
[vba]
LastRow = Range("E" & Rows.Count).End(xlUp).Row

Range("F2:F" & LastRow).FormulaR1C1="=PROPER(TRIM(RC[-1]))" [/vba]

[YellowLabPro]

Hi Norie,
The previous line of code edits column (D) to remove unwanted characters, this would be the same column, (D), to run Proper and Trim on. But it should start in D2 and .End(xlUp)

Code for reference purposes:
[VBA]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[/VBA]

YLP

[Norie]

Sorry but I still can't see which column you want the formula in.

[YellowLabPro]

Norie,
Sorry. I may be thinking of this incorrectly.
Column D is where the formula and the results would take place. Can the formula be run and results occur in the same cell? Will this work?

[Norie]

Sorry, now you've got me really confused.

The code I posted should place the formula in F for every row where there is data in column E.

Is that what you want or is it something else?

[mdmackillop]

Hi YLP,
Can you post a spreadsheet indicating your original data and the result you want to see.
regards
MD

[YellowLabPro]

MD,
I got this resolved. I finally see how the code was working. Thanks to you and Norie.
I am posting a new topic, but a follow up question regurarding this topic.

YLP