VBA Changes

[raider]

Hi All,

I need your assistance with the VBA code below. I would like to add
functionality to the below VBA but so far is not working with my
changes. Please help.

Below is the VBA and the new functionality I would like to include.


Thanks in advance.



Public Sub SalesPerformance(SalesTeamMembers As String)

Dim Team(1 To 20) As Variant
Dim Estimate(1 To 20) As Variant
Dim i, m, p As Integer

' Row Counter on the sales performance worksheet for the list of
SalesIDs with estimates outstanding
p = 3

Set Slist = Range("SalesID")
Set SName = Range("SalesTeamName")
Set Slink = Range("LinkToSalesID")



'PLEASE HELP
'Hi All, this where I would like to check for the below statements
before it gets to the For Loops
'If Sales_Quote_Required <> "" AND Sales_Quote_Completed <> "" then
continue with the For Loops below
'The above if statement does not work, no data is display but I
checked manually and there is data with the above paramenters



For i = 1 To 20
RName = "Team" & i
Set Team(i) = Range(RName)
EName = "EstCom" & i
Set Estimate(i) = Range(EName)
Next

' Loop through SalesIDs and for the team selected write out the
SalesID if they have an estimate outstanding
For Each ID In Slist
i = ID.Row
For m = 1 To 20
If ((InStr(1, Team(m)(i - 1), SalesTeamMembers) > 0) And
(Estimate(m)(i - 1) = "N")) Then
p = p + 1
Sheets("Sales Quotes").Range("A" & p).Value = ID.Value
' SalesID
With Sheets("Sales Quotes")
.Hyperlinks.Add .Range("A" & p), SLink(i)(0) '
Turn the SalesID Number into a link
End With
Sheets("Sales Quotes").Range("B" & p).Value = SName(i)
(0)
End If

 

[Nigel]

Your logic requires that BOTH these two values need to have some text. If a
quote is required AND it is completed - is that correct?

If Sales_Quote_Required <> "" AND Sales_Quote_Completed <> "" then

Also you may find that they contain spaces these will not be seen, so use
TRIM in the above statement or test for the length of the strings instead.

If TRIM(Sales_Quote_Required) <> "" AND TRIM(Sales_Quote_Completed) <> ""
then

or....

If LEN(TRIM(Sales_Quote_Required)) > 0 AND LEN(TRIM(Sales_Quote_Completed))

0 then

--

Regards,
Nigel
(e-mail address removed)