0

The error is:

error: Invalid Outside Procedure

Code:

Sub MyProcedure()
a = Worksheets("ark1").Cells(Rows.Count, 1).End(xlUp).Row

MsgBox (a)

End Sub

my first sheet is called Ark2 and my updated sheet is called Ark1. my first row i wanted to move, is a string and called "nøgletal", my other column is called år and it is integers. i wanted to move all date within cell´s ranging from c2 to c16 and b2 to b16, to the other sheet.

i am now getting the error on line 4 " nøgletal = Range("B2:B16")"

Private Sub CommandButton1_Click()
Dim nøgletal As String, år As Integer
    Worksheets("Ark2").Select
    nøgletal = Range("B2:B16")
    år = Range("C2:C16")
    Worksheets("Ark1").Select
    Worksheets("Ark1").Range("A4").Select
    If Worksheets("Ark1").Range("A4").Offset(1, 0) <> "" Then
    Worksheets("Ark1").Range("A4").End(xlDown).Select
    End If
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = nøgletal
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = år
    Worksheets("Ark2").Select
    Worksheets("Ark2").Range("B2", "B16").Select


End Sub
  • 2
    The error occurs with that first bit of code? It has `End Sub` at the end, but nothing at the start - `Sub MyProcedure()` so it is outside the confines of a procedure (which must start with `Sub ProcedureName()` and end with `End Sub`) so it's outside of a procedure, which is invalid.... – Darren Bartrup-Cook Dec 19 '18 at 09:54
  • 1
    Also `nøgletal = Range("B2", "B16")` won't work. This is trying to reference two cells and put the value of both into a string variable? And this `Worksheets("Ark2").Range("B2", "B16").` doesn't seem to be doing anything. Should it be `"B2:B16"`, but then what is it there for? [Range object](https://learn.microsoft.com/en-us/office/vba/api/excel.range(object)) – Darren Bartrup-Cook Dec 19 '18 at 10:03
  • Thanks guys that all worked out for me !! –  Dec 19 '18 at 10:04
  • It did? Could you show us what you changed - at the moment the second block of code doesn't look like it should work. – Darren Bartrup-Cook Dec 19 '18 at 10:05
  • @DarrenBartrup-Cook when i try it, it says run-time error`13´ type mismatch? –  Dec 19 '18 at 10:06
  • 1
    Also would be worth you reading https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?r=SearchResults&s=1|268.7045 – SJR Dec 19 '18 at 10:11
  • 1
    You haven't said which line errors, but it's probably down to Darren's second comment. – SJR Dec 19 '18 at 10:13
  • Mine says `Syntax Error` because of the last line. If I remove that line completely I get `Type Mismatch` on `nøgletal = Range("B2", "B16")`, the same error will also occur for the following line. Are you just trying to move (rather than copy) the data from `Ark2!C2:C16` to `Ark1!B2:B16` - no other conditions apart from that? – Darren Bartrup-Cook Dec 19 '18 at 10:14
  • sorry @SJR , the line error is currently at line 4 "nøgletal = Range("B2:B16") –  Dec 19 '18 at 10:14
  • Sorry - thinking of rolling back your updates. As you update your question with the corrected code the question vanishes so people with the same problem won't find the answer.... – Darren Bartrup-Cook Dec 19 '18 at 10:16
  • @DarrenBartrup-Cook i am trying to copy data from Ark2 over to Ark1. yes that is also where i am now, with the error –  Dec 19 '18 at 10:17
  • @DarrenBartrup-Cook , it works if i only do for ("B2"), but as soon as i try ("B2:B16") it comes up with mismatch –  Dec 19 '18 at 10:30
  • You can copy data from `C2:C16` to `B2:B16` using either `ThisWorkbook.Worksheets("Ark1").Range("B2:B16").Value = ThisWorkbook.Worksheets("Ark2").Range("C2:C16").Value` if it's just values you're after, or if you want formula/formatting as well then you can use `ThisWorkbook.Worksheets("Ark2").Range("C2:C16").Copy Destination:=ThisWorkbook.Worksheets("Ark1").Range("B2:B16")`. – Darren Bartrup-Cook Dec 19 '18 at 10:53
  • @DarrenBartrup-Cook you are my angle! thank you! –  Dec 19 '18 at 11:38
  • I didn't want to add it as an answer as it doesn't answer the actual question (you're trying to set a range to a string variable), but it resolves the problem. The answer to the question is to define `nøgletal` as a range: `Dim nøgletal As Range: Set nøgletal = Range("B2:B16")` which would get rid of the type mismatch error, but not make the code work. – Darren Bartrup-Cook Dec 19 '18 at 11:42

0 Answers0