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
[VBA]training_range_179.name = "training_range_179"
training_range_365.name = "training_range_365"[/VBA]


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