How can I excel in Excel

Can't record, excel tells me
authormessage
Hildes
No VBA knowledge


Posted:
March 12, 2008, 2:52 p.m.
Call name: Hildes

       Version: Office 2003

Hello the VBA professionals

I have a formula that works and I want to pack it into a macro.

Start Macro Recorder, and then in the cell, copy and paste the formula and Macro Recorder.

But this time it says "Can't record".
The formula is 304 characters long

Code:
= IF (PART (Q5; 4; 1) * 1 = 7; IF (ISNV (VLOOKUP (Q5; Rg2007,3; FALSE)); "Rg NA"; IF (VLOOKUP (Q5; Rg2007; 3; FALSE) = ""; "open"; VLOOKUP (Q5; Rg2007; 3; FALSE))); IF (PART (Q5; 4; 1) * 1 = 8; IF (ISNV (VLOOKUP (Q5; Rg2008; 3; FALSE)) ; "Rg NV"; IF (VLOOKUP (Q5; Rg2008; 3; FALSE) = ""; "open"; VLOOKUP (Q5; Rg2008; 3; FALSE))); "Rg NV"))


I've already had enough trouble typing them correctly and with the best of intentions I don't know how to translate them into VBA language.

What did I do wrong, how can I put this formula in a macro?
Thank you for your help.
Melanie Breden
MVP Excel 2003 to 2011


Posted:
March 12, 2008, 3:47 pm
First name:
Place of residence: Offenbach district

       Version: Office 2003

Hello,

mark the formula cell and write the following command in the VBA editor in the direct window and confirm the entry with Return:
Code:

? Activecell.Formula


Now you already have your formula with English function names and syntax and have to
just double all the quotation marks.

To test, you can write the formula from the direct window back into the worksheet:
Code:

Activecell.Formula = _
"= IF (MID (Q5,4,1) * 1 = 7, IF (ISNA (VLOOKUP (Q5, RG2007,3, FALSE))," "Rg NV" ", IF (VLOOKUP (Q5, RG2007,3, FALSE) = "" "", "" open "", VLOOKUP (Q5, RG2007,3, FALSE))), IF (MID (Q5,4,1) * 1 = 8, IF (ISNA (VLOOKUP (Q5, RG2008,3, FALSE)), "" Rg NV "", IF (VLOOKUP (Q5, RG2008,3, FALSE) = "" "", "" open "", VLOOKUP (Q5, RG2008,3, FALSE)) ), "" Rg NV "")) "

_________________
greeting
Melanie
Hildes
No VBA knowledge


Posted:
March 12, 2008, 3:59 pm
Call name: Hildes

       Version: Office 2003

Hello Melanie,

I have to be stupid, I don't understand.

I created a macro with no recorder
Code:
? Activecell.Formula


The cell with the formula is selected but when I run the macro I have the debugger.
Hildes
No VBA knowledge


Posted:
March 12, 2008, 4:03 pm
Call name: Hildes

       Version: Office 2003

Hildes - March 12, 2008, 14:59 wrote the following:
Hello Melanie,

I have to be stupid, I don't understand.

I created a macro with no recorder
Code:
? Activecell.Formula



The cell with the formula is selected but when I run the macro I have the debugger.

Edit
Stop! Got it. I have now found this direct window.
Thanks for the tip!
Hildes
No VBA knowledge


Posted:
March 12, 2008, 4:25 pm
Call name: Hildes


       Version: Office 2003

Excellent! It works out.
You only have to rename cell Q5 in this syntax RC [-1] or RC [-2] relative to the cell that you selected at the beginning of the macro.

And then it runs like clockwork.

Thank you Melanie !!
    UpAll times are
GMT + 1 hour
You can Post in this forum.
You can Reply to posts in this forum.
You can your posts in this forum Not to edit.
You can your posts in this forum Not Clear.
You can to polls in this forum Not participate.
You can Files in this forum Not Post
You can Download files in this forum

Related topics
Forum / Topics reply author Calls last contribution
Excel VBA (macros): Record macro: Remove scroll commands5hugo2922201May 03, 2013, 2:02 pm
dera
Excel VBA (macros): record a diagram creation1pumpkin017811 Jul 2012, 8:27 am
RO_SCH
Excel VBA (macros): record cell contents (formulas) with VBA ...14äxcel59605 Apr 2011, 9:29 am
äxcel
Excel VBA (macros): Record events3guest179Feb 26, 2011, 8:35 am
guest
Excel VBA (Macros): Macro Cannot Record Formula7kletscher70211 Aug 2010, 11:28 am
Nicolaus
Excel Help: Record Calc Macro (Text in Columns)1Platinum Storm2004Jul 12, 2010, 3:15 pm
guest
Excel VBA (macros): Macro formula error message when recording0Retty403March 03, 2010, 11:19 am
Retty
Excel VBA (macros): Macro does something different at runtime than when recording2stargazer0128229 Jul 2009, 4:24 pm
stargazer01
Excel formulas: boss says sometimes6Maxi7039507 Jul 2009, 11:45 am
Maxi70
Excel Help: Excel says "can't find file"0gpeter732015March 23, 2009, 6:22 pm
gpeter73
Excel VBA (Macros): Record that sheet protection has been removed4Luki741299Jan 23, 2009, 9:56 am
guest
Excel Help: Macro should record a new macro0Bernd d.R.30503 Apr 2008, 1:33 pm
Bernd d.R.