Apply Excel functions to specific cells 1

[RPrinceton]

Hi Everyone,
I have an Excel spreadsheet. I want to conditionally use the 'PROPER' function against certain cells. The code snippet below results in an Excel error that indicates that there is circular reference. I do not know how this can be since I bump the value of r with each interation. How do I conditonally apply a formula to a specific cell?
Please advise. Thx in advance.
Regards,
RPrinceton

code snippet:
Do While r <> lastrow ' Loop until end of rows.
c = 1
r = r + 1
With Worksheets(1).Cells(r, c)
addr = .Address(RowAbsolute:=False) 'get addr in $A1 format
addr = Mid(addr, 2) ' trim $A1, $A2 to A1, A2 etc
if .value > "t"
.Formula = "=PROPER(" & addr & ")"
End With
Loop

 

[GlennUK]

So, you are putting =PROPER(A1) in cell A1 and you don't know why you're getting a circular reference error. Have a quick think about it.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[RPrinceton]

Hi GlenUK,
Perhaps I don't know what a circular reference is.
If you look at my code I am bumping r by 1. This means that
iteration 1 will be address A1, iteration 2 will be address B2, iteration will be address C3 etc. When I think of a circular reference I would think that it is saying that I have already put the 'PROPER' format in A1 and I am attempting to do it again...if I am advancing to row B, C etc with each iteration how can that be circular. What do I not understand, please enlighten me.
Thx in advance!
Regards,
RPrinceton

 

[vbap]

RPrinceton...if you put the formula in A1 from what cell is the formula getting its value? Since you are trying to get the value from itself (the same cell), you get a circular reference.

 

[GlennUK]

RPrinceton,

read vbap's explanation.

You are creating multiple instances of these circular referencing formulae by advanced to new cells to enter new ones.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[RPrinceton]

Hi vbap,
Thank you for the explanation. I will rephrase the question.
My understanding of the 'PROPER' function is that it more or less corrects the case of text within a cell. Let's say for sake of argument I only want to apply the 'PROPER' function to cells A1, E1, G1 M1. How would this be done?
Please advise. Thx in advance!
Regards,
RPrinceton

 

[vbap]

PROPER

Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.

Syntax

PROPER(text)

Text is text enclosed in quotation marks, a formula that returns text, or a reference to a cell containing the text you want to partially capitalize.

Examples

PROPER("this is a TITLE") equals "This Is A Title"

PROPER("2-cent's worth") equals "2-Cent'S Worth"

PROPER("76BudGet") equals "76Budget"


In other words:

In A1 put "what is the time?"
In B1 put =PROPER(A1)

The proper is displayed in cell B1 for the contents in A1.

You'll need something more like this:

'This uses the PROPER function on cells selected.
Sub tst2()
For Each cell In Selection
cell.Value = WorksheetFunction.Proper(cell)
Next
End Sub

Selection can be replaced with a Range of cells.