Data Validation not seeing My Dynamic Range PLEASE HELP

[bamaisgreat]

I had the great idea of changing some of my named ranges to Dynamic Ranges.
I did not research this before doing so, now nothing shows up in my drop downs.
All I have in the Data Valadation Source Box is =INDIRECT (I28)
I have read that Dynamic and Indirect dont go together.
If someone could would you Please modify =INDIRECT (I28)
so it will work. This is very frustrating.
Thank you all

 

[bamaisgreat]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Wow, I should have caught that.
Thanks you saved me again.

 

[bamaisgreat]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Biff in regards to this Thread I have managed to get all the Dynamic Ranges together.I have a sheet named "LIST" which I am trying to constuct a userform with a command button that will simply add the data in textbox1 to the last empty row in column A and textbox2 the data will go to the last empty row in column B. The code below is bits and pieces of what Ive gathered up but I cant seem to get it put together correctly.The lower part of the code suggest entering it in pairs which would be nice. Thanks Biff you have helped me alot.

Private Sub CommandButton1_Click()
Dim nxtRwE as long, nxtRwF as long
nxtRwE = Sheets("List").Range("E" & Rows.Count).End(xlUp).Row + 1
nxtRwF = Sheets("List").Range("F" & Rows.Count).End(xlUp).Row + 1
Then Textbox1.value goes to Range("E" & nxtRwE) and
Textbox2.value goes to Range("F" & nxtRwF)
If these values will always be entered as pairs you can simplify this and just have one variable nxtRwE
then Textbox1.Value goes to Range("E" & nxtRwE) and Textbox2.Value goes to
Range("E" & nxtRwE).Offset(0,1)
End sub

 

[Andrew Poulsom]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Is this what you want?

Private Sub CommandButton1_Click()
    Dim NextRow As Long
    NextRow = Sheets("List").Range("E" & Rows.Count).End(xlUp).Row + 1
    Range("E" & NextRow).Value = TextBox1.Value
    Range("E" & NextRow).Offset(0, 1).Value = TextBox2.Value
End Sub

 

[bamaisgreat]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Sorry I should not have specified them being together.
TextBox1 go to last empty row in column A
TextBox2 go to last empty row in column B

 

[Andrew Poulsom]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Change "E" to "A" in the code I posted.

 

[bamaisgreat]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Like this maybe:

Private Sub CommandButton1_Click()
Dim NextRow As Long
NextRow = Sheets("List").Range("A" & Rows.Count).End(xlUp).Row + 1
NextRow = Sheets("List").Range("B" & Rows.Count).End(xlUp).Row + 1
Range("A" & NextRow).Value = TextBox1.Value
Range("B" & NextRow).Value = TextBox2.Value
End Sub

 

[Andrew Poulsom]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

More like:

Private Sub CommandButton1_Click()
    Dim NextRow As Long
    NextRow = Sheets("List").Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("A" & NextRow).Value = TextBox1.Value
    NextRow = Sheets("List").Range("B" & Rows.Count).End(xlUp).Row + 1
    Range("B" & NextRow).Value = TextBox2.Value
End Sub

 

[bamaisgreat]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

thanks again. big help

 

[bamaisgreat]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Andrew is there something that can be done so if one of the textboxes has data in it then the other has to have data in it. Make since?

Private Sub CommandButton1_Click()
Dim NextRow As Long
NextRow = Sheets("List").Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A" & NextRow).Value = TextBox1.Value
NextRow = Sheets("List").Range("B" & Rows.Count).End(xlUp).Row + 1
Range("B" & NextRow).Value = TextBox2.Value
End Sub

 

[Andrew Poulsom]

Re: Data Valadation not seeing My Dynamic Range PLEASE HELP

Try:

Private Sub CommandButton1_Click()
    Dim NextRow As Long
    If TextBox1.Value = "" Or TextBox2.Value = "" Then
        MsgBox "Please complete both TextBoxes."
        Exit Sub
    End If
    NextRow = Sheets("List").Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("A" & NextRow).Value = TextBox1.Value
    NextRow = Sheets("List").Range("B" & Rows.Count).End(xlUp).Row + 1
    Range("B" & NextRow).Value = TextBox2.Value
End Sub