#1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default VBA Changes

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 735
Default VBA Changes

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




"raider" wrote in message
...
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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 09:49 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"