Error

[VBA_Noob002]

Hi guys, I get an application defined/Object defined error for the below, any help is much appreciated!

Sub Macro1()
'
' Macro1 Macro
'


'


    For Y = 1 To 12
    For Z = 1 To 50
    For A = 1 To 50
    For B = 1 To 50
    X = Z + 3
    C = A + 4
    D = B + 5
    


    ThisWorkbook.Worksheets("XMR").Range(Cells(Y + 1, 4)).Select
    ActiveCell.FormulaR1C1 = "='[File.xlsx]SM'!R148C" & X
    ThisWorkbook.Worksheets("XMR").Range(Cells(Y + 1, 5)).Select
    ActiveCell.FormulaR1C1 = "='[File.xlsx]SM'!R148C" & C
    ThisWorkbook.Worksheets("XMR").Range(Cells(Y + 1, 6)).Select
    ActiveCell.FormulaR1C1 = "='[File.xlsx]SM'!R148C" & D
    
    Next B
    Next A
    Next Z
    Next Y


    
End Sub

 

[Stiuart_W]

.Worksheets should be .Sheets and "Range(Cells(Y + 1, 4))." is not correct as you aren't specifying a range

Are you trying to add a formula to a cell or a range?

 

[VBA_Noob002]

Hi Stuart,

Thank you for your reply!

I am basically trying to add a formula to a range, one cell at a time, so I guess I should probably just use Cels() and remove the Range().

 

[pgc01]

Hi
Welcome to the board

You cannot only use .Select in a cell in the activesheet

... and you don't usually use .Select at all in vba.

Try, for ex., instead of

ThisWorkbook.Worksheets("XMR").Range(Cells(Y + 1, 4)).Select
    ActiveCell.FormulaR1C1 = "='[File.xlsx]SM'!R148C" & X

this

ThisWorkbook.Worksheets("XMR").Cells(Y + 1, 4).FormulaR1C1 = "='[File.xlsx]SM'!R148C" & X

 

[Stiuart_W]

Try this.

Sub Macro1()
Application.ScreenUpdating = False
Application.Calculation = xlCalculateManual

    For Y = 1 To 12
        For Z = 1 To 50
            For A = 1 To 50
                For B = 1 To 50
                    X = Z + 3
                    C = A + 4
                    D = B + 5

                    ThisWorkbook.Sheets("XMR").Cells(Y + 1, 4).FormulaR1C1 = "='[File.xlsx]SM'!R148C" & X
                    ThisWorkbook.Worksheets("XMR").Cells(Y + 1, 5).FormulaR1C1 = "='[File.xlsx]SM'!R148C" & C
                    ThisWorkbook.Worksheets("XMR").Cells(Y + 1, 6).FormulaR1C1 = "='[File.xlsx]SM'!R148C" & D
    
                Next B
            Next A
        Next Z
    Next Y
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

But be WARNED - You have 4 loops which will take a long time to run - you have 50 x 50 x 50 x 12 loops. That is a LOT!

 

[VBA_Noob002]

Hi Stiuart, thank you very much for the code, but let me explain what I need to do.


I have one excel file with the following layout:

	Motorcycles	Bicycles	Cars
	Wheels	Machines	Cost	Wheels	Machines	Cost	Wheels	Machines	Cost
California	100	200	300	100	200	300	100	200	300
Alabama	200	300	400	200	300	400	200	300	400

<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>
</tbody>

And im trying to link the cells in the file with the below format to the other file using VBA, the code does not quite do that as it links all the cells in the second file with the “Cars” Cells

		Wheels	Machines	Cost
California	Wheels
California	Bicycles
California	Cars
Alabama	Wheels
Alabama	Bicycles
Alabama	Cars

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

Thank you very much again for your help!

 

[Stiuart_W]

So let me get this straight:-

1) So the first file always has States column + Motorcycles/Bicycles/Cars under each of which is Wheels/Machines/Cost Columns? Ie 10 columns in total

2) The first 2 rows are the headers as above. then rows of State data - Presumably there are 1 for each state ie 50?

3) You have a new blank file and you want to create the format you post (I assume you mean Motorcycles after California and Alabama and NOT wheels). So the top left number would be 100

 

[VBA_Noob002]

Hi Stiuart,

Exactly!