Recording Macro with IF(OR(AND function
Closed
Stupidbear
-
May 26, 2011 at 04:51 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 31, 2011 at 09:46 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 31, 2011 at 09:46 PM
Related:
- Recording Macro with IF(OR(AND function
- How to download recording from messenger - Guide
- Audacity listen while recording - Guide
- How to send recording in messenger - Guide
- Spell number in excel without macro - Guide
- Agp function - Guide
3 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 26, 2011 at 04:58 AM
May 26, 2011 at 04:58 AM
May be its just me, but I think for any meaningful insight, one has to see what is the code and where it errors out. Based on information that you provided all I can say is that marco is referencing some thing which it does not like
ActiveCell.FormulaR1C1 = _
=IF(A6="AAA",A6,IF(A6="BBB",A6,IF(A6="CCC",A6,IF(A6="DDD",A6,IF(OR(AND(A6="Guy",D6="Tall"),AND(A6="Guy",D6="Short"),AND(A6="Guy",D6="Thin")),"MAN",IF(OR(AND(A6="Girl",D6="Pretty"),AND(A6="Girl",D6="HOT")),"Woman",IF(AND(A6="Pet",D6="Dog"),"Cute")))))))
Range("E3").Select
ActiveWindow.LargeScroll ToRight:=-1
Range("E2").Select
Selection.Copy
Range("E3").Select
ActiveSheet.Paste
Range("E4").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("E2:E4").Select
Selection.AutoFill Destination:=Range("E2:E94")
Range("E2:E94").Select
Columns("E:E").EntireColumn.AutoFit
I tried to run the above function as part of a macro recording but when i tried to run this recorded Macro, the recording stops here, failed.
=IF(A6="AAA",A6,IF(A6="BBB",A6,IF(A6="CCC",A6,IF(A6="DDD",A6,IF(OR(AND(A6="Guy",D6="Tall"),AND(A6="Guy",D6="Short"),AND(A6="Guy",D6="Thin")),"MAN",IF(OR(AND(A6="Girl",D6="Pretty"),AND(A6="Girl",D6="HOT")),"Woman",IF(AND(A6="Pet",D6="Dog"),"Cute")))))))
Range("E3").Select
ActiveWindow.LargeScroll ToRight:=-1
Range("E2").Select
Selection.Copy
Range("E3").Select
ActiveSheet.Paste
Range("E4").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("E2:E4").Select
Selection.AutoFill Destination:=Range("E2:E94")
Range("E2:E94").Select
Columns("E:E").EntireColumn.AutoFit
I tried to run the above function as part of a macro recording but when i tried to run this recorded Macro, the recording stops here, failed.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 26, 2011 at 05:32 AM
May 26, 2011 at 05:32 AM
Well the reason is your if statement. The thing is the assignment to FORMULAR1C1 is expected to be in form of a string.
.formular1c1 = < A STRING >, example activecell.formular1c1="=row()"
The string starts with a double quote and ends with a double quote. So you need to convert you if statement into a string
"=IF(A6=""AAA"",A6,IF(A6=""BBB"",A6,IF(A6=""CCC"",A6,IF(A6=""DDD"",A6,IF(OR(AND(A6=""Guy"",D6=""Tall""),AND(A6=""Guy"",D6=""Short""),AND(A6=""Guy"",D6=""Thin"")),""MAN"",IF(OR(AND(A6=""Girl"",D6=""Pretty""),AND(A6=""Girl"",D6=""HOT"")),""Woman"",IF(AND(A6=""Pet"",D6=""Dog""),""Cute"")))))))"
Note that all your AAA, BBB etc are enclosed in two double quotes to make " a part of the full string
.formular1c1 = < A STRING >, example activecell.formular1c1="=row()"
The string starts with a double quote and ends with a double quote. So you need to convert you if statement into a string
"=IF(A6=""AAA"",A6,IF(A6=""BBB"",A6,IF(A6=""CCC"",A6,IF(A6=""DDD"",A6,IF(OR(AND(A6=""Guy"",D6=""Tall""),AND(A6=""Guy"",D6=""Short""),AND(A6=""Guy"",D6=""Thin"")),""MAN"",IF(OR(AND(A6=""Girl"",D6=""Pretty""),AND(A6=""Girl"",D6=""HOT"")),""Woman"",IF(AND(A6=""Pet"",D6=""Dog""),""Cute"")))))))"
Note that all your AAA, BBB etc are enclosed in two double quotes to make " a part of the full string
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 27, 2011 at 05:46 AM
May 27, 2011 at 05:46 AM
could you post a sample workbook with code at some shared site and post back the link here.some of such sites are , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 27, 2011 at 07:11 AM
May 27, 2011 at 07:11 AM
DUH, I am so blind
Ok here is one more issue with your formula
you are using R1C1. In that case you cannot refer to cells like A6. You have to say R6C1 (row 6, column 1)
If you want to you A6, instead of R6C1 then you need to use ActiveCell.Formula instead of ActiveCell.FormulaR1C1
In case you want to go with R1C1, then in case you are not aware. this is way you use RC notations
RC : same row same column
R1C: row 1 , same column
RC4: same row, column 4
R[2]C: two rows down from the cell where formula is being written but same column
R[-2]C: two rows up from the cell where formula is being written but same column
R[4]C5: four rows down and column 5
Ok here is one more issue with your formula
you are using R1C1. In that case you cannot refer to cells like A6. You have to say R6C1 (row 6, column 1)
If you want to you A6, instead of R6C1 then you need to use ActiveCell.Formula instead of ActiveCell.FormulaR1C1
In case you want to go with R1C1, then in case you are not aware. this is way you use RC notations
RC : same row same column
R1C: row 1 , same column
RC4: same row, column 4
R[2]C: two rows down from the cell where formula is being written but same column
R[-2]C: two rows up from the cell where formula is being written but same column
R[4]C5: four rows down and column 5
ActiveCell.Formula = _
"=IF(A4=""CaAAA"",A4,IF(A4=""BBB"",A4,IF(A4=""CCC"",A4,IF(A4=""DDD"",A4,IF(OR(AND(A4=""GUY"",D4=""TALL"",AND(A4=""GUY"",D4=""SHORT""),AND(A4=""GUY"",D4=""THIN"")),""MAN"",IF(OR(AND(A4=""GIRL"",D4=""PRETTY""),AND(A4=""GIRL"",D4=""HOT"")),""WOMAN"",IF(AND(A4=""PET"",D4=""DOG""),""CUTE"")))))))"
Range("E3").Select
My current VB formula is already like that of above.
What other mistakes is there that I need to change to get the macro to start working??
Arghhhh!!!
"=IF(A4=""CaAAA"",A4,IF(A4=""BBB"",A4,IF(A4=""CCC"",A4,IF(A4=""DDD"",A4,IF(OR(AND(A4=""GUY"",D4=""TALL"",AND(A4=""GUY"",D4=""SHORT""),AND(A4=""GUY"",D4=""THIN"")),""MAN"",IF(OR(AND(A4=""GIRL"",D4=""PRETTY""),AND(A4=""GIRL"",D4=""HOT"")),""WOMAN"",IF(AND(A4=""PET"",D4=""DOG""),""CUTE"")))))))"
Range("E3").Select
My current VB formula is already like that of above.
What other mistakes is there that I need to change to get the macro to start working??
Arghhhh!!!
