The sort command should be:
.Cells.Sort Key1:=.Range("CF2"), Order1:=xlAscending, Key2:=.Range("A2") _
, Order2:=xlAscending, Header:=xlYes
with periods in front of all Range elements.
Not sure why your R1C1 is not working.
--
Regards,
Tom Ogilvy
"WinterCoast"
wrote in message
...
I have designed a form that takes in a lot of information and then saves
that information to a sheet called 'Data'. Each entry is contained on
it's own row and there are some formulas involved. To simplify things,
I wrote the formulas in R1C1 notation in VBA.
The problem occurs when the macro saves the formulas to their
designated cells. Each and every one of them comes up with the #NAME?
error, even though the syntax of the formula in the Formula Bar is
perfect. I can fix this by clicking into the Formula Bar as if I am
going to edit the formula, and immediately pressing Enter. The formula
does not change, but suddenly it works. What can I change in my code in
order to get this to work?
Code:
--------------------
Private Sub cmdOK_Click()
With Worksheets("Data")
FinalRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("A" & FinalRow + 1).Value = txtCharacterName.Value
.Range("B" & FinalRow + 1).Value = txtInitiative.Value
.Range("C" & FinalRow + 1).FormulaR1C1 = "=20-(RC[-1])"
.Range("D" & FinalRow + 1).Value = txtFort.Value
.Range("E" & FinalRow + 1).Value = txtReflex.Value
.Range("F" & FinalRow + 1).Value = txtWill.Value
.Range("G" & FinalRow + 1).Value = txtListen.Value
.Range("H" & FinalRow + 1).Value = txtSearch.Value
.Range("I" & FinalRow + 1).Value = txtSpot.Value
.Range("J" & FinalRow + 1).Value = txtSTR.Value
.Range("K" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],'Abiity
Scores'!A:B,2,FALSE)"
.Range("L" & FinalRow + 1).Value = txtDEX.Value
.Range("M" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],'Abiity
Scores'!A:B,2,FALSE)"
.Range("N" & FinalRow + 1).Value = txtCON.Value
.Range("O" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],'Abiity
Scores'!A:B,2,FALSE)"
.Range("P" & FinalRow + 1).Value = txtINT.Value
.Range("Q" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],'Abiity
Scores'!A:B,2,FALSE)"
.Range("R" & FinalRow + 1).Value = txtWIS.Value
.Range("S" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],'Abiity
Scores'!A:B,2,FALSE)"
.Range("T" & FinalRow + 1).Value = txtCHA.Value
.Range("U" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],'Abiity
Scores'!A:B,2,FALSE)"
.Range("V" & FinalRow + 1).FormulaR1C1 =
"=RC[-9]+RC[3]+RC[9]+RC[15]+RC[21]+RC[27]+RC[33]+RC[39]+RC[45]+RC[51]+RC[57]
+RC[59]+RC[60]"
.Range("W" & FinalRow + 1).FormulaR1C1 =
"=RC[-9]+RC[38]+RC[44]+RC[50]+RC[56]+RC[58]+RC[59]"
.Range("X" & FinalRow + 1).FormulaR1C1 = "=RC[-2]-RC[-11]"
.Range("Y" & FinalRow + 1).FormulaR1C1 = "=MAX(RC[1]:RC[5])"
.Range("Z" & FinalRow + 1).Value = txtArmor.Value
.Range("AE" & FinalRow + 1).FormulaR1C1 = "=MAX(RC[1]:RC[5])"
.Range("AF" & FinalRow + 1).Value = txtArmorEnhance.Value
.Range("AK" & FinalRow + 1).FormulaR1C1 = "=MAX(RC[1]:RC[5])"
.Range("AL" & FinalRow + 1).Value = txtShield.Value
.Range("AQ" & FinalRow + 1).FormulaR1C1 = "=MAX(RC[1]:RC[5])"
.Range("AR" & FinalRow + 1).Value = txtShieldEnhance.Value
.Range("AW" & FinalRow + 1).FormulaR1C1 = "=MAX(RC[1]:RC[5])"
.Range("AX" & FinalRow + 1).Value = txtNatural.Value
.Range("BC" & FinalRow + 1).FormulaR1C1 = "=MAX(RC[1]:RC[5])"
.Range("BD" & FinalRow + 1).Value = txtNaturalEnhance.Value
.Range("BI" & FinalRow + 1).FormulaR1C1 = "=MAX(RC[1]:RC[5])"
.Range("BJ" & FinalRow + 1).Value = txtDeflection.Value
.Range("BO" & FinalRow + 1).FormulaR1C1 = "=SUM(RC[1]:RC[5])"
.Range("BP" & FinalRow + 1).Value = txtDodge.Value
.Range("BU" & FinalRow + 1).FormulaR1C1 =
"=IF(ISBLANK(RC[3])=FALSE,RC[4],RC[2])"
.Range("BV" & FinalRow + 1).Value = cboSize.Value
.Range("BW" & FinalRow + 1).FormulaR1C1 =
"=VLOOKUP(RC[-1],Tables!A2:C10,2,FALSE)"
.Range("BZ" & FinalRow + 1).Value = txtPrestige1Name.Value
.Range("CA" & FinalRow + 1).Value = txtPrestige1.Value
.Range("CB" & FinalRow + 1).Value = txtPrestige2Name.Value
.Range("CC" & FinalRow + 1).Value = txtPrestige2.Value
If chkWisdom = True Then .Range("CD" & FinalRow + 1).FormulaR1C1 =
"=RC[-63]"
.Range("CE" & FinalRow + 1).Value = cboType.Value
.Range("CF" & FinalRow + 1).FormulaR1C1 =
"=VLOOKUP(RC[-1],Tables!E2:F4,2,FALSE)"
.Cells.Sort Key1:=Range("CF2"), Order1:=xlAscending, Key2:=Range("A2") _
, Order2:=xlAscending, Header:=xlYes
End With
Unload Me
End Sub
--------------------
Also, I am confused as to why the sort command at the very bottom gives
me an error as well.
Thanks,
Scott
--
WinterCoast
------------------------------------------------------------------------
WinterCoast's Profile:
http://www.excelforum.com/member.php...o&userid=21044
View this thread: http://www.excelforum.com/showthread...hreadid=377463