Excel VBA Macro

  • Hi,


    I am having a problem with a VBA code, first I want it to give me a Yes/No message box when a box has text in it which I have managed, I then want it to run a macro when the user clicks "OK" but I keep getting first Error code 1004 and now I am getting "Compile error: Invalid or unqualifited reference.


    I am not that good in Excel VBA yet and my knowledge comes from googling and watching videos so would appreciate help in locating and fixing errors.


    See below for code and errors:


    [INDENT]Private Sub Worksheet_Change(ByVal Target As Excel.Range)[/INDENT]
    [INDENT]
    [/INDENT]
    [INDENT]If Worksheets("Voucher").Range("B37").Value = "" Then[/INDENT]
    [INDENT]Exit Sub[/INDENT]
    [INDENT]Else[/INDENT]
    [INDENT]MsgBox ("Will passengers receive meal voucher?"), vbYesNo[/INDENT]
    [INDENT]Call MealVoucher[/INDENT]
    [INDENT]End If[/INDENT]
    [INDENT]End Sub
    ---------------------------------------------------------------------[/INDENT]
    [INDENT]Sub MealVoucher() <----- Compile error[/INDENT]
    [INDENT]'[/INDENT]
    [INDENT]' MealVoucher Makro[/INDENT]
    [INDENT]' Copies a mealvoucher from the Mealvoucher sheet to the Confirmation sheet[/INDENT]
    [INDENT]'[/INDENT]
    [INDENT]
    [/INDENT]
    [INDENT]'[/INDENT]
    [INDENT] Sheets("Mealvoucher").Select[/INDENT]
    [INDENT] Selection.Copy[/INDENT]
    [INDENT] Sheets("Confirmation").Select[/INDENT]
    [INDENT] .Range("A31").Select <---------- Error 1004
    ActiveSheet.Paste[/INDENT]
    [INDENT] ActiveWindow.SmallScroll Down:=6[/INDENT]
    [INDENT] Range("B37:B38").Select[/INDENT]
    [INDENT]End Sub[/INDENT]


    It needs to copy into A31 in the Confirmation sheet since if I do it below it will be to long down to fit into one page which is important.

  • Re: Excel VBA Macro


    First you need to put your code between code tags. Highlight your individual macros and click on the # sign.


    In your MealVoucher code you are selecting a sheet called "Mealvoucher", copy this sheet and attempt to paste it into "Confirmation" sheet. AFAIK, you can't paste a sheet into another sheet. Mind you, I have not tried it but you can do that yourself.

  • Re: Excel VBA Macro


    In addition to what's already been said.


    I think the Error1004 is being caused by the fact that your Range Property is prefixed with a period, this would imply that you've already confirmed the reference string using a With xxxx statement.


    But no sign of the with statement in the code.


    So you either need to encapsulate that line within a With Statement, put the earlier part of the reference (sheet) prior to the period or remove the period all together.

  • Re: Excel VBA Macro


    In addition to adding code tags to your post, please amend your thread title. Here is an extract from the forum rules which you agreed to when you joined


    Quote

    THREAD TITLES
    1a. Thread titles MUST aptly and concisely describe your problem.
    1b. Your title must describe your problem, not your anticipated solution.
    1c. Use terms suitable to a Google search and do not words like "Please Help", "Urgent", "Excel Problem"
    1d. Do not use Excel formulas in thread titles. E.g. "Help with VLOOKUP" (you have assumed the solution to your problem involves VLOOKUP).


    Why? All of the above makes your code search engine friendly and easier for other people with similar problems to find solutions. It turns Ozgrid into a knowledge base, rather than a forum.



    This thread should receive no more responses until the OP complies with these two requirements.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!