UserForm R1C1 problem

W

WinterCoast

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
 
T

Tom Ogilvy

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" <[email protected]>
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
Click to expand...
 
W

WinterCoast

Thanks, Tom. :)
I knew the sort command had to be something simple, I can't believe I
let that get past me. Now if I can find out what's going on with the
R1C1.
 
W

WinterCoast

Does anyone have any ideas why my R1C1 coding is not working? The codes
are going in letter perfect, but give a #NAME? error until I click in
the Formula bar and then press enter, at which time they work
perfectly.

Please help,
Scott
 
S

STEVE BELL

Looks like you may have mixed references:

..Range("K" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],'Abiity
Scores'!A:B,2,FALSE)"

Should be: A:B is A1 notation; C1:C2 is R1C1 notation

..Range("K" & FinalRow + 1).FormulaR1C1 = "=VLOOKUP(RC[-1],'Abiity
Scores'!C1:C2,2,FALSE)"

--
steveB

Remove "AYN" from email to respond
"WinterCoast" <[email protected]>
wrote in message
news:[email protected]...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to use Solver in Excel VBA 1
Trouble selecting multiple sheets using RDB code 13
Lookup Formula Problem 2
Macro to search range for a portion of a specific value and if findit - then paste formula in anothe 2
Why are the two codes below not getting the right formula for myworksheet event change formula? 2
Macro autofills formula when a cell value is changed 4
COPY BY MACRO DOWN TO LAST VALUE CELL IN COLUMN "A" 1
Problem with line of code / syntax 4

Top