Refining my code?

[danbates77]

Hi,

Please can someone help me?

I have 7 palletisers at work (G,H,J,K,L,M & N) and each palletiser has 40 programs (1-40).

I would like to have a sheet (DATA) where I can compare up to 10 programs side by side (C1:L1 for the palletiser letter and C2:L2 for palletiser number) at any one time from any palletiser.

I have created my sheet and I have coded 1 palletiser so far but the way I know how to do it means I have 40 lines of code per palletiser.

I was wondering if someone could take a look and see if you know how to shorten it?

Sub Check_Palletiser_Program_Number()

Application.EnableEvents = False

If Range("C1") = "" Or Range("C2") = "" Then Range("C4:C37").ClearContents

If Range("C1") = "G" And Range("C2") = "1" Then Range("C4:C37").Value = Sheets("G").Range("C4:C37").Value
If Range("C1") = "G" And Range("C2") = "2" Then Range("C4:C37").Value = Sheets("G").Range("D3:D37").Value
If Range("C1") = "G" And Range("C2") = "3" Then Range("C4:C37").Value = Sheets("G").Range("E4:E37").Value
If Range("C1") = "G" And Range("C2") = "4" Then Range("C4:C37").Value = Sheets("G").Range("F4:F37").Value
If Range("C1") = "G" And Range("C2") = "5" Then Range("C4:C37").Value = Sheets("G").Range("G4:G37").Value
If Range("C1") = "G" And Range("C2") = "6" Then Range("C4:C37").Value = Sheets("G").Range("H4:H37").Value
If Range("C1") = "G" And Range("C2") = "7" Then Range("C4:C37").Value = Sheets("G").Range("I4:I37").Value
If Range("C1") = "G" And Range("C2") = "8" Then Range("C4:C21").Value = Sheets("G").Range("J4:J37").Value
If Range("C1") = "G" And Range("C2") = "9" Then Range("C4:C21").Value = Sheets("G").Range("K4:K37").Value
If Range("C1") = "G" And Range("C2") = "10" Then Range("C4:C21").Value = Sheets("G").Range("L4:L37").Value
If Range("C1") = "G" And Range("C2") = "11" Then Range("C4:C21").Value = Sheets("G").Range("M4:M37").Value
If Range("C1") = "G" And Range("C2") = "12" Then Range("C4:C21").Value = Sheets("G").Range("N4:N37").Value
If Range("C1") = "G" And Range("C2") = "13" Then Range("C4:C21").Value = Sheets("G").Range("O4:O37").Value
If Range("C1") = "G" And Range("C2") = "14" Then Range("C4:C21").Value = Sheets("G").Range("P4:P37").Value
If Range("C1") = "G" And Range("C2") = "15" Then Range("C4:C21").Value = Sheets("G").Range("Q4:Q37").Value
If Range("C1") = "G" And Range("C2") = "16" Then Range("C4:C21").Value = Sheets("G").Range("R4:R37").Value
If Range("C1") = "G" And Range("C2") = "17" Then Range("C4:C21").Value = Sheets("G").Range("S4:S37").Value
If Range("C1") = "G" And Range("C2") = "18" Then Range("C4:C21").Value = Sheets("G").Range("T4:T37").Value
If Range("C1") = "G" And Range("C2") = "19" Then Range("C4:C21").Value = Sheets("G").Range("U4:U37").Value
If Range("C1") = "G" And Range("C2") = "20" Then Range("C4:C21").Value = Sheets("G").Range("V4:V37").Value
If Range("C1") = "G" And Range("C2") = "21" Then Range("C4:C21").Value = Sheets("G").Range("W4:W37").Value
If Range("C1") = "G" And Range("C2") = "22" Then Range("C4:C21").Value = Sheets("G").Range("X4:X37").Value
If Range("C1") = "G" And Range("C2") = "23" Then Range("C4:C21").Value = Sheets("G").Range("Y4:Y37").Value
If Range("C1") = "G" And Range("C2") = "24" Then Range("C4:C21").Value = Sheets("G").Range("Z4:Z37").Value
If Range("C1") = "G" And Range("C2") = "25" Then Range("C4:C21").Value = Sheets("G").Range("AA4:AA37").Value
If Range("C1") = "G" And Range("C2") = "26" Then Range("C4:C21").Value = Sheets("G").Range("AB4:AB37").Value
If Range("C1") = "G" And Range("C2") = "27" Then Range("C4:C21").Value = Sheets("G").Range("AC4:AC37").Value
If Range("C1") = "G" And Range("C2") = "28" Then Range("C4:C21").Value = Sheets("G").Range("AD4:AD37").Value
If Range("C1") = "G" And Range("C2") = "29" Then Range("C4:C21").Value = Sheets("G").Range("AE4:AE37").Value
If Range("C1") = "G" And Range("C2") = "30" Then Range("C4:C21").Value = Sheets("G").Range("AF4:AF37").Value
If Range("C1") = "G" And Range("C2") = "31" Then Range("C4:C21").Value = Sheets("G").Range("AG4:AG37").Value
If Range("C1") = "G" And Range("C2") = "32" Then Range("C4:C21").Value = Sheets("G").Range("AH4:AH37").Value
If Range("C1") = "G" And Range("C2") = "33" Then Range("C4:C21").Value = Sheets("G").Range("AI4:AI37").Value
If Range("C1") = "G" And Range("C2") = "34" Then Range("C4:C21").Value = Sheets("G").Range("AJ4:AJ37").Value
If Range("C1") = "G" And Range("C2") = "35" Then Range("C4:C21").Value = Sheets("G").Range("AK4:AK37").Value
If Range("C1") = "G" And Range("C2") = "36" Then Range("C4:C21").Value = Sheets("G").Range("AL4:AL37").Value
If Range("C1") = "G" And Range("C2") = "37" Then Range("C4:C21").Value = Sheets("G").Range("AM4:AM37").Value
If Range("C1") = "G" And Range("C2") = "38" Then Range("C4:C21").Value = Sheets("G").Range("AN4:AN37").Value
If Range("C1") = "G" And Range("C2") = "39" Then Range("C4:C21").Value = Sheets("G").Range("AO4:AO37").Value
If Range("C1") = "G" And Range("C2") = "40" Then Range("C4:C21").Value = Sheets("G").Range("AP4:AP37").Value

