offset method problem
 

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
--
Copy & paste developer

offset method problem
 

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.


"Axel" wrote:

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
--
Copy & paste developer

offset method problem
 

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.




"Axel" wrote in message
...
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
--
Copy & paste developer

offset method problem
 

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

--
Copy & paste developer


"Mike H" wrote:

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.


"Axel" wrote:

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
--
Copy & paste developer

offset method problem
 

Solved this way
Set m = y.Offset(20, 0)

Set m = y.Offset(8, 0)

Thanks
--
Copy & paste developer


"JLGWhiz" wrote:

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.




"Axel" wrote in message
...
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
--
Copy & paste developer


.