venname = replace(Worksheets("Sales").Range("B2"),"&","")
vennumber = replace(Worksheets("Sales").Range("B3"),"&","")
venyear = replace(Worksheets("Data").Range("J15"),"&","")
venperiod = replace(Worksheets("Data").Range("J16"),"&","")



"anduare2" wrote:

Some of my venname data contains an "&" in them. When one is selected from a
dropdown list, it populates cell B2. When the macro runs to save the file I
need to replace the & with the text "and" so my file save routine will not
bomb. But if there is no & in the name I need it to continue on to the save.
It seems like a simple if:then:if:endif would fix it, but I just can't get a
handle on the dim/set value items to get it to replace / what / replacement
code to work for just one instance. Maybe I am looking at it all backwards
again? Here is a list of the macro/code I have pieced together


'Procedure to save the Sales Workbook to the Sharepoint Library

Sub SaveWork()

Dim venname As String
Dim vennumber As String
Dim venyear As String
Dim venperiod As String

venname = Worksheets("Sales").Range("B2")
vennumber = Worksheets("Sales").Range("B3")
venyear = Worksheets("Data").Range("J15")
venperiod = Worksheets("Data").Range("J16")

ActiveWorkbook.SaveAs
Filename:="HTTP://teams.kedc.org/mst/Sales_Reports/" & venname & "_" &
vennumber & "_" & venyear & "_" & venperiod & ".xls"
MsgBox "File was saved as " & ActiveWorkbook.Name
End Sub


If possible please include as much detail in example, I am slightly macro
literate but vb ignorant.

Much Appreciated

Martin