Loop through array for value to copy to textbox

[Mr_Ragweed2]

Hello and thanks for reading. I am missing something simple here on a conditional loop. I am first checking to see if a textbox on a userform has a number in it. If it does not, i am looping through the 5 sheets that may have the number in cell P14. Whenever i find the number i want to change the value of textbox4 to the value of the number i found. The below code has no errors, but it not returning the value. (I have made sure the value exists on one of the worksheets in a practice copy.)
What am i not including/doing wrong?

'look for and capture tech id to userform
If TextBox4.Value <> "" = True Then
    End If

Dim OSheets As Variant
Dim d As Variant

    OSheets = Array("Sheet1", "Sheet3", "Sheet10", "Sheet12", "Sheet20")
    
    For Each d In OSheets
        If ActiveSheet.Range("P14").Value = "" Then
        End If
        If ActiveSheet.Range("P14").Value <> "" Then
        ActiveSheet.Range("P14").Select
         UserForm1_MasterSeedOrderForm.TextBox4.Value = ActiveSheet.Range("P14")
         End If
      Next

Thanks again for your time and any assistance.

 

[Micron]

first thing to try is If TextBox4.Value <> "" Then
(get rid of the second logical test - possibly it's looking for -1). Also possible that <> returns False and is then being compared to True, which would always be false.

Regardless of the result of the test, the rest of your code will still run so I don't see the point of the test.

EDIT just noticed this similar test -
If ActiveSheet.Range("P14").Value = "" Then
End If
That does nothing regardless if true or false.

 

[Mr_Ragweed2]

Thank you for responding! I changed to the code below - 1 test only but still no results.

'look for and capture tech id to userform 
Dim OSheets As Variant
Dim d As Variant

    OSheets = Array("Dekalb Seed Order Form", "Allegiant Seed Order Form", "Asgrow Seed Order Form", "Syngenta Seed Order Form", "Croplan Seed Order Form")
    
    For Each d In OSheets
    
        If ActiveSheet.Range("P14").Value <> "" Then
        ActiveSheet.Select
        With Selection
        .Range("P14").Select
        TextBox4.Value = ActiveSheet.Range("P14").Value
        End With
         End If
        Next d

My apologies, but VBA is not my strength. If i were to type it in laymans terms it would read:

If Textbox4 is has something in it, Then do nothing
If Textbox4 is empty, Then look at cell P14 in these 5 sheets
If cell P14 on one of those sheets has something in it,
Select that sheet, that cell and set the value of textbox4 = to cell P14 of the sheet

 

[Micron]

Almost there. When the last step happens, then what? Keep going and over-write if step 3 is true on another sheet - or stop?

 

[Mr_Ragweed2]

Sorry for the delay. When it finds it, then it can stop. It will be the same number on any of the sheets if the number exists at all. Now, it may not exist in either condition in which case i can use a msgbox. "Nothing found - go to web blah blah". It is acceptable for the textbox and the sheets to not have the number. If that is the case i have a hyperlink on the userform that takes the user to the web to generate a number and then they can manually put it in the textbox.

 

[Micron]

Just getting back to your issue now, but another question. Wouldn't it be better to just loop over however many sheets you have in a wb, otherwise if you add a sheet or change its name, you'll have to edit this code? So
- do nothing if textbox4 has a value, else
- loop over all sheets looking in P14 and if a value is found, set textbox to first P14 value found, then stop, else raise message box?

Then you don't need a hard coded array that you might one day need to edit. Can also avoid searching particular sheets if you have others with P14 values that you would not want to grab from. In that case, those names would have to be hard coded, or you'd have to get them from sheet cells.
Just trying to throw out ideas to make this easier for you to maintain but if you prefer the hard coded names, that shouldn't be an issue.

Also, you left off the first code line that would have indicated what will trigger the code to run. Is that a userform button? Something else?
I suspect the main issue with your existing code is the ActiveSheet part. You're not activating sheets as per the array sheet names as far as I can see.

 

[Micron]

Update: modified your code to work with my sheets and userform. This works.

Sub Mr_Ragweed2()
Dim OSheets As Variant
Dim d As Variant

If UserForm.TextBox3 = "" Then
'   OSheets = Array("Dekalb Seed Order Form", "Allegiant Seed Order Form", "Asgrow Seed Order Form", "Syngenta Seed Order Form", "Croplan Seed Order Form")
   OSheets = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")
   For Each d In OSheets
       If Sheets(d).Range("P14") <> "" Then UserForm.TextBox3 = Sheets(d).Range("P14")
   Next
End If

End Sub

I imagine you will be able to adapt. Since you said the number in P14 would be the same value on any or all sheets, I didn't see the point of stopping the search when a value is found.

 

[Mr_Ragweed2]

Thanks. I don't mind hardcoding the array. The odds of it needing changed are slim and if it ever does need changed i can handle it. I'm not really keen on looping through all of the sheets as i can't confirm what will be in that cell now or in the future on other sheets.

As far as triggering this code, it's part of a larger macro attached to a button on the userform. It is basically a sequence of events that moves and formats some data and then puts an image back onto the userform. This bit of code is inserted in a "semi-logical" place as related to the other events so they can unfold in a particular sequence. It does not absolutely have to go where i have it. I can show you the entire code if you like, but it is be unrelated to this bit we are working on.

The ActiveSheet part was a guess. When i used just Sheets or just the range i got errors.

 

[Mr_Ragweed2]

Dude that's awesome!! Works perfectly. I knew it was way simpler than i was making it.

 

[Micron]

You're welcome & don't feel bad about lack of simplicity. I think you can see that I tend to complicate the matter by sometimes thinking too much about what can be.
Thanks for the rep point.