PDA

View Full Version : Solved: Inserting formulas into code



Aussiebear
09-25-2006, 05:07 AM
When you insert a formula into a section of code, do you have to use the R1C1 format and if so, does this then mean that you need to convert every cell locating in the formula form the Col Row address to R1C1 format?

or is there an easier way?

mdmackillop
09-25-2006, 05:47 AM
Hi Ted,
If you have a complicated formula, try removing the equals sign, then start the macro recorder and reinsert the sign. This can save a lot of grief in getting the quote marks etc. right.

Bob Phillips
09-25-2006, 06:09 AM
When you insert a formula into a section of code, do you have to use the R1C1 format and if so, does this then mean that you need to convert every cell locating in the formula form the Col Row address to R1C1 format?

or is there an easier way?

No you can use A1 notation


rng.Formula = ...

instead of

rng.FormulaR1C1 = ...


The big advantage of R1C1 notation is that it makes it easier to use variables for rows and/or columns.

Cyberdude
09-25-2006, 08:28 PM
For example, I just used this:
Range(?A1?).Formula = "=Text(" & N - 1 & ", ""+0%"") & "" = "" & Text(" & N & "* RefPrice" & Sufx & ", ""$0.00"") & "" (="" & Text(" & N & "* RefPrice" & Sufx & "* Shares" & Sufx & " - BrkEven" & Sufx & "* Shares" & Sufx & " , ""+$#,##0;-$#,##0"") & "")""" to store this in a cell:

=TEXT(0.05, "+0%") & " = " & TEXT(1.05 * RefPrice2, "$0.00") & " (=" & TEXT(1.05 * RefPrice2 * Shares2 - BrkEven2* Shares2, "+$#,##0;-$#,##0") & ")"

which creates a value like this:

+3% = $6.41 (=-$3,094)

(It's odd looking, but it's one of many lines in a table I create.)

Bob Phillips
09-26-2006, 12:53 AM
=TEXT(0.05, "+0%") & " = " & TEXT(1.05 * RefPrice2, "$0.00") & " (=" & TEXT(1.05 * RefPrice2 * Shares2 - BrkEven2* Shares2, "+$#,##0;-$#,##0") & ")"


