UserForm Question...kinda, more like just a regular VBA question

  • Thread starter Thread starter abxy
  • Start date Start date
A

abxy

ok, I've created a userform, that so far does this when i click on m
command button:

Range("A8").Select

Do

Activecell.Offset(1, 0).Select

Loop Until ActiveCell.Value = TextBox1.Value

If ActiveCell.Value = TextBox1.Value Then

ActiveCell.Offset(0, 7).Select

ActiveCell.Value = ComboBox1 & ComboBox2

End If


Now, what i'm doing is basically saying is start in A8, and go dow
until you find the number that i put in TextBox1, once you find th
number, go right 7 spaces and put the values of combobox1 and 2 in th
cell...This works perfectly fine...but i need to amp it up
notch...this is what i need to know how to do:

what code do i add if i want it to go ONLY from A8 to A275, and o
sheet1 thru sheet3, and if it doesn't find a cell with the value tha
equals Textbox1, it goes to a different document and searches from A
to A275, on sheet1 thru sheet3, and if it still doesn't find it, i
returns a message box saying "Didn't find [textbox1 value]"

I know i'm asking a lot, but this would be of great help...any help i
the least would be greatly appreciatted

Thanks in advance all! :
 
Dim bk as Workbook
Dim i as Long
for each bk in Workbooks(Array("Book1.xls", "Book2.xls")
bk.Activate
for i = 1 to 3
worksheets(i).Activate
Range("A8").Select

Do Until ActiveCell.Value = TextBox1.Value or _
ActiveCell.row > 275


Activecell.Offset(1, 0).Select

Loop

If ActiveCell.Value = TextBox1.Value Then

ActiveCell.Offset(0, 7).Select

ActiveCell.Value = ComboBox1 & ComboBox2
Exit sub
end if
Next ' sheet
Next ' workbook
Msgbox "No match made"

Exit Sub


The way you had your loop written, Cell A8 was never checked as a match.
 
Untested, but here is a shot

Sub TestValue()
Dim i As Long, j as Long

For j = 1 Toi 3
For i = 8 To 275
If Worksheets(j).Cells(i,"A").Value = TextBox1.Value Then
Cells(i,"A").Offset(0, 7).= ComboBox1 & ComboBox2
Exit Sub
End If
Next i
Next j
MsgBox "Didn't find " & TextBox1.Value

End Sub
Range("A8").Select

Do

Activecell.Offset(1, 0).Select

Loop Until ActiveCell.Value = TextBox1.Value

If ActiveCell.Value = TextBox1.Value Then
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Sorry. a typo and left some of your code in there,

Amended to

Sub TestValue()
Dim i As Long, j as Long

For j = 1 Toi 3
For i = 8 To 275
If Worksheets(j).Cells(i,"A").Value = TextBox1.Value Then
Worksheets(i).Cells(i,"A").Offset(0, 7).= ComboBox1 &
ComboBox2
Exit Sub
End If
Next i
Next j
MsgBox "Didn't find " & TextBox1.Value

End Sub
Range("A8").Select


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob Phillips said:
Untested, but here is a shot

Sub TestValue()
Dim i As Long, j as Long

For j = 1 Toi 3
For i = 8 To 275
If Worksheets(j).Cells(i,"A").Value = TextBox1.Value Then
Cells(i,"A").Offset(0, 7).= ComboBox1 & ComboBox2
Exit Sub
End If
Next i
Next j
MsgBox "Didn't find " & TextBox1.Value

End Sub
Range("A8").Select

Do

Activecell.Offset(1, 0).Select

Loop Until ActiveCell.Value = TextBox1.Value

If ActiveCell.Value = TextBox1.Value Then
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

abxy > said:
ok, I've created a userform, that so far does this when i click on my
command button:

Range("A8").Select

Do

Activecell.Offset(1, 0).Select

Loop Until ActiveCell.Value = TextBox1.Value

If ActiveCell.Value = TextBox1.Value Then

ActiveCell.Offset(0, 7).Select

ActiveCell.Value = ComboBox1 & ComboBox2

End If


Now, what i'm doing is basically saying is start in A8, and go down
until you find the number that i put in TextBox1, once you find the
number, go right 7 spaces and put the values of combobox1 and 2 in the
cell...This works perfectly fine...but i need to amp it up a
notch...this is what i need to know how to do:

what code do i add if i want it to go ONLY from A8 to A275, and on
sheet1 thru sheet3, and if it doesn't find a cell with the value that
equals Textbox1, it goes to a different document and searches from A8
to A275, on sheet1 thru sheet3, and if it still doesn't find it, it
returns a message box saying "Didn't find [textbox1 value]"

I know i'm asking a lot, but this would be of great help...any help in
the least would be greatly appreciatted

Thanks in advance all! :)
Click to expand...
Click to expand...
 
it really sucks not having the file that i need help on right in fron
of me but anyhoo...there are a few terms i don't understand on Tom'
solution, just due in general to my lack of VBA skills, and I don'
want to get home, and then paste in this solution and then it doesn'
work, and then i don't know what specifically to alter because i don'
know what's going on. so just a few things, could you clarify what the
do in simple spoken terms?

what's going on here? and what is Dim all about?

Dim bk as Workbook
Dim i as Long
for each bk in Workbooks(Array("Book1.xls", "Book2.xls")
bk.Activate
for i = 1 to 3
worksheets(i).Activate

and here...

Next ' sheet
Next ' workbook
Msgbox "No match made"

thank
 
Dim declares a variable,
then it loops through the array of workbooks
activates the current workbook
loops through worksheets 1 to 3 in the current workbook
etc.

Tom's code is also showing you how to handle multiple workbooks as well as
multiple worksheets and multiple cells, but as I don't think you asked for
multiple workbooks and as your understanding seems limited, this is probably
more than you need.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
it goes to a different document and searches . . .

Sounded like he wanted to work with two workbooks to me.

Anyway, it doesn't seem to like using an array as an argument to Workbooks
(and there was a typo)

This has been tested: I put it in the click event of a commandbutton on the
userform.

Private Sub CommandButton1_Click()

Dim bk As Workbook
Dim i As Long
Dim vArr As Variant
vArr = Array("Book1.xls", "Book2.xls")
For j = LBound(vArr) To UBound(vArr)
Set bk = Workbooks(vArr(j))
bk.Activate
For i = 1 To 3
Worksheets(i).Activate
Range("A8").Select
Do Until ActiveCell.Value = TextBox1.Value Or _
ActiveCell.Row > 275
ActiveCell.Offset(1, 0).Select
Loop
If ActiveCell.Value = TextBox1.Value Then
ActiveCell.Offset(0, 7).Select
ActiveCell.Value = ComboBox1 & ComboBox2
Exit Sub
End If
Next ' sheet
Next ' workbook
MsgBox "No match made"

End Sub

--
Regards,
Tom Ogilvy




Bob Phillips said:
Dim declares a variable,
then it loops through the array of workbooks
activates the current workbook
loops through worksheets 1 to 3 in the current workbook
etc.

Tom's code is also showing you how to handle multiple workbooks as well as
multiple worksheets and multiple cells, but as I don't think you asked for
multiple workbooks and as your understanding seems limited, this is probably
more than you need.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combobox will not show list 4
COMBOBOX AND TEXTBOXES QUESTION !! 6
2 If/Then statements in 1 sub not working correctly 2
Load Userform from Array using a Loop 4
Activate Sheet switching 3
userform question 1
Entering data from a userform into multiple sheets 2
Transfer Data from UserForm to Worksheet 8

Back
Top