VBA code that finds computer configuration matches

Noir

Active Member
Joined
Mar 24, 2002
Messages
362
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Jim,
Yes, although the description will change (eg:Dell, HP, etc.) The System Chassis is always first.

Noir
 
Upvote 0
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?
 
Upvote 0
-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
 
Upvote 0
-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
 
Upvote 0

Forum statistics

Threads
1,224,395
Messages
6,178,348
Members
452,841
Latest member
GenAkaman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top