Help with code

**edwardpestian** · 04-29-2006, 01:20 PM

The following code is returning a Compile Error: Automation type not supported in visual basic. The underlined portion of the code is what is highlighted when I get the error message.

Regards,

EP

Sub Button9_Click()

Dim Answer As VbMsgBoxResult
Dim blnCopy As Boolean
If Sheets("Data").Range("Y3").Value = Sheets("Input").Range("G9").Value Then
CopyData:
********Sheets("Input").Range("G15:H29").Copy
********Sheets("Data").Range("D114").PasteSpecial Paste:=xlValues, SkipBlanks:=True
********Sheets("Input").Range("G15:H29").ClearContents
********Sheets("Input").Range("L15:M24").Copy
********Sheets("Data").Range("D130").PasteSpecial Paste:=xlValues
********Sheets("Input").Range("L15:M24").ClearContents
If blnCopy = True Then GoTo ResumeHere
**blnCopy = True
End If
If Range("C15").Value <> Range("F15").Value = False Then
********Answer = MsgBox("These dates have already been entered. Do you want to continue?", vbYesNo)
End If
If Answer = vbYes Then
*GoTo CopyData
ResumeHere:
ElseIf Answer = vbNo Then
** 'Do Nothing
End If
Continue:
****Application.CutCopyMode = False
End Sub

Bob Phillips · 04-29-2006, 01:35 PM

Just declare it as Long.

edwardpestian wrote:
> The following code is returning a Compile Error: Automation type not
> supported in visual basic. The underlined portion of the code is what
> is highlighted when I get the error message.
>
> Regards,
>
> EP
>
> Sub Button9_Click()
>
> Dim _Answer_As_VbMsgBoxResult_
> Dim blnCopy As Boolean
> If Sheets("Data").Range("Y3").Value =
> Sheets("Input").Range("G9").Value Then
> CopyData:
> ********Sheets("Input").Range("G15:H29").Copy
> ********Sheets("Data").Range("D114").PasteSpecial Paste:=xlValues,
> SkipBlanks:=True
> ********Sheets("Input").Range("G15:H29").ClearContents
> ********Sheets("Input").Range("L15:M24").Copy
> ********Sheets("Data").Range("D130").PasteSpecial Paste:=xlValues
> ********Sheets("Input").Range("L15:M24").ClearContents
> If blnCopy = True Then GoTo ResumeHere
> **blnCopy = True
> End If
> If Range("C15").Value <> Range("F15").Value = False Then
> ********Answer = MsgBox("These dates have already been entered. Do you
> want to continue?", vbYesNo)
> End If
> If Answer = vbYes Then
> *GoTo CopyData
> ResumeHere:
> ElseIf Answer = vbNo Then
> ** 'Do Nothing
> End If
> Continue:
> ****Application.CutCopyMode = False
> End Sub
>
>

**edwardpestian** · 04-29-2006, 01:40 PM

I'm new to VBA, so this is a learing experience for me. Can you tell me how to declare it as long.

Regards,

EP

Dave Peterson · 04-29-2006, 03:30 PM

Dim Answer As Long

edwardpestian wrote:
>
> I'm new to VBA, so this is a learing experience for me. Can you tell me
> how to declare it as long.
>
> Regards,
>
> EP
>
> --
> edwardpestian
> ------------------------------------------------------------------------
> edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
> View this thread: http://www.excelforum.com/showthread...hreadid=537485

--

Dave Peterson

Two-Canucks · 04-30-2006, 03:20 PM

Sorry to break the rule of not asking a question here;
but how do I start a new thread?
Clicked on New, then ? new question, then nothing happened.
Could not type in the box. Notihing in Help.
--
Occasionally stumped

"edwardpestian" wrote:

>
> The following code is returning a Compile Error: Automation type not
> supported in visual basic. The underlined portion of the code is what
> is highlighted when I get the error message.
>
> Regards,
>
> EP
>
> Sub Button9_Click()
>
> Dim _Answer_As_VbMsgBoxResult_
> Dim blnCopy As Boolean
> If Sheets("Data").Range("Y3").Value =
> Sheets("Input").Range("G9").Value Then
> CopyData:
> ********Sheets("Input").Range("G15:H29").Copy
> ********Sheets("Data").Range("D114").PasteSpecial Paste:=xlValues,
> SkipBlanks:=True
> ********Sheets("Input").Range("G15:H29").ClearContents
> ********Sheets("Input").Range("L15:M24").Copy
> ********Sheets("Data").Range("D130").PasteSpecial Paste:=xlValues
> ********Sheets("Input").Range("L15:M24").ClearContents
> If blnCopy = True Then GoTo ResumeHere
> **blnCopy = True
> End If
> If Range("C15").Value <> Range("F15").Value = False Then
> ********Answer = MsgBox("These dates have already been entered. Do you
> want to continue?", vbYesNo)
> End If
> If Answer = vbYes Then
> *GoTo CopyData
> ResumeHere:
> ElseIf Answer = vbNo Then
> ** 'Do Nothing
> End If
> Continue:
> ****Application.CutCopyMode = False
> End Sub
>
>
> --
> edwardpestian
> ------------------------------------------------------------------------
> edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
> View this thread: http://www.excelforum.com/showthread...hreadid=537485
>
>

Dave Peterson · 04-30-2006, 07:25 PM

You're posting through the Microsoft Communities web site, right?

There's a New button/icon to the left of the "Show" (all threads, etc) that you
can click on.

Two-Canucks wrote:
>
> Sorry to break the rule of not asking a question here;
> but how do I start a new thread?
> Clicked on New, then ? new question, then nothing happened.
> Could not type in the box. Notihing in Help.
> --
> Occasionally stumped
>
> "edwardpestian" wrote:
>
> >
> > The following code is returning a Compile Error: Automation type not
> > supported in visual basic. The underlined portion of the code is what
> > is highlighted when I get the error message.
> >
> > Regards,
> >
> > EP
> >
> > Sub Button9_Click()
> >
> > Dim _Answer_As_VbMsgBoxResult_
> > Dim blnCopy As Boolean
> > If Sheets("Data").Range("Y3").Value =
> > Sheets("Input").Range("G9").Value Then
> > CopyData:
> > ********Sheets("Input").Range("G15:H29").Copy
> > ********Sheets("Data").Range("D114").PasteSpecial Paste:=xlValues,
> > SkipBlanks:=True
> > ********Sheets("Input").Range("G15:H29").ClearContents
> > ********Sheets("Input").Range("L15:M24").Copy
> > ********Sheets("Data").Range("D130").PasteSpecial Paste:=xlValues
> > ********Sheets("Input").Range("L15:M24").ClearContents
> > If blnCopy = True Then GoTo ResumeHere
> > **blnCopy = True
> > End If
> > If Range("C15").Value <> Range("F15").Value = False Then
> > ********Answer = MsgBox("These dates have already been entered. Do you
> > want to continue?", vbYesNo)
> > End If
> > If Answer = vbYes Then
> > *GoTo CopyData
> > ResumeHere:
> > ElseIf Answer = vbNo Then
> > ** 'Do Nothing
> > End If
> > Continue:
> > ****Application.CutCopyMode = False
> > End Sub
> >
> >
> > --
> > edwardpestian
> > ------------------------------------------------------------------------
> > edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
> > View this thread: http://www.excelforum.com/showthread...hreadid=537485
> >
> >

--

Dave Peterson