Run Time 1004 Error

[ipon70]

I have a piece of code

Sub Fix_Page()
Dim ws As Worksheet
Dim range1 As Range, rng As Range
Set ws = ThisWorkbook.Worksheets("Input")
Set rng4 = ws.Range("G8")
With rng4.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=Org"
End With
End Sub

The above code works for most of the cells I am trying to use it for except this one. Because at the point this runs nothing above it has been selected "technically" this cell does "resolve to an error" so you would just normally say "continue". and it does work once selections above it are made. How do I tell VBA "yes I know its an error right now but it will work later" and have it continue with the list and formula for that cell.

Hope that makes sense.

Thanks in advance.

 

[Vogateer]

If it's just an alert, you can try to use the following:

Application.DisplayAlerts = False
' Code that causes the alert here
Application.DisplayAlerts = True

 

[ipon70]

If it's just an alert, you can try to use the following:

Application.DisplayAlerts = False
' Code that causes the alert here
Application.DisplayAlerts = True

So the error is from VBA, but the issue is with the "Name Manager" and the name listed above. Because section 1,2, and 3 haven't been filled out a before this is put in, VBA thinks its an error.
And I did try this and it didn't work

 

[Vogateer]

Yes, sorry, you said 1004 error. I'm just having trouble figuring out what's happening in the code, and I don't know what Section 1, 2, and 3 are for you. Could you give us a screenshot of the sheet and point out where the error is happening?

 

[Alex Blakenburg]

If you are setting the data validation before you have set up the range name "Org" then you would certainly get that error but why would you do that ?

 

[ipon70]

If you are setting the data validation before you have set up the range name "Org" then you would certainly get that error but why would you do that ?

So people are copying and pasting things and that messes up some of the cells that we allow manual typing. Every once in awhile for whatever reason another cell with a validated drop down list will get mess up randomly. The code for that works. But it doesn't have anything to choose or pull the list from until it knows what state, city, and county you are in, then it knows what "orgs" to offer. Because the State, city, and county aren't picked when you press the "fix it" button, it thinks theres an errors but there really isn't. So I need the code to accept that the =Org is ok it just appears broken at the moment.
In the name manager the name Org refers to this code "=OFFSET(Setup!$G$2,1,MATCH(Input!$G$6,Setup!$G$2:$AE$2,0)-1,COUNTA(OFFSET(Setup!$G$2,1,MATCH(Input!$G$6,Setup!$G$2:$AE$2,0)-1,40,1),1))"
In the picture below is what I get when I press the VBA button and what I get when I do it manually. But I promise the Source is good it just doesn't work until things above it are picked.

 

[ipon70]

Yes, sorry, you said 1004 error. I'm just having trouble figuring out what's happening in the code, and I don't know what Section 1, 2, and 3 are for you. Could you give us a screenshot of the sheet and point out where the error is happening?

So people are copying and pasting things and that messes up some of the cells that we allow manual typing. Every once in awhile for whatever reason another cell with a validated drop down list will get mess up randomly. The code for that works. But it doesn't have anything to choose or pull the list from until it knows what state, city, and county you are in, then it knows what "orgs" to offer. Because the State, city, and county aren't picked when you press the "fix it" button, it thinks theres an errors but there really isn't. So I need the code to accept that the =Org is ok it just appears broken at the moment.
In the name manager the name Org refers to this code "=OFFSET(Setup!$G$2,1,MATCH(Input!$G$6,Setup!$G$2:$AE$2,0)-1,COUNTA(OFFSET(Setup!$G$2,1,MATCH(Input!$G$6,Setup!$G$2:$AE$2,0)-1,40,1),1))"
In the picture below is what I get when I press the VBA button and what I get when I do it manually. But I promise the Source is good it just doesn't work until things above it are picked.

 

[Vogateer]

Could you fill out those cells with some standard State, City, and County info, then add the validation, then remove the State, City and County info afterward?