PDA

View Full Version : Solved: Probem with comparing list code.



noobie
12-25-2006, 06:47 PM
Dear all,

Pls help me on this question. It's been troubling me for quite sometime. And I do not know how to solve this.

Crosspost : http://www.ozgrid.com/forum/showthread.php?t=61092

Pls refer to the above link for more understanding.

The code given to me worked fine. However, if I need the similar code for another sheet, things get complicated.



Private Sub Worksheet_Change(ByVal Target As Range)
Set rWatch = Target(1, 1)
Run "CompareCode2"
Set rWatch = Nothing
End Sub
Private Sub ComboBox1_Change()
If ComboBox1.ListIndex > -1 Then
Set rWatch = ActiveCell
Run "CompareCode2"
Set rWatch = Nothing
End If
End Sub
Private Sub ComboBox2_Change()
If ComboBox1.ListIndex > -1 Then
Set rWatch = ActiveCell
Run "CompareCode2"
Set rWatch = Nothing
End If
End Sub
Private Sub ComboBox3_Change()
If ComboBox1.ListIndex > -1 Then
Set rWatch = ActiveCell
Run "CompareCode2"
Set rWatch = Nothing
End If
End Sub






This is my worksheet code. I do not understand why rWatch is highlited. It states Ambigous name.


The code below is my public module code.



Public rWatch As Range
Sub CompareCode2()

With rWatch.Cells(1, 1)
If Not Intersect(.Cells, Range("b3:b5")) Is Nothing Then
Range("b6") = "Not Found"
For I = 3 To 5: txt = txt & Cells(I, "b").Value & "_": Next
For Each r In Range("G3", Cells(3, Columns.Count).End(xlToLeft))
For I = 0 To 2: txt2 = txt2 & r.Offset(I).Value & "_": Next
If txt = txt2 Then
Range("b6").Value = r.Offset(3).Value
Exit For
End If
txt2 = ""
Next
ElseIf Not Intersect(.Cells, Range("b16:b18")) Is Nothing Then
Range("b19") = "Not Found"
For I = 16 To 18: txt = txt & Cells(I, "b").Value & "_": Next
For Each r In Range("m16", Cells(16, Columns.Count).End(xlToLeft))
For I = 0 To 2: txt2 = txt2 & r.Offset(I).Value & "_": Next
If txt = txt2 Then
Range("b19").Value = r.Offset(3).Value
Exit For
End If
txt2 = ""
Next
ElseIf Not Intersect(.Cells, Range("b29:b30")) Is Nothing Then
Range("b31") = "Not Found"
For I = 29 To 30: txt = txt & Cells(I, "b").Value & "_": Next
For Each r In Range("m29", Cells(29, Columns.Count).End(xlToLeft))
For I = 0 To 1: txt2 = txt2 & r.Offset(I).Value & "_": Next
If txt = txt2 Then
Range("b31").Value = r.Offset(2).Value
Exit For
End If
txt2 = ""
Next
ElseIf Not Intersect(.Cells, Range("b39:b40")) Is Nothing Then
Range("b41") = "Not Found"
For I = 39 To 40: txt = txt & Cells(I, "b").Value & "_": Next
For Each r In Range("m39", Cells(39, Columns.Count).End(xlToLeft))
For I = 0 To 1: txt2 = txt2 & r.Offset(I).Value & "_": Next
If txt = txt2 Then
Range("b41").Value = r.Offset(2).Value
Exit For
End If
txt2 = ""
Next
ElseIf Not Intersect(.Cells, Range("b51:b55")) Is Nothing Then
Range("b56") = "Not Found"
For I = 51 To 55: txt = txt & Cells(I, "b").Value & "_": Next
For Each r In Range("m51", Cells(51, Columns.Count).End(xlToLeft))
For I = 0 To 4: txt2 = txt2 & r.Offset(I).Value & "_": Next
If txt = txt2 Then
Range("b56").Value = r.Offset(5).Value
Exit For
End If
txt2 = ""
Next
End If

End With
Application.EnableEvents = True
End Sub

JimmyTheHand
12-26-2006, 04:25 AM
Hi Noobie :hi:

At first look I can point out 2 odd things. None of them is related to the question, but I show them, nevertheless.

1) Each Combobox_Change event in your code depends on the same Combobox1.Listindex. Might be intentional, sure, but maybe it isn't.

