PDA

View Full Version : How to build a command string to execute



bassnsjp
06-25-2009, 06:34 AM
Im using MS 2003 and Windows XP Pro. I don't have access to the PC that contains the VBA macro I'm writing so I will have to type an example. This is what I need to do:

Take a part number from the Required Inventory and attempt to locate it in the Available Inventory. Currently I do this by performing an InStr compare however, that sometimes finds an incorrect part number, but in fact the correct part number exists, but since the incorrect part number is encountered first it is used. Now, there are valid instances in which the Required part number is embedded in the Available part number, which is why I'm currently using InStr.

Now, to remedy the situation I'm thinking of performing a search in the Available Inventory using the StrComp function in attempt to locate part number strings that match dit for dit. If I don't find it then go back and perform the search again using InStr.

I was thinking of accomplishing this by building the string compare command and sending that to the search routine to use at the appropriate time.

strcmd = """StrComp(" & AvailPN "," & ReqdPN & ", " & "vbTextCompare)"

or

strcmd = """InStr(1, " & AvailPN "," & ReqdPN & ", " & "vbTextCompare)"

If strcmd = 0 then
do something
else
do something else
end if

Is it possible to build a command string? I haven't had much luck in getting this to work. Any assistance would be greatly appreciated, thanks in advance. :help

Steve

GTO
06-25-2009, 12:12 PM
Greetings Steve,

Could you post a small example wb with some req'd and avail numbers, indicating which req'd numbers find the correct avail numbers and which foul?

Mark

bassnsjp
06-25-2009, 03:22 PM
Mark,

Does this small example help.

Reqd_CommentReqd_PNAvail_PNThis PN would not be found2879034890This PN has an exact match348909876F-GXThis PN has an embedded match9876FAJK765This PN has an embedded matchJK765XXHP98

bassnsjp
06-25-2009, 03:25 PM
Mark,

Well that didn't work very well, let me try again.


'Reqd_Comment Reqd_PN Avail_PN
'This PN would not be found 28790 34890
'This PN has an exact match 34890 9876F-GX
'This PN has a valid embedded match 9876F AJK765
'This PN has a valid embedded match JK765 XXHP98

Aussiebear
06-25-2009, 10:06 PM
A small workbook with non confidential data would be extremely useful

bassnsjp
06-26-2009, 03:11 PM
How would I attach a workbook?

GTO
06-26-2009, 09:07 PM
Greetings Steve,

To attach a workbook, press the Go Advanced button right below the quick reply box.

You will see an Additional Options section below the reply box there. Click the Manage Attachments button; self-explanatory after that.

Now as to your current sample date, not sure this is what you want, but looks like maybe.

In this example, the headers will be in A1 and B1...


Option Explicit
Sub GetEmbedded()
Dim rngSearchRange As Range
Dim rngMatch As Range
Dim rngReqd As Range, rCell As Range

'// I used the default codename of Sheet1 //
With Sheet1
'// Set the range of available part numbers //
Set rngSearchRange = .Range("B2:B" & .Cells(Rows.Count, "B").End(xlUp).Row)
'// Set the range of req'd part numbers //
Set rngReqd = .Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row)

For Each rCell In rngReqd
'// For each Req'd part number, try to find exact match first//
Set rngMatch = rngSearchRange.Find(What:=rCell.Value, _
After:=rngSearchRange(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext)
'// If that works, incdicate where it was found in Col C
If Not rngMatch Is Nothing Then
rCell.Offset(, 2).Value = "Match at: " & rngMatch.Address(False, False)
Set rngMatch = Nothing
Else
'// Else, try to find a partial
Set rngMatch = rngSearchRange.Find(What:=rCell.Value, _
After:=rngSearchRange(1, 1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext)
'// If partial found, remark as above, else indicate not found //
If Not rngMatch Is Nothing Then
rCell.Offset(, 2).Value = "Partial at: " & rngMatch.Address(False, False)
Set rngMatch = Nothing
Else
rCell.Offset(, 2).Value = "Not found"
End If
End If
Next
End With
End Sub


Hope that helps,

Mark