Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > UserForm R1C1 problem
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default UserForm R1C1 problem

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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default UserForm R1C1 problem
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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default UserForm R1C1 problem

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.


--
WinterCoast
------------------------------------------------------------------------
WinterCoast's Profile: http://www.excelforum.com/member.php...o&userid=21044
View this thread: http://www.excelforum.com/showthread...hreadid=377463

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default UserForm R1C1 problem

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


--
WinterCoast
------------------------------------------------------------------------
WinterCoast's Profile: http://www.excelforum.com/member.php...o&userid=21044
View this thread: http://www.excelforum.com/showthread...hreadid=377463

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 692
Default UserForm R1C1 problem
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"
wrote in message
...

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


--
WinterCoast
------------------------------------------------------------------------
WinterCoast's Profile:
http://www.excelforum.com/member.php...o&userid=21044
View this thread: http://www.excelforum.com/showthread...hreadid=377463



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
help on Converting R1C1 to A1 and A1 to R1C1..tia sa2 temp Excel Discussion (Misc queries) 3 September 13th 07 08:31 AM
help on Converting R1C1 to A1 and A1 to R1C1..tia sa2 temp Excel Worksheet Functions 3 September 13th 07 08:31 AM
Range R1C1 notation & the problem with my Macro skiptabor Excel Programming 2 May 31st 05 02:05 PM
looping formula - r1c1 problem - Con't MDC[_2_] Excel Programming 0 October 22nd 03 11:42 PM
looping formula - r1c1 problem MDC[_2_] Excel Programming 2 October 22nd 03 11:13 PM


All times are GMT +1. The time now is 03:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement