Getting a range to work in my vba code

[nathan2314]

Hey all,
how come this won't work? I'm trying to create a range and then set another range of cells equal to the created range values.
Here is my code


Dim training_range_179 as range
Dim training_range_365 as range
If Sheets("Long Term Input Data").Range("AB1") = "E6" Then
Set training_range_179 = Sheets("179 Training Requirements").Range("C92:Z92")
Set training_range_365 = Sheets("365 Training Requirements").Range("C92:Z92")
End If
training_range_179.name = "training_range_179"
training_range_365.name = "training_range_365"

Worksheets("179 Training Requirements").range("C4:Z4") = Range("training_range_179")


I thought I had done this same thing in some of my earlier vba coding but it doesn't work. So I must be missing something..
Help!!

[mikerickson]

What do you want that code to do?
What does it do (or not do) that displeases you?

[nathan2314]

I would like the code to set the values in the range 'worksheets("179 Training Requirements").range("C4:Z4")' to the values in "training_range_179" (which should be 'Sheets("179 Training Requirements").Range("C92:Z92")'). I'm doing a similar thing with "training_range_365" further down in the code. I get an error when running the code when it gets to the line
Worksheets("179 Training Requirements").range("C4:Z4") = Range("training_range_179")
The error says 'Run-time error '1004': Application-defined or object-defined error"
I'm a newbie at VBA so I'm sure i'm just missing something small but not sure what ??

[nathan2314]

Here is the full code in case you want to see

Private Sub ComboBox1_Change()
Dim training_range_179 As Range
Dim training_range_365 As Range

If Sheets("Long Term Input Data").Range("AB1") = "ALL" Then
Set training_range_179 = Sheets("179 Training Requirements").Range("C100:Z100")
Set training_range_365 = Sheets("365 Training Requirements").Range("C100:Z100")
End If

If Sheets("Long Term Input Data").Range("AB1") = "O2" Then
Set training_range_179 = Sheets("179 Training Requirements").Range("C12:Z12")
Set training_range_365 = Sheets("365 Training Requirements").Range("C12:Z12")
End If

If Sheets("Long Term Input Data").Range("AB1") = "O3" Then
Set training_range_179 = Sheets("179 Training Requirements").Range("C20:Z20")
Set training_range_365 = Sheets("365 Training Requirements").Range("C20:Z20")
End If

If Sheets("Long Term Input Data").Range("AB1") = "O4" Then
Set training_range_179 = Sheets("179 Training Requirements").Range("C28:Z28")
Set training_range_365 = Sheets("365 Training Requirements").Range("C28:Z28")
End If

If Sheets("Long Term Input Data").Range("AB1") = "O5" Then
Set training_range_179 = Sheets("179 Training Requirements").Range("C36:Z36")
Set training_range_365 = Sheets("365 Training Requirements").Range("C36:Z36")
End If

If Sheets("Long Term Input Data").Range("AB1") = "O6" Then
Set training_range_179 = Sheets("179 Training Requirements").Range("C44:Z44")
Set training_range_365 = Sheets("365 Training Requirements").Range("C44:Z44")
End If

If Sheets("Long Term Input Data").Range("AB1") = "E1" Then
Set training_range_179 = Sheets("179 Training Requirements").Range("C52:Z52")
Set training_range_365 = Sheets("365 Training Requirements").Range("C52:Z52")
End If

If Sheets("Long Term Input Data").Range("AB1") = "E2" Then
Set training_range_179 = Sheets("179 Training Requirements").Range("C60:Z60")
Set training_range_365 = Sheets("365 Training Requirements").Range("C60:Z60")
End If

If Sheets("Long Term Input Data").Range("AB1") = "E3" Then
Set training_range_179 = Sheets("179 Training Requirements").Range("C68:Z68")
Set training_range_365 = Sheets("365 Training Requirements").Range("C68:Z68")
End If

If Sheets("Long Term Input Data").Range("AB1") = "E4" Then
Set training_range_179 = Sheets("179 Training Requirements").Range("C76:Z76")
Set training_range_365 = Sheets("365 Training Requirements").Range("C76:Z76")
End If

If Sheets("Long Term Input Data").Range("AB1") = "E5" Then
Set training_range_179 = Sheets("179 Training Requirements").Range("C84:Z84")
Set training_range_365 = Sheets("365 Training Requirements").Range("C84:Z84")
End If

If Sheets("Long Term Input Data").Range("AB1") = "E6" Then
Set training_range_179 = Sheets("179 Training Requirements").Range("C92:Z92")
Set training_range_365 = Sheets("365 Training Requirements").Range("C92:Z92")
End If
training_range_179.name = "training_range_179"
training_range_365.name = "training_range_365"

Worksheets("179 Training Requirements").Range("C4:Z4") = Range("training_range_179")
Worksheets("365 Training Requirements").Range("C4:Z4") = Range("training_range_365")
End Sub

[mikerickson]

There are two things I see.
1) If none of the conditions are met, training_range_179 is Nothing, which would cause problems.

2) If the posted code was copy/pasted from VB editor, I would expect Name to be capitalized in
training_range_179.name = "training_range_179"
training_range_365.name = "training_range_365"


I can't see a specific cause for your problem.