offset method problem

A

Axel

Hi!
I struggle to make the offset method to change with the selected case.
anyone that can se what am doing wrong?

Sub kopi()

Dim x As Range
Dim y As Range
Dim Stilling
Dim m As Range



Stilling = Sheet1.Range("E3").Value

Select Case Stilling

Case "Senior Boresjef"
Set y = Sheet2.Range("B2:M2")
Set m = x.Offset(20, 0)

Case "Vedlikeholdsleder"
Set y = Sheet2.Range("B14:M14")
Set m = x.Offset(8, 0)

Case Else
MsgBox "Select a job description"

End Select



For Each x In y
If Not IsEmpty(x) Then
m.Resize(6, 1).Copy

Sheets("Skjema").Select
Run "lim"
End If

Next
End Sub
Sub lim()
Dim rngDestination As Range



Set rngDestination = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
rngDestination.Select


Selection.PasteSpecial Paste:=xlValue, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True


End Sub
 
M

Mike H

Hi,

You have a problem here with the second line

Set y = Sheet2.Range("B2:M2")
Set m = x.Offset(20, 0)

x has been declared as a range but not SET to an actual range and before you
set another range OFFSET from that the code has to know where X is.

What is x?
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
J

JLGWhiz

Set m = x.Offset(20, 0)

Set m = x.Offset(8, 0)

The two lines above are attempting to Offset from an object variable that
has not yet been defined. x is an empty object variable at this point in
the code.
 
A

Axel

Thank you for the explanation. Looks like i have to find another way to do
this.

x is the non empty cells in a range set by the y in the case statement.

If a user choose one of the cases, the offset method will copy range from a
database sheet, to the user sheet
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top