Hello,

I am new to the forum, and new to VBA. It is nice to have a place full
of such knowledgeable people!

So, right to it...

I have been creating a quote module for the project managers at my lab,
and none of them are very computer literate, and do not care to become
so. Needless to say making the module user friendly for them has been a
challenge. VBA so far has helped me by leaps and bounds, but I have hit
a snag.

I want them to save each new quote as a unique file once the quote
number has been updated and saved to the master file.
(pardon if my terminology is a little off, as I said, I am not a
professional programmer)

The code I have so far has been running great. It is for a command
button, that increases the quote number by one, and then saves the
file. This afternoon I started trying to figure out how to get it to
save to a unique file, and I am up against a wall. I have looked in my
VBA book, but they don't even mention "SaveAs", and looking online has
been like pulling teeth. I can't find consistant answers that tell me
how VBA is reading the information, and what I'm not giving it.

This is the code so far:

Private Sub CommandButton1_Click()
'Declares Variables of the file name for "SaveAs" function
Dim QNum As String
Dim CNam As String
Dim CrDt As String
Dim VNum As String

'Defines the variable names from Quote Form
QNum = "W10"
CNam = "N19"
CrDt = Format(Now, "mmddyy")
VNum = "AA10"

'Changes the Quote Number to Increase by 1
Range("W10").Value = Range("W10").Value + 1
Range("W10").Copy
Range("W10").PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
'Saves the New Quote Number to the Template
ActiveWorkbook.Save
'Saves the New Quote as a unique file
ActiveWorkbook.SaveAs "X:\_FEE SCHEDULE & QUOTE MODULE\" &
Str(QNum) & Str(CNam) & (CrDt) & " Ver" & Str(VNum)
End Sub

The debugger is coming up with a run time error, type mismatch.

Someone PLEASE help!

Thank you in advance.:)


--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
View this thread: http://www.excelforum.com/showthread...hreadid=497045

The type mismatch comes about in the Str functions in your SaveAs line;
you're passing a string to a function that expects a number. My guess is
that the variables QNum, QNam and VNum are intended to take on the contents
of the cells rather than the cell addresses as you've got them assigned now.
For instance, instead of QNum = "W10", use QNum = Range("W10").Value.
Likewise with QNam and VNum. See if that gets you past the type mismatch.
--Bruce

"Amber_D_Laws" wrote:


Hello,

I am new to the forum, and new to VBA. It is nice to have a place full
of such knowledgeable people!

So, right to it...

I have been creating a quote module for the project managers at my lab,
and none of them are very computer literate, and do not care to become
so. Needless to say making the module user friendly for them has been a
challenge. VBA so far has helped me by leaps and bounds, but I have hit
a snag.

I want them to save each new quote as a unique file once the quote
number has been updated and saved to the master file.
(pardon if my terminology is a little off, as I said, I am not a
professional programmer)

The code I have so far has been running great. It is for a command
button, that increases the quote number by one, and then saves the
file. This afternoon I started trying to figure out how to get it to
save to a unique file, and I am up against a wall. I have looked in my
VBA book, but they don't even mention "SaveAs", and looking online has
been like pulling teeth. I can't find consistant answers that tell me
how VBA is reading the information, and what I'm not giving it.

This is the code so far:

Private Sub CommandButton1_Click()
'Declares Variables of the file name for "SaveAs" function
Dim QNum As String
Dim CNam As String
Dim CrDt As String
Dim VNum As String

'Defines the variable names from Quote Form
QNum = "W10"
CNam = "N19"
CrDt = Format(Now, "mmddyy")
VNum = "AA10"

'Changes the Quote Number to Increase by 1
Range("W10").Value = Range("W10").Value + 1
Range("W10").Copy
Range("W10").PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
'Saves the New Quote Number to the Template
ActiveWorkbook.Save
'Saves the New Quote as a unique file
ActiveWorkbook.SaveAs "X:\_FEE SCHEDULE & QUOTE MODULE\" &
Str(QNum) & Str(CNam) & (CrDt) & " Ver" & Str(VNum)
End Sub

The debugger is coming up with a run time error, type mismatch.

Someone PLEASE help!

Thank you in advance.:)


--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
View this thread: http://www.excelforum.com/showthread...hreadid=497045

Thanks! I never expected such a quick response.
I have a little less than an hour an a half to finish this up before we
all go home for new years.
I will let you know how it goes.
Your explination was very clear on something that has had me puzzled
for a while.
Thanks again!


--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
View this thread: http://www.excelforum.com/showthread...hreadid=497045

Ok. That took care of the run time / type mismatch, but now I have a
slightly different issue.

The first varible is the quote number, which if formated in the cell to
appear as "MOB0000062", of course the value of the cell is only 62.

How do I get the file name to return the text and not the value?

Sorry to be so picky about it.

Thanks for finding my problem for the type mismatch, that alone was a
lifesaver!


--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
View this thread: http://www.excelforum.com/showthread...hreadid=497045

Instead of:
QNum = Range("W10").Value
you could use:
QNum = Range("W10").Text

..text will return the formatted number.

Or if W10 actually contained 62, you could do the formatting yourself in code:

QNum = "MOB" & format(range("w10").value, "0000000")

===
Did I use the right variable names? I was kind of confused.

Amber_D_Laws wrote:

Ok. That took care of the run time / type mismatch, but now I have a
slightly different issue.

The first varible is the quote number, which if formated in the cell to
appear as "MOB0000062", of course the value of the cell is only 62.

How do I get the file name to return the text and not the value?

Sorry to be so picky about it.

Thanks for finding my problem for the type mismatch, that alone was a
lifesaver!

--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
View this thread: http://www.excelforum.com/showthread...hreadid=497045

--

Dave Peterson

Yes, you did use the right variable names, and your solution worked.

However, I just noticed it is saving the file with the number prior t
the change. Any ideas how to fix?

Thanks again! and have a Happy New Year!

Dave Patterson wrote:

Instead of:
QNum = Range("W10").Value
you could use:
QNum = Range("W10").Text

..text will return the formatted number.

Or if W10 actually contained 62, you could do the formatting yoursel
in code:

QNum = "MOB" & format(range("w10").value, "0000000")

===
Did I use the right variable names? I was kind of confused.

Amber_D_Laws wrote:

Ok. That took care of the run time / type mismatch, but now I have a
slightly different issue.

The first varible is the quote number, which if formated in the cel
to
appear as "MOB0000062", of course the value of the cell is only 62.

How do I get the file name to return the text and not the value?

Sorry to be so picky about it.

Thanks for finding my problem for the type mismatch, that alone wa
a
lifesaver!

--
Amber_D_Laws

------------------------------------------------------------------------
Amber_D_Laws's Profile
http://www.excelforum.com/member.php...o&userid=30012
View this thread
http://www.excelforum.com/showthread...hreadid=497045

--

Dave Peterso

--
Amber_D_Law
-----------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...fo&userid=3001
View this thread: http://www.excelforum.com/showthread.php?threadid=49704