Error

VBA_Noob002

New Member
Joined
Mar 29, 2016
Messages
5
Hi guys, I get an application defined/Object defined error for the below, any help is much appreciated!
Code:
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
 
Last edited by a moderator:

Stiuart_W

Well-known Member
Joined
Jul 3, 2013
Messages
516
.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

New Member
Joined
Mar 29, 2016
Messages
5
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,755
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


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

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

Stiuart_W

Well-known Member
Joined
Jul 3, 2013
Messages
516
Try this.

Code:
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

New Member
Joined
Mar 29, 2016
Messages
5
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:

MotorcyclesBicyclesCars
WheelsMachinesCostWheelsMachinesCostWheelsMachinesCost
California100200300100200300100200300
Alabama200300400200300400200300400


<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

WheelsMachines Cost
CaliforniaWheels
CaliforniaBicycles
CaliforniaCars
AlabamaWheels
AlabamaBicycles
AlabamaCars

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


Thank you very much again for your help!
 

Stiuart_W

Well-known Member
Joined
Jul 3, 2013
Messages
516
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
 

Forum statistics

Threads
1,085,842
Messages
5,386,284
Members
401,992
Latest member
CleverHopper

Some videos you may like

This Week's Hot Topics

Top