Code Won't Work If ComboBox Text Is Anything But "01"

[APElliott]

Hello,

I've got the following code below that works like a dream if the ComboBox Text is 01, but if it's anything else it won't work.

Please help!

msheet = ActiveSheet.Name
Select Case msheet
Case "BoQ"
x = 0
For i = 1 To Range("s65536&quot.End(xlUp).Row

If Range("s" & i).Text = ComboBox1.Text Then
If x < 1 Then
rgSel = &quot;B&quot; & i & &quot;&quot; & i

Else
rgSel = rgSel & &quot;,B&quot; & i & &quot;&quot; & i

End If
x = x + 1
Else
End If
Next i
Range(rgSel).Select
Selection.Copy
Case Else
MsgBox &quot;Please Select BoQ To Select Information!&quot;
End Select
End Sub

Thanks, Andrew

 

[rmikesmith]

Andrew,

I don't immediately see anything wrong with your code but without knowing what values are in column &quot;S&quot; and the source of items in your ComboBox, it's difficult to diagnose the problem. I'm guessing there is a mismatch between the entries in the ComboBox (other than &quot;01&quot and what's in column &quot;S&quot; (e.g. extraneous spaces, etc.).

I did notice that the following construct

If x < 1 Then
        rgSel = &quot;B&quot; & i & &quot;:D&quot; & i

    Else
        rgSel = rgSel & &quot;,B&quot; & i & &quot;:D&quot; & i

    End If
x = x + 1

can probably be replaced with

rgSel = rgSel & ",B" & i & ":D" & i

since the statement

rgSel = "B" & i & ":D" & i

will only run the first time through the loop, when rgSel = &quot;&quot; anyway. This will save the overhead of the extra counter, x.

HTH
M. Smith

 

[Loomah]

ok
you haven't given much to go on here but as i've seen this app grow i'll have a guess

put this line in to see waht's happening

x = x + 1
Else
msgbox &quot;Is This Where I Fall Down??&quot;
End If

if this message comes up i'd guess that the data you're looking for in column S isn't there

hoppy friday
If a man says something and there are no women there to hear him, is he still wrong?

 

[APElliott]

Cheers Mike,

I,m really puzzled!

The second test for the code was to select 02

This failed.

03 failed

04 failed

but the majority of the other entries i've tried do work!

If you want to see a copy i,ll mail it to you.

Thanks Andrew

 

[APElliott]

Cheers Loomah,

As i mention to Mike above it is only falling down on selected items, but they definately are there, but i did want your option adding anyway!

You option keeps looping and is a bit tricky to get out of though!

If you want to help a bit more i'll mail the sheet to you so you can see!

Thanks

Andrew

 

[Loomah]

Andrew
Don't know what you mean by my option. If you mean the message keeps popping up and you want to exit the routine at that point put Exit Sub on the next line.

I am actually looking for a job but I'm having a week off so there would be little point in sending the file to me now!! If you still at it after 2nd Sep I might have a look but that will e a busy time for me while the college is studentless!

Happy Friday
&quot;One Man Went To Mow...&quot;
;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[APElliott]

Yeah Sorry Loomah i was refering to the message box.

Added Exit Sub after it now, but the code now tells me that none of the text selected exists.

Don't lose interest now please.

Cheers

 

[Loomah]

Andrew
It's not a case of losing interest, just 15 mins from now I'm off to the Bridge.
Followed by a week off 'work', looking for work.
Followed by another home game.
Followed by a return to 'work'

Try stepping thru the code (F8) to see exactly where it tumbles.
Have you checked for tricky little spaces before or after your text like wot mike suggested?

Keep the faith
Lippy Friday
If a man says something and there are no women there to hear him, is he still wrong?

 

[APElliott]

Cheers,

4 nil to Man U

Thanks

 

[rmikesmith]

Andrew,

Send me your workbook and I'll take a look:

mike.smith@delphiauto.com

Loomah,
Enjoy your time off and Find That Job


Regards,
Mike

 

[rmikesmith]

Andrew,

If you send the workbook over the weekend, please use the following email address:

rmikesmith@earthlink.net

Regards,
Mike