2) In Sub CompareCode2 you examine a bunch of ranges, and do basically the same operations on them. I'm sure this could be contracted into a simpler code.

As for the question itself, it's not clear how you planned it to work on other worksheets. There's no code provided for them, unless you have the same code on each. Why don't you upload the workbook. It would be much easier to understand and solve the problem. Well, for me anyway :)

Aussiebear
12-26-2006, 04:55 AM
I've just had a error message myself on a spreadsheet which suggested I could not name something as I wanted because the name was ambiguous. Turns out I had accidentally tried to use a name twice. Could that be a similar instance here?

noobie
12-27-2006, 05:58 AM
hi guys,
Thanks for your replies. As suggested by both of u, I've tried. But to no avail. An overview of what I'm trying to do.

I'm actually trying to compare lists of data to return the correct value. I don't really know how to explain. Hopefully, my attachment will be of use.
I
n my attached document, the element sheet is what I'm trying to acheive. It worked for that sheet. But when I try it on the RM sheet, it had an error.So i wish to tweak my macro so i can use in the RM sheet.

Pls advise me.

Another thing is, when the data is inputted from the combo box, it has a error. I must convert the text into number before the macro can proceed. Could u suggest something to get rid of this?

Thanks so much. :bow:


Noobie

JimmyTheHand
12-27-2006, 07:21 AM
Hi Noobie :hi:

I see more than one problem here.

1) On sheet "ELEMENT" the reference list begins in column "M", and also the code is stone-carved to look for data from column "M" to the right. This is OK so far. However, on sheet "RM" the reference list begins in column "G", whereas the code still looks in column "M" first.

2) The ranges that are to be checked are not the same size on sheets "ELEMENT" and "RM". Look at this code (copied here from your workbook):
If Not Intersect(.Cells, Range("b3:b6")) Is Nothing Then
Range("b7") = "Not Found"
For I = 3 To 6: txt = txt & Cells(I, "b").Value & "_": Next
For Each r In Range("m3", Cells(3, Columns.Count).End(xlToLeft))
For I = 0 To 3: txt2 = txt2 & r.Offset(I).Value & "_": Next
If txt = txt2 Then
Range("b7").Value = r.Offset(4).Value
Exit For
End If
txt2 = ""
Next "For I = 3 to 6" ensures that this code compares Range("B3:B6") with the list, which is fine on sheet "ELEMENT", however, on sheet "RM" only Range("B3:B5") should be checked, because B6 is the result.

Problem #1 is easy enough to overcome, it needs only a parameter for Sub CompareCode that tells it which column is first. Like this:
Sub CompareCode(Col As String)

Application.EnableEvents = False
With rWatch.Cells(1, 1)
If Not Intersect(.Cells, Range("b3:b6")) Is Nothing Then
Range("b7") = "Not Found"
For I = 3 To 6: txt = txt & Cells(I, "b").Value & "_": Next
For Each r In Range(Col & "3", Cells(3, Columns.Count).End(xlToLeft))
For I = 0 To 3: txt2 = txt2 & r.Offset(I).Value & "_": Next
If txt = txt2 Then
Range("b7").Value = r.Offset(4).Value
Exit For
End If
txt2 = ""
Next


and feeding it with a parameter:
Private Sub Worksheet_Change(ByVal Target As Range)
Set rWatch = Target(1, 1)
CompareCode ("m")
Set rWatch = Nothing
End Sub
But Problem #2, as I see it now, makes a major review necessary :(

I have something in mind. Can't explain in detail, as it's still vague, but I can tell this much:
Name the ranges that need to be checked.
Look for intersections with named ranges.
If intersection is found, determine how many rows are in the found named range.
Use this number of rows in the For... Next loop that does the comparison

I believe it can be done this way. Do you want me to do it?
(I'd be glad to try, but don't want to force my idea on you.)

Jimmy

noobie
12-27-2006, 04:30 PM
Thanks Jimmythehand. According to your suggestions, I'll review my mistake again. And tweak my code. Meanwhile, I'll love to try something easier like you mentioned.


Thanks alot for your step by step explainatory.

Post soon!

noobie
12-28-2006, 07:01 AM
Dear Jimmy,

I've tweaked the code as u have suggested. But there's something wrong with the " rWatch ". It had an error mistake. Pls advise.

Also, due to the some communication fault, I apologized if i'm not able to reply promptly.


Thanks so much in advance.

JimmyTheHand
12-28-2006, 07:37 AM
Hi Noobie,

can you upload the workbook with the tweaked code?

Jimmy

noobie
12-28-2006, 07:28 PM
:hi: Jimmy

Here's the edited one. I only edited the element sheet first. Because I do not want to further complicate the code.


Pls do further guide me.

Thanks.

JimmyTheHand
12-28-2006, 09:19 PM
Thanks Noobie.
I'll get back with some answer later this day. I mean, for you, the next day. :)

noobie
12-28-2006, 10:18 PM
no problem. Take your time. I'm happy enough you're helping me. :)


Thanks alot. :thumb

A happy new year in advance. :hi:

JimmyTheHand
12-29-2006, 05:06 AM
Hi Noobie :hi:

To answer your question, the cause of the error with rWatch was that it was declared on both code modules. The ComboBoxes didn't work because they set rWatch to ActiveCell. Actually, it's the linked cell that's being changed by using the ComboBox, so the linked cell should be sent to further processing, not the ActiveCell. The latter has nothing to do with it all. So:

'this is
Set rWatch = ActiveCell
'this should be
Set rWatch = ComboBox.LinkedCell
I was intrigued by this project, so... :think: I'm not sure I did the right thing, but I revamped the workbook. Considerably. I'll try to sum up.

1) Created named ranges to each item (SMV, SMX, SMI, etc.) on both sheets.
2) Rewrote te code almost completely.
3) Moved the list definitions to a new, separate sheet, called "Definitions"
4) Reformatted the sheet so that it looks better.
5) Renamed ComboBoxes so that the names are more suggestive.
6) Recreated ComboBox_Change events.
(Steps 3-6 apply only to sheet "ELEMENT". I had no patience to do the same to sheet "RM". I trust you can do it yourself. Steps 3-5 are by no means necessary, but they pay off in the long run, I think.)

There's a few things I want to tell about ComboBox_Change events.
They all are essentially the same, only the name is changing. All they do is
1) point rWatch (the input parameter of Sub CompareCode)to the linked cell of the current combobox.
2) Execute CompareCode.

The original ComboBox_Change code in your sample workbook contained those "If ComboBox.ListIndex > -1 Then etc." lines.
They are against the the possibility that the user will write into the combobox a value that is not in the source list. I find this "protection" completely useless, as it won't stop the user writing new values into the linked cell. And even if the user does so, the worst thing to happen is that they get a "Not Found" as a result. So what.
Nevertheless, I left these If..Then lines in, because there might be a reason for them, which I can't see.

About the main code.
The main code (Sub CompareCode) now works with both sheets. The combobox events on sheet "RM" are not updated, so using those cboxes will not update "Outstanding Stock" cells. But direct writing into the named ranges will.

The code will work with any new sheets as well, without modification. If you want to add new sheets, there's 3 things to do.
1) Create the named ranges for all items on the new sheet.
2) Update Function StartColumn
3) Create ComboBoxes, and write the event code for them. (Look at the ComboBox_Change event codes on Sheet "ELEMENT", and you'll see that thisk task is not at all difficult.)

I put explanatory comments into the code so that you can follow the logic. I think it's quite clear, how it works, but if you have questions, just ask.

I hope I didn't ruin your fun too much... :o:

Jimmy

noobie
01-07-2007, 06:32 AM
Hi jimmy,
sorry for the lack of replies. My connection here was poor due to the earthquake. will get back to you tml. Thanks alot!

Nobbie.

noobie
01-08-2007, 02:32 AM
Hi Jimmy,

According to your suggestion, I've deleted the combo box macro, to simplify things further. however, I do not know why this came up.

There was a compliation error stating rWatch as ambigous name.

Pls advise me. Thanks

The code is as below.



Private Sub Worksheet_Change(ByVal Target As Range)
Set rWatch = Target(1, 1)
CompareCode
Set rWatch = Nothing
End Sub



Thanks for your explainatory. It helped me understand. :thumb

JimmyTheHand
01-08-2007, 05:51 AM
Hi Noobie,

As I pointed out in my previous post, the cause of the error concerning rWatch is that rWatch is declared twice. Use Edit --> Find to look for "dim rwatch", and select Curent Project as searched range. There should be two instances found.

noobie
01-08-2007, 11:30 PM
Thanks for your help. This was certainly an easier way out, meanwhile clearing my doubts! Thanks a million Jimmy. :bow:


:beerchug: ,
Noobie