VBA code that finds computer configuration matches

[Noir]

I maintain a listing of all workstations and servers purchased by my company. I have each workstation and server broken out by description. A standard (workstation) configuration may look as follows;

Book1, Sheet1
---------------------
A2 - ACME 700Mhz, P3, 256k
A3- 128MB SIMMS
A4 - 64MB Voodoo video card
A5 - CD Writer
A6 - 104 Standard KB
A7 - 17" LR Monitor
A8 - 2 Button Serial Mouse

I double-check my configuration list against the lists maintained by computer tech's (who physically receive the equipment) to ensure that we have accounted for the same number of computer configurations. (Note:The tech's always give me a count of how many configurations they have listed on their list). Many times, i spend "a lot" of time finding that out of 50 or 60 individual configurations i've checked, only 1 or 2 differ.

The following code (Many thanks to Anupam!!) does a search between my sheet1 configuration (A2:A8) and a co-worker workbook Book2, Sheet1, B2:B1000.

(1) I cannot quickly add new config's in my sheet1, colmn A to be search. The code makes you specify the number of lines within your config.

(2) The code will only match the two config's if the config in Book2 has 1 space above and below it separating the config from other data. If the config lines run together with other data, it will not find the config match. I would like to have the code modified so it will simply look for a "consecutive configuration" match no matter what data is above or below it.

Here is the code;

Sub doit()
Dim blnFoundMatch As Boolean
Dim counter As Integer
Dim arrMyconfig(1 To 7) As String
Dim strSheetname As String
Dim strMySheetname As String
blnFoundMatch = False
counter = 0
strMySheetname = "Sheet1"
'This is my data workbook
Workbooks("Book1.xls").Activate
Sheets(strMySheetname).Select
arrMyconfig(1) = Range("A2")
arrMyconfig(2) = Range("A3")
arrMyconfig(3) = Range("A4")
arrMyconfig(4) = Range("A5")
arrMyconfig(5) = Range("A6")
arrMyconfig(6) = Range("A7")
arrMyconfig(7) = Range("A8")
'This is the woorkbook you get from you co -workers
Workbooks("Book2.xls").Activate
Sheets("Sheet1").Select
For i = 2 To 1000 Step 7 'You will have to change the counter base on how many rows you have (1000 for 1000 rows in Book 5)
If Range("B" & i).Value = arrMyconfig(1) Then
If Range("B" & i + 1).Value = arrMyconfig(2) Then
If Range("B" & i + 2).Value = arrMyconfig(3) Then
If Range("B" & i + 3).Value = arrMyconfig(4) Then
If Range("B" & i + 4).Value = arrMyconfig(5) Then
If Range("B" & i + 5).Value = arrMyconfig(6) Then
If Range("B" & i + 6).Value = arrMyconfig(7) Then
blnFoundMatch = True
End If
End If
End If
End If
End If
End If
End If
If blnFoundMatch Then
counter = counter + 1
blnFoundMatch = False
End If
Next i
Workbooks("Book1.xls").Activate
Sheets(strMySheetname).Select
Range("B2").Value = counter


End Sub

Thx,
Noir
This message was edited by Noir on 2002-09-11 06:14

 

[Jim North]

Noir,

In order to accomplish (2) there MUST be some way of determining the start and end of a configuration. In this code the space before and after was used... if that isn't available, how else can the procedure identify a start and end?

 

[Noir]

Jim,
Maybe i'm mistaken but, i thought that the config from my Sheet1 would be used as a template. It would filter through my co-worker's workbooks looking for a "consecutive" data match. Basically, the data itself would tell you where to start and end.

Did i answer your question?

Noir

PS, Is there a way for the code to "record" the configuration data from my Sheet1 and use it as it's instruction to start and end?

 

[hianupam]

I may have a solution to your first problem. It will invlove writing a function that will read in the lines into a dynamic array and pass it on to the main sub routine as an array. I don't have time to delve into this right now but keep that in mind whe you are looking for a solution.

 

[Noir]

A solution to either issue would be a big help

Noir

 

[Jim North]

Noir,

is the system chassis ALWAYS the first item on your list (WITHOUT exception)?

 

[Noir]

Jim,
Yes, although the description will change (eg:Dell, HP, etc.) The System Chassis is always first.

Noir

 

[Jim North]

What is the maximum number of components you will have?

Will your list every have blank lines?

On your sheet is there more than one configuration?

 

[Noir]

-99.999% of the time the lines will be no more than 20.

-My config's will not have blank lines

-To simplify things i was only going to input/search for 1 config at a time

Noir

 

[Noir]

-99.999% of the time the lines will be no more than 20.

-My config's will not have blank lines

-To simplify things i was only going to input/search for 1 config at a time

Noir