Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error '9':subscript out of range
Hi, I am realativley new user of VBA and am having trouble getting cells on one page equal to the particular cells in the other page. I previosuly had the code set up for cells on the same page equaling each other and the code was running fine. I now get a Run time Error '9':sbscript out of range I have checked and made sure that the page name is correct however am still getting this error. Any help would be fantastic Sub herdparameters() For i = 1 To 20 Sheets("With hmodel")(8, 3).Value = Sheets("With herd").Select(46 + i, 3).Value Sheets("With hmodel")(12, 3).Value = Sheets("With herd")(46 + i, 23).Value Sheets("with hmodel")(13, 3).Value = Sheets("with herd")(46 + i, 29).Value Sheets("with hmodel")(11, 6).Value = Sheets("with herd")(46 + i, 15).Value Sheets("with hmodel")(12, 6).Value = Sheets("with herd")(46 + i, 10).Value Calculate Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error '9':subscript out of range
With or with ?? -- Gary''s Student - gsnu200858 "meggie" wrote: Hi, I am realativley new user of VBA and am having trouble getting cells on one page equal to the particular cells in the other page. I previosuly had the code set up for cells on the same page equaling each other and the code was running fine. I now get a Run time Error '9':sbscript out of range I have checked and made sure that the page name is correct however am still getting this error. Any help would be fantastic Sub herdparameters() For i = 1 To 20 Sheets("With hmodel")(8, 3).Value = Sheets("With herd").Select(46 + i, 3).Value Sheets("With hmodel")(12, 3).Value = Sheets("With herd")(46 + i, 23).Value Sheets("with hmodel")(13, 3).Value = Sheets("with herd")(46 + i, 29).Value Sheets("with hmodel")(11, 6).Value = Sheets("with herd")(46 + i, 15).Value Sheets("with hmodel")(12, 6).Value = Sheets("with herd")(46 + i, 10).Value Calculate Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error '9':subscript out of range
hi not sure what you are trying to do but it looks like you have syntax problems. maybe this would work better. Sub herdparameters() For i = 1 To 20 Sheets("with hmodel").Cells(8, 3).Value = Sheets("with herd").Cells(46 + i, 3).Value Sheets("With hmodel").Cells(12, 3).Value = Sheets("With herd").Cells(46 + i, 23).Value Sheets("with hmodel").Cells(13, 3).Value = Sheets("with herd").Cells(46 + i, 29).Value Sheets("with hmodel").Cells(11, 6).Value = Sheets("with herd").Cells(46 + i, 15).Value Sheets("with hmodel").Cells(12, 6).Value = Sheets("with herd").Cells(46 + i, 10).Value Calculate Next End Sub regards FSt1 "meggie" wrote: Hi, I am realativley new user of VBA and am having trouble getting cells on one page equal to the particular cells in the other page. I previosuly had the code set up for cells on the same page equaling each other and the code was running fine. I now get a Run time Error '9':sbscript out of range I have checked and made sure that the page name is correct however am still getting this error. Any help would be fantastic Sub herdparameters() For i = 1 To 20 Sheets("With hmodel")(8, 3).Value = Sheets("With herd").Select(46 + i, 3).Value Sheets("With hmodel")(12, 3).Value = Sheets("With herd")(46 + i, 23).Value Sheets("with hmodel")(13, 3).Value = Sheets("with herd")(46 + i, 29).Value Sheets("with hmodel")(11, 6).Value = Sheets("with herd")(46 + i, 15).Value Sheets("with hmodel")(12, 6).Value = Sheets("with herd")(46 + i, 10).Value Calculate Next End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error '9':subscript out of range
Try the below.. Sub herdparameters() Dim ws1 As Worksheet Dim ws2 As Worksheet Set ws1 = Sheets("With hmodel") Set ws2 = Sheets("With herd") For i = 1 To 20 ws1.Cells(8, 3).Value = ws2.Cells(46 + i, 3).Value ws1.Cells(12, 3).Value = ws2.Cells(46 + i, 23).Value ws1.Cells(13, 3).Value = ws2.Cells(46 + i, 29).Value ws1.Cells(11, 6).Value = ws2.Cells(46 + i, 15).Value ws1.Cells(12, 6).Value = ws2.Cells(46 + i, 10).Value Calculate Next End Sub -- If this post helps click Yes --------------- Jacob Skaria "meggie" wrote: Hi, I am realativley new user of VBA and am having trouble getting cells on one page equal to the particular cells in the other page. I previosuly had the code set up for cells on the same page equaling each other and the code was running fine. I now get a Run time Error '9':sbscript out of range I have checked and made sure that the page name is correct however am still getting this error. Any help would be fantastic Sub herdparameters() For i = 1 To 20 Sheets("With hmodel")(8, 3).Value = Sheets("With herd").Select(46 + i, 3).Value Sheets("With hmodel")(12, 3).Value = Sheets("With herd")(46 + i, 23).Value Sheets("with hmodel")(13, 3).Value = Sheets("with herd")(46 + i, 29).Value Sheets("with hmodel")(11, 6).Value = Sheets("with herd")(46 + i, 15).Value Sheets("with hmodel")(12, 6).Value = Sheets("with herd")(46 + i, 10).Value Calculate Next End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error '9':subscript out of range
You should copy the code from the VBE and paste into your message. The code you posted won't work. Sheets("With hmodel")(8, 3).Value = Sheets("With herd").Select(46 + i, 3).Value Is not correct. maybe you meant: Sheets("With hmodel").cells(8, 3).Value _ = Sheets("With herd").cells(46 + i, 3).Value But that doesn't explain the subscript error. I'm guessing that one (or more) of the sheet name(s) is spelled incorrectly. Or the wrong workbook is active???? meggie wrote: Hi, I am realativley new user of VBA and am having trouble getting cells on one page equal to the particular cells in the other page. I previosuly had the code set up for cells on the same page equaling each other and the code was running fine. I now get a Run time Error '9':sbscript out of range I have checked and made sure that the page name is correct however am still getting this error. Any help would be fantastic Sub herdparameters() For i = 1 To 20 Sheets("With hmodel")(8, 3).Value = Sheets("With herd").Select(46 + i, 3).Value Sheets("With hmodel")(12, 3).Value = Sheets("With herd")(46 + i, 23).Value Sheets("with hmodel")(13, 3).Value = Sheets("with herd")(46 + i, 29).Value Sheets("with hmodel")(11, 6).Value = Sheets("with herd")(46 + i, 15).Value Sheets("with hmodel")(12, 6).Value = Sheets("with herd")(46 + i, 10).Value Calculate Next End Sub -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error '9':subscript out of range
Upper/lower case won't matter in this case. Gary''s Student wrote: With or with ?? -- Gary''s Student - gsnu200858 "meggie" wrote: Hi, I am realativley new user of VBA and am having trouble getting cells on one page equal to the particular cells in the other page. I previosuly had the code set up for cells on the same page equaling each other and the code was running fine. I now get a Run time Error '9':sbscript out of range I have checked and made sure that the page name is correct however am still getting this error. Any help would be fantastic Sub herdparameters() For i = 1 To 20 Sheets("With hmodel")(8, 3).Value = Sheets("With herd").Select(46 + i, 3).Value Sheets("With hmodel")(12, 3).Value = Sheets("With herd")(46 + i, 23).Value Sheets("with hmodel")(13, 3).Value = Sheets("with herd")(46 + i, 29).Value Sheets("with hmodel")(11, 6).Value = Sheets("with herd")(46 + i, 15).Value Sheets("with hmodel")(12, 6).Value = Sheets("with herd")(46 + i, 10).Value Calculate Next End Sub -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error '9':subscript out of range
Thanks Jacob that solves my problem perfectly. Thanks Again "Jacob Skaria" wrote: Try the below.. Sub herdparameters() Dim ws1 As Worksheet Dim ws2 As Worksheet Set ws1 = Sheets("With hmodel") Set ws2 = Sheets("With herd") For i = 1 To 20 ws1.Cells(8, 3).Value = ws2.Cells(46 + i, 3).Value ws1.Cells(12, 3).Value = ws2.Cells(46 + i, 23).Value ws1.Cells(13, 3).Value = ws2.Cells(46 + i, 29).Value ws1.Cells(11, 6).Value = ws2.Cells(46 + i, 15).Value ws1.Cells(12, 6).Value = ws2.Cells(46 + i, 10).Value Calculate Next End Sub -- If this post helps click Yes --------------- Jacob Skaria "meggie" wrote: Hi, I am realativley new user of VBA and am having trouble getting cells on one page equal to the particular cells in the other page. I previosuly had the code set up for cells on the same page equaling each other and the code was running fine. I now get a Run time Error '9':sbscript out of range I have checked and made sure that the page name is correct however am still getting this error. Any help would be fantastic Sub herdparameters() For i = 1 To 20 Sheets("With hmodel")(8, 3).Value = Sheets("With herd").Select(46 + i, 3).Value Sheets("With hmodel")(12, 3).Value = Sheets("With herd")(46 + i, 23).Value Sheets("with hmodel")(13, 3).Value = Sheets("with herd")(46 + i, 29).Value Sheets("with hmodel")(11, 6).Value = Sheets("with herd")(46 + i, 15).Value Sheets("with hmodel")(12, 6).Value = Sheets("with herd")(46 + i, 10).Value Calculate Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run-time error '9': subscript out of range | Excel Discussion (Misc queries) | |||
Run-time error '9': Subscript out of range | Excel Programming | |||
Run-time Error 9: Subscript out of range | Excel Programming | |||
Run time error-subscript out of range | Excel Programming | |||
Run time error 9 : Subscript out of range | Excel Discussion (Misc queries) |