PDA

View Full Version : Solved: Help: Adding function to line of code



YellowLabPro
06-10-2006, 08:27 AM
Current code:

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


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


YLP

mdmackillop
06-10-2006, 08:59 AM
ActiveCell.FormulaR1C1 = "=PROPER(TRIM(RC[-1]))"

YellowLabPro
06-10-2006, 09:10 AM
MD,
Instead of using ActiveCell.Formula, how about starting at E2 and down to last cell in E that has
data?

YLP

Norie
06-10-2006, 09:34 AM
Which column is the formula to go in?

Try this.

LastRow = Range("E" & Rows.Count).End(xlUp).Row

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

YellowLabPro
06-10-2006, 11:55 AM
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:
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

YLP

Norie
06-10-2006, 12:32 PM
Sorry but I still can't see which column you want the formula in.

YellowLabPro
06-10-2006, 12:44 PM
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
06-10-2006, 12:51 PM
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
06-10-2006, 02:13 PM
Hi YLP,
Can you post a spreadsheet indicating your original data and the result you want to see.
regards
MD

YellowLabPro
06-10-2006, 02:43 PM
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