Application.EnableEvents = True

End Sub

Hope this makes sense and any help would be appreciated

Thanks

Dan

 

[Joe4]

Is the part in red below a typo?

If Range("C1") = "G" And Range("C2") = "1" Then Range("C4:C37").Value = Sheets("G").Range("C4:C37").Value
If Range("C1") = "G" And Range("C2") = "2" Then Range("C4:C37").Value = Sheets("G").Range("D3:D37").Value
If Range("C1") = "G" And Range("C2") = "3" Then Range("C4:C37").Value = Sheets("G").Range("E4:E37").Value

It looks like every row in your code starts with row 4, except this one line.

 

[Joe4]

Assuming that the thing I mentioned above is just a typo, and the values of 1-40 in cell C2 are actually entered as numbers and not text, then I think you should be able to replace those 40 lines of code with this simple loop:

Dim i As Long
For i = 1 To 40
    If Range("C1") = "G" And Range("C2") = i Then Range("C4:C37").Value = Sheets("G").Range(Cells(4, i + 2), Cells(37, i + 2)).Value
Next i

 

[danbates77]

Hi Joe4,

Yes it was a typo and good spot by you.

I've tried your code but I'm getting the following error

run time error 1004
application defined or object defined error

and it highlights this part

Range("C4:C37").Value = Sheets("G").Range(Cells(4, i + 2), Cells(37, i + 2)).Value

any ideas?

thanks again
Dan

 

[Joe4]

Do the code work before the way you had it? I thought it looked a little funny. VBA usually does not like setting a multi-cell range to another multi-cell range.
I think you may be better off with a copy/paste.

I think we can also simplify things to get rid of the loop. See if this does what you need:

    If (Range("C1") = "G") And (Range("C2").Value >= 1) And (Range("C2").Value <= 40) Then
        Sheets("G").Range(Cells(4, i + 2), Cells(37, i + 2)).Copy Range("C4")
    End If

 

[danbates77]

Hi,

It still errors and highlights the same area of code.

I've just tried my code again and it works.

Could blanks cells within the range on sheet G be a problem?

thanks again
Dan

 

[Joe4]

What exactly is in cells C1 and C2 in this example?

Can you enter this formula anywhere on the main sheet, and tell me what it returns?

=ISNUMBER(C2)

Also, do you have any errors in any of the cells on Sheet G?
Or do you have any merged cells on either sheet in the ranges you are copying from or pasting into?

 

[danbates77]

They both have data validation set in them.

C1 with all the palletiser letters & C2 with the program numbers.

That formula returns - FALSE

 

[Joe4]

That formula returns - FALSE

OK, that tells me that the values in cell C2 are actually entered as Text, and not numbers.
Is there any reason why you set it up this way, if the cell should just contain numbers 1-40?

We have a few options here.
One would be to fix it so cell C2 contains valid entered numeric values instead of "numbers entered as text".
Or we could try to coerce it to a number in the VBA code, i.e. something like this:

    Dim i As Long
    i = Range("C2") + 0
    If (Range("C1") = "G") And (i >= 1) And (i <= 40) Then
        Sheets("G").Range(Cells(4, i + 2), Cells(37, i + 2)).Copy Range("C4")
    End If

 

[danbates77]

No particular reason other than I find it easier, I can remove it if it helps?

This code doesn't error but it doesn't enter that data either.