=TEXT(0.05, "+0%") & TEXT(1.05 * RefPrice2, """= ""$0.00") & " (=" & TEXT(1.05 * RefPrice2 * Shares2 - BrkEven2* Shares2, "+$#,##0;-$#,##0") & ")" :)

Simon Lloyd
09-26-2006, 01:03 AM
Hi i don't know if this will be of help to you but i have used this in code to lookup a named range
mycell.Formula = "=VLOOKUP(INDIRECT(" & Chr(34) & r & Chr(34) & "),EventList,2,FALSE)", for me i just had to ensure that whatever i wanted in the cell by using code had to be in the form of text hence the " before and after the formula.

Regards,
Simon

Aussiebear
09-26-2006, 04:32 AM
Hmmm.... This is what I have attempted to do. Well down is the 4 formula's which I need to refresh the values in a sheet. unfortunately the workbook has become too big for the forum, even when zipped, so i will be relying on your imagination to tell me where and why I've gone off the rails.


Private Sub Worksheet_Change(By Val Target As Range)
' Copy and Paste the active row into the Bucket History Sheet
If Target.Cells.Count>1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
If Target.Column = 22 And Target.Row > 1 And Target = "Cleared" Then
lRow = Sheets("Bucket History").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
With ActiveSheet
.Range(.Cells(Target.Row, 1) .Cells(Target.Row, 22)).Copy
With Sheets("Bucket History")
.Range( .Cells(lRow, 1), .Cells(lRow, 22)).PasteSpecial xlValue
.Range( .Cells(lRow - 1), .Cells(lRow - 1, 22)).Copy
.Range( .Cells(lRow, 1), .Cells(lRow, 22).PasteSpecial xlFormats
End With

'Reset the default values for the row just copied
cRow = Target.Row
.Range("B" & cRow & ":H" & cRow & ",J" & cRow & ",L" & cRow & ",N" & cRow & ",P" & cRow & ",R" & cRow & ",T" & cRow & ":V" & cRow).ClearContents

' Attach a Vendor ID to a Vendor Name
.Range("D2:D156").Formula = "=If(C2="""","""",Vlookup(C2,'Risk Levels'!$A$2:$B$1587,2,False))"

' Determine firstly if the Contract is closed and then if the Vendor Dec is Closed
.Range("E2:E156"). Formula = "=If(B2="""","""",If(IsError(vLookup(Left(B2,50,'Contracts'!B:C,2,0)),If(H2<""0.01"","""", If(H2>G2,""Y"",""N"")), If(Vlookup(Left(B2,5),'Contracts'!B:C,2,0) =""N"",If(H2<""0.01"", """",If(H2>G2,""Y"",""N"")),""Y"")))"

' Attach the current risk level to the vendor
.Range("F2:F156").Formula = "=If(C2="""","""",Vlookup(D2,'Risk Levels'!$B$2:$M$1587,8.False))"

'Reset the delivered tonagge value to zero
.Range("H" & cRow) = 0

Application.EnableEvents = True
End With
End IF

' Repeated section of code for instances where you wish to transfer data rows to Hold History Sheet....
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub



The formula's work fine when placed on the sheet, so will they work if typed as such in the code?

When I tried this at work I initially got a Run time Error 9 "Subscript out of Range " message, ( defaulting at the With Sheets("Bucket History") line in the code)which I cleared by adjusting the code to show the new cell addresses.

Then it popped up with a Run time error 424 "Object not defined" and stopped at the 3rd line in the code " If Target.Cells.Count > 1 Then Exit Code".

I wish I could post the Workbook... it would make so much more sence.

Bob Phillips
09-26-2006, 04:46 AM
fOUND QUITE A FEW BUGS, BUT THIS AT LEAST RUNS



Private Sub Worksheet_Change(ByVal Target As Range)
' Copy and Paste the active row into the Bucket History Sheet
If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
If Target.Column = 22 And Target.Row > 1 And Target = "Cleared" Then
lRow = Sheets("Bucket History").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
With Me
.Range(.Cells(Target.Row, 1), .Cells(Target.Row, 22)).Copy
With Sheets("Bucket History")
.Range(.Cells(lRow, 1), .Cells(lRow, 22)).PasteSpecial xlValue
.Range(.Cells(lRow - 1), .Cells(lRow - 1, 22)).Copy
.Range(.Cells(lRow, 1), .Cells(lRow, 22)).PasteSpecial xlFormats
End With

'Reset the default values for the row just copied
cRow = Target.Row
.Range("B" & cRow & ":H" & cRow & ",J" & cRow & ",L" & cRow & ",N" & cRow & ",P" & cRow & ",R" & cRow & ",T" & cRow & ":V" & cRow).ClearContents

' Attach a Vendor ID to a Vendor Name
.Range("D2:D156").Formula = "=If(C2="""","""",Vlookup(C2,'Risk Levels'!$A$2:$B$1587,2,False))"

' Determine firstly if the Contract is closed and then if the Vendor Dec is Closed
.Range("E2:E156").Formula = "=If(B2="""","""",If(IsError(vLookup(Left(B2,5),'Contracts'!B:C,2,0)),If(H2<""0.01"","""", If(H2>G2,""Y"",""N"")), If(Vlookup(Left(B2,5),'Contracts'!B:C,2,0) =""N"",If(H2<""0.01"", """",If(H2>G2,""Y"",""N"")),""Y"")))"

' Attach the current risk level to the vendor
.Range("F2:F156").Formula = "=If(C2="""","""",Vlookup(D2,'Risk Levels'!$B$2:$M$1587,8,False))"

'Reset the delivered tonagge value to zero
.Range("H" & cRow) = 0

Application.EnableEvents = True
End With
End If

' Repeated section of code for instances where you wish to transfer data rows to Hold History Sheet....
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Aussiebear
09-26-2006, 05:07 AM
Mate I'm going cross eyed here..... You changed "With Sheets" to "With Me".

What else?


Opps! "With ActiveSheet" to "With Me"

Bob Phillips
09-26-2006, 11:29 AM
Can't remember them all but tehre were spaces where there shouldn't be, dots where there should be commas, a 0 where there should have been a ),a nd so on.

Aussiebear
09-26-2006, 12:03 PM
I often find that if I copy a section of code it will lose some of the content by replacing dots and commas etc. Just part of the Great system here in Australia that we get charged squillions to use.

Norie
09-26-2006, 12:11 PM
Aussiebear

I don't think xld was referring to the code, it was probably errors in the formulas.

Also,as far as I'm aware code won't change if copied and pasted.

A formula might though.

mdmackillop
09-26-2006, 02:09 PM
I've noticed occasional errors, extra spaces, when posting code here.

Aussiebear
09-28-2006, 02:30 AM
Okay, running into more issues. Error messages telling me the the use of the " With Me" is invalid use of "Me" so I've gone back to "With Sheets"

Second error message "Member or method not found" and debug points to the next line after With Sheets, and yellows out on the first .Range( .Cells(Target.Row, 1) section of code.

What is causing this?

Ted

Bob Phillips
09-28-2006, 04:22 AM
Okay, running into more issues. Error messages telling me the the use of the " With Me" is invalid use of "Me" so I've gone back to "With Sheets"

This suggests that although you purportedly are using a worksheet change evnt, you haven't put it in the worksheet code module.

Aussiebear
09-28-2006, 12:38 PM
Okay will check this.

Aussiebear
09-30-2006, 05:38 PM
All is now well with the code. MD has housecalled and the patient is up and about walking...