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:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
.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?
 
Upvote 0
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().
 
Upvote 0
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:
Upvote 0
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!
 
Upvote 0
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:

[TABLE="width: 640"]
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="class: xl63, width: 192, colspan: 3"]Motorcycles[/TD]
[TD="class: xl63, width: 192, colspan: 3"]Bicycles[/TD]
[TD="class: xl63, width: 192, colspan: 3"]Cars[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Wheels[/TD]
[TD]Machines[/TD]
[TD]Cost[/TD]
[TD]Wheels[/TD]
[TD]Machines[/TD]
[TD]Cost[/TD]
[TD]Wheels[/TD]
[TD]Machines[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]California[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD]Alabama[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]400

[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Wheels[/TD]
[TD="width: 64"]Machines [/TD]
[TD="width: 64"]Cost[/TD]
[/TR]
[TR]
[TD]California[/TD]
[TD]Wheels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]California[/TD]
[TD]Bicycles[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]California[/TD]
[TD]Cars[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alabama[/TD]
[TD]Wheels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alabama[/TD]
[TD]Bicycles[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alabama[/TD]
[TD]Cars[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you very much again for your help!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top