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?
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?
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
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.
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'
No you can use A1 notationOriginally Posted by Aussiebear
[vba]
rng.Formula = ...
instead of
rng.FormulaR1C1 = ...
[/vba]
The big advantage of R1C1 notation is that it makes it easier to use variables for rows and/or columns.
For example, I just used this:
[vba]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"") & "")"""[/vba] 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.)
=TEXT(0.05, "+0%") & TEXT(1.05 * RefPrice2, """= ""$0.00") & " (=" & TEXT(1.05 * RefPrice2 * Shares2 - BrkEven2* Shares2, "+$#,##0;-$#,##0") & ")"
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, 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.mycell.Formula = "=VLOOKUP(INDIRECT(" & Chr(34) & r & Chr(34) & "),EventList,2,FALSE)"
Regards,
Simon
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.
[VBA]
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("D2156").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
[/VBA]
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.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
fOUND QUITE A FEW BUGS, BUT THIS AT LEAST RUNS
[VBA]
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
' 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
[/VBA]
Mate I'm going cross eyed here..... You changed "With Sheets" to "With Me".
What else?
Opps! "With ActiveSheet" to "With Me"
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
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.
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.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
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.
I've noticed occasional errors, extra spaces, when posting code here.
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'
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
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
This suggests that although you purportedly are using a worksheet change evnt, you haven't put it in the worksheet code module.
Okay will check this.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
All is now well with the code. MD has housecalled and the patient is up and about walking...
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Posting Permissions
Reply With Quote
