• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I record macro but when I start it I receive error message

L

LyubomirLambov

New Member
I record macro but when I start it I receive error message - Run time error 1004

>>> use code - tags <<<
Code: 
With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:= _
    "=OFFSET(INDIRECT(""System_General_Cargo[[#Headers];[Location]]"");MATCH(B12;INDIRECT(""System_General_Cargo[Location]"");0);1;COUNTIF(INDIRECT(""System_General_Cargo[Location]"");B12);1)"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With
 
Last edited by a moderator:

LyubomirLambov​

Your above give code cannot work alone.
Which line gives that error?
Could You send whole code?
... or much better, if You could send a sample Excel-file, which has that code.
 
A guess, try replacing:
Code: 
"=OFFSET(INDIRECT(""System_General_Cargo[[#Headers];[Location]]"");MATCH(B12;INDIRECT(""System_General_Cargo[Location]"");0);1;COUNTIF(INDIRECT(""System_General_Cargo[Location]"");B12);1)"
with
Code: 
"=OFFSET(INDIRECT(""System_General_Cargo[[#Headers],[Location]]""),MATCH(B12,INDIRECT(""System_General_Cargo[Location]""),0),1,COUNTIF(INDIRECT(""System_General_Cargo[Location]""),B12),1)"
 
p45cal said:
A guess, try replacing:
Code: 
"=OFFSET(INDIRECT(""System_General_Cargo[[#Headers];[Location]]"");MATCH(B12;INDIRECT(""System_General_Cargo[Location]"");0);1;COUNTIF(INDIRECT(""System_General_Cargo[Location]"");B12);1)"
with
Code: 
"=OFFSET(INDIRECT(""System_General_Cargo[[#Headers],[Location]]""),MATCH(B12,INDIRECT(""System_General_Cargo[Location]""),0),1,COUNTIF(INDIRECT(""System_General_Cargo[Location]""),B12),1)"
Click to expand...
Not Working
 
vletm said:

LyubomirLambov​

Your above give code cannot work alone.
Which line gives that error?
Could You send whole code?
... or much better, if You could send a sample Excel-file, which has that code.
Click to expand...
Example code attached.

Macro is recorded. In DataValidation I Paste:
Code: 
=OFFSET(INDIRECT("System_General_Cargo[[#Headers];[Location]]");MATCH(B12;INDIRECT("System_General_Cargo[Location]");0);1;COUNTIF(INDIRECT("System_General_Cargo[Location]");B12);1)
After that I FillDown and everything is working well.
But when run Macro RunTime Error 1004 occures.

I Debug and Delete is coplete. Error shows on this part:
Code: 
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:= _
        "=OFFSET(INDIRECT(""System_General_Cargo[[#Headers];[Location]]"");MATCH(B12;INDIRECT(""System_General_Cargo[Location]"");0);1;COUNTIF(INDIRECT(""System_General_Cargo[Location]"");B12);1)"
 

Attachments

  • 2023 Electrical - Copy.xlsm
    47.5 KB · Views: 3
Last edited by a moderator:

LyubomirLambov​

What should be Your selection before Validation ?
as well as with AutoFill?
Without those - Your code will give Your error.
You've there fixed B12.
That keeps all Your Validations (as below snapshot) in same range - no matter of row in 1693-sheet.
Screenshot 2023-07-27 at 10.53.01.png
 
As I tried to write
# Your code do not know selection before Validation.
... of course, You have selected something - is it (Your selection) correct?
# There are same case with Autofill ... Your code do not know, what is selection before Your code should Autofill something
# If those could work - then there will be same case with all Your Validations as I tried to write above #6 reply's snapshot.
 
See.....
If I Dont use VBA, just use DataValidation an paste This:
=OFFSET(INDIRECT("System_General_Cargo[[#Headers];[Location]]");MATCH(B12;INDIRECT("System_General_Cargo[Location]");0);1;COUNTIF(INDIRECT("System_General_Cargo[Location]");B12);1)
Every thinh work correctly.
When i record marco for this action, macro is generated. But When I run this macro Error runtime 1004
The question is: Why its working normaly without VBA, Why its not working when i Try to validate with data validation
 
It's not okay!
If You'll select B13.
You'll run Your macro.
and after that
You'll check that B13's dropdown, it looks like below
Screenshot 2023-07-27 at 11.13.52.png
Because fixed B12 in Your formula (with VBA).
 
vletm said:
It's not okay!
If You'll select B13.
You'll run Your macro.
and after that
You'll check that B13's dropdown, it looks like below
View attachment 84787
Because fixed B12 in Your formula (with VBA).
Click to expand...
How did you do that? How you run macro without error? (about screenshot)
If I select B13 as you said and run recorded macro same error occuers

Can you give me solution to add validation in C12 with VBA. Validatio must be:
LIST:
=OFFSET(INDIRECT("System_General_Cargo[[#Headers];[Location]]");MATCH(B12;INDIRECT("System_General_Cargo[Location]");0);1;COUNTIF(INDIRECT("System_General_Cargo[Location]");B12);1)
 

LyubomirLambov​

Below code creates correct validation to cell C12:
Code: 
Sub MacroRecorder()
    With Range("C12").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:= _
        "=OFFSET(INDIRECT(""System_General_Cargo[[#Headers],[Location]]""),MATCH(B12,INDIRECT(""System_General_Cargo[Location]""),0),1,COUNTIF(INDIRECT(""System_General_Cargo[Location]""),B12),1)"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub
BUT
I cannot work it with eg cell C13 ... I don't use OFFSET's.
 

LyubomirLambov​

This sample shows other way to do those A- & C-column validations.
... it creates those validations then user will need it (one-by-one)
= no need to run separate code before use or so
 

Attachments

  • 2023 Electrical - Copy.xlsm
    49 KB · Views: 0
You must log in or register to reply here.
Back
Top