hi don thanks for your help but i wanted to ask you is there a way to do
the same task without loop because it will go to all the sheets that are
mention in the table but for some reason i want to do the same task without
loop for that matter i want something like this
it should go to every sheet one by one and select the range then next sheet
but not using loop
and thanks for letting me know about the cells references it was great
information for me and now i will definitely try make change in my existing
macro.
regards,
Avinash
On Friday, July 6, 2012 9:07:39 PM UTC+5:30, Don Guillett wrote:
>
> You could have looked in the vba help for cells
>
> cells(1,1) is the same as cells(1,”a”)
>
> cells(2,”M”)
> cells(2,”N”)
>
> Don Guillett
> Microsoft Excel Developer
> SalesAid Software
> dguille...@gmail.com
>
> *From:* Avinash <avinash007pa...@gmail.com>
> *Sent:* Friday, July 06, 2012 10:32 AM
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ help regarding range selection from
> worksheet
>
> hi don thanks for your help
> and sorry for i was not able to explain my requirement but can u please
> explain me how this code is working and what if the range table which is
> mentioned in A and B column is in M and N column???
>
> what will be the code???
>
> Regards,
> Avinash
>
>
>
> On Friday, July 6, 2012 8:19:26 PM UTC+5:30, Don Guillett wrote:
>>
>> Had you explained that you want to select the appropriate range in ALL
>> sheets I would have offered.
>>
>> Sub selectallSAS()
>> dim I as long
>> With Sheets("settings")
>> For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
>> Application.Goto Sheets(CStr(.Cells(i, 1))).Range(.Cells(i, 2))
>> Next i
>> End With
>> End Sub
>>
>> Don Guillett
>> Microsoft Excel Developer
>> SalesAid Software
>> dguille...@gmail.com
>>
>> *From:* Avinash <avinash007pa...@gmail.com>
>> *Sent:* Friday, July 06, 2012 9:20 AM
>> *To:* excel-macros@googlegroups.com
>> *Subject:* Re: $$Excel-Macros$$ help regarding range selection from
>> worksheet
>>
>> Please find attached Excel sheet and run my macro for better
>> understanding
>>
>> Regards,
>>
>> Avinash
>>
>> On Friday, July 6, 2012 7:22:45 PM UTC+5:30, Don Guillett wrote:
>>>
>>> If?? I understand what you want, right click sheet tab>view
>>> code>insert this>double click on sheet name in column A
>>>
>>> Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel
>>> As Boolean)
>>> If Target.Column <> 1 Then Exit Sub
>>> Application.Goto Sheets(CStr(Target)).Range(Target.Offset(, 1))
>>> End Sub
>>>
>>> Don Guillett
>>> Microsoft Excel Developer
>>> SalesAid Software
>>> dguille...@gmail.com
>>>
>>> *From:* Avinash <avinash007pa...@gmail.com>
>>> *Sent:* Friday, July 06, 2012 6:14 AM
>>> *To:* excel-macros@googlegroups.com
>>> *Subject:* Re: $$Excel-Macros$$ help regarding range selection from
>>> worksheet
>>>
>>> hi ashish there is little problem in this code when i am trying to use
>>> this code in to my macro it is givving me error
>>> subscript out of range
>>>
>>> because if my macro working on sheet2 it is not using range which i have
>>> mentioned in the settings sheet
>>>
>>> for that matter i have to define range in that perticular sheet like
>>>
>>> if my macro is working in settings sheet then it is working cuz defined
>>> ranges are in settings sheet but when it goes in to sheet1 then is not
>>> working
>>>
>>>
>>> Sheet Name Defined Range
>>> Sheet1 C7:BS37
>>> Sheet2 C8:BS37
>>> Sheet3 C9:BS37
>>> Sheet4 C10:BS37
>>>
>>>
>>> below is my macro
>>>
>>> and please find attached sample sheet
>>>
>>> Sub test()
>>>
>>> Sheets(Range("a2").Value).Range(Range("b2").Value).Select
>>>
>>> Sheets("Sheet2").Select
>>>
>>> Sheets(Range("a3").Value).Range(Range("b3").Value).Select
>>> 'Selection.FormulaR1C1 = "=VLOOKUP(RC1,Info!C1:C2,2,FALSE)"
>>>
>>> End Sub
>>>
>>>
>>>
>>> Regards,
>>> Avinash
>>>
>>>
>>>
>>> On Thursday, July 5, 2012 10:55:05 PM UTC+5:30, Avinash wrote:
>>>>
>>>> Awesome................!!!!
>>>>
>>>>
>>>> Jhakaaaaaaaaaassssssssssssssss.............!!!
>>>>
>>>>
>>>> Thank You So Much...!:)
>>>>
>>>>
>>>>
>>>>
>>>> On Thursday, July 5, 2012 10:51:16 PM UTC+5:30, ashish wrote:
>>>>>
>>>>> try something like this
>>>>>
>>>>>
>>>>> a2 = sheet name
>>>>> b2 = cell adress
>>>>>
>>>>>
>>>>> Sub test()
>>>>> Sheets(Range("a2").Value).Range(Range("b2").Value).Select
>>>>> End Sub
>>>>>
>>>>>
>>>>>
>>>>> On Thu, Jul 5, 2012 at 10:47 PM, Avinash <avinash007pa...@gmail.com>wrote:
>>>>>
>>>>>> Hi all,
>>>>>>
>>>>>> i want help regarding range selection from worksheet.
>>>>>>
>>>>>> i have created 1 macro to select specific range from sheet and for
>>>>>> that matter i have used below mentioned code
>>>>>>
>>>>>> Sheets("sheet1").Select
>>>>>> ActiveSheet.Range("C7:BS37").Select
>>>>>>
>>>>>>
>>>>>> my macro should work on range which i have mentioned in the worksheet
>>>>>> not in macro module because if the range gets change i have to make
>>>>>> correction in my macro code
>>>>>>
>>>>>> but instead of writing this code in to the macro i want something
>>>>>> like this
>>>>>>
>>>>>> all macro related things are there in the settings sheet and
>>>>>>
>>>>>> macro should capture like
>>>>>>
>>>>>>
>>>>>> *Sheet Name* *Defined Range* Sheet1 C7:BS37 Sheet2 C8:BS37 Sheet3
>>>>>> C9:BS37 Sheet4 C10:BS37
>>>>>>
>>>>>>
>>>>>> and it should use range from worksheet not from macro module i hope u
>>>>>> understand what i am trying to tell u guys.
>>>>>>
>>>>>> please help me on this
>>>>>>
>>>>>> Regards,
>>>>>>
>>>>>> Avinash
>>>>>>
>>>>>> --
>>>>>> FORUM RULES (986+ members already BANNED for violation)
>>>>>>
>>>>>> 1) Use concise, accurate thread titles. Poor thread titles, like
>>>>>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need
>>>>>> Advice will not get quick attention or may not be answered.
>>>>>>
>>>>>> 2) Don't post a question in the thread of another member.
>>>>>>
>>>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>>>> measure.
>>>>>>
>>>>>> 4) Acknowledge the responses you receive, good or bad.
>>>>>>
>>>>>> 5) Cross-promotion of, or links to, forums competitive to this forum
>>>>>> in signatures are prohibited.
>>>>>>
>>>>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>>>>> Forum owners and members are not responsible for any loss.
>>>>>>
>>>>>>
>>>>>> ------------------------------------------------------------------------------------------------------
>>>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>>>
>>>>>> To unsubscribe, send a blank email to
>>>>>> excel-macros+unsubscr...@googlegroups.com
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> *Regards*
>>>>> **
>>>>> *Ashish Koul*
>>>>> *http://www.excelvbamacros.com/*
>>>>> *http://www.accessvbamacros.com/* <http://www.accessvbamacros.com/>
>>>>>
>>>>>
>>>>> P Before printing, think about the environment.
>>>>>
>>>>>
>>>>>
>>>> --
>>> FORUM RULES (986+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.
>>>
>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.
>>>
>>>
>>> ------------------------------------------------------------------------------------------------------
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> To unsubscribe, send a blank email to
>>> excel-macros+unsubscr...@googlegroups.com
>>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> ------------------------------------------------------------------------------------------------------
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> To unsubscribe, send a blank email to
>> excel-macros+unsubscr...@googlegroups.com
>>
>
> On Friday, July 6, 2012 8:19:26 PM UTC+5:30, Don Guillett wrote:
>>
>> Had you explained that you want to select the appropriate range in ALL
>> sheets I would have offered.
>>
>> Sub selectallSAS()
>> dim I as long
>> With Sheets("settings")
>> For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
>> Application.Goto Sheets(CStr(.Cells(i, 1))).Range(.Cells(i, 2))
>> Next i
>> End With
>> End Sub
>>
>> Don Guillett
>> Microsoft Excel Developer
>> SalesAid Software
>> dguille...@gmail.com
>>
>> *From:* Avinash <avinash007pa...@gmail.com>
>> *Sent:* Friday, July 06, 2012 9:20 AM
>> *To:* excel-macros@googlegroups.com
>> *Subject:* Re: $$Excel-Macros$$ help regarding range selection from
>> worksheet
>>
>> Please find attached Excel sheet and run my macro for better
>> understanding
>>
>> Regards,
>>
>> Avinash
>>
>> On Friday, July 6, 2012 7:22:45 PM UTC+5:30, Don Guillett wrote:
>>>
>>> If?? I understand what you want, right click sheet tab>view
>>> code>insert this>double click on sheet name in column A
>>>
>>> Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel
>>> As Boolean)
>>> If Target.Column <> 1 Then Exit Sub
>>> Application.Goto Sheets(CStr(Target)).Range(Target.Offset(, 1))
>>> End Sub
>>>
>>> Don Guillett
>>> Microsoft Excel Developer
>>> SalesAid Software
>>> dguille...@gmail.com
>>>
>>> *From:* Avinash <avinash007pa...@gmail.com>
>>> *Sent:* Friday, July 06, 2012 6:14 AM
>>> *To:* excel-macros@googlegroups.com
>>> *Subject:* Re: $$Excel-Macros$$ help regarding range selection from
>>> worksheet
>>>
>>> hi ashish there is little problem in this code when i am trying to use
>>> this code in to my macro it is givving me error
>>> subscript out of range
>>>
>>> because if my macro working on sheet2 it is not using range which i have
>>> mentioned in the settings sheet
>>>
>>> for that matter i have to define range in that perticular sheet like
>>>
>>> if my macro is working in settings sheet then it is working cuz defined
>>> ranges are in settings sheet but when it goes in to sheet1 then is not
>>> working
>>>
>>>
>>> Sheet Name Defined Range
>>> Sheet1 C7:BS37
>>> Sheet2 C8:BS37
>>> Sheet3 C9:BS37
>>> Sheet4 C10:BS37
>>>
>>>
>>> below is my macro
>>>
>>> and please find attached sample sheet
>>>
>>> Sub test()
>>>
>>> Sheets(Range("a2").Value).Range(Range("b2").Value).Select
>>>
>>> Sheets("Sheet2").Select
>>>
>>> Sheets(Range("a3").Value).Range(Range("b3").Value).Select
>>> 'Selection.FormulaR1C1 = "=VLOOKUP(RC1,Info!C1:C2,2,FALSE)"
>>>
>>> End Sub
>>>
>>>
>>>
>>> Regards,
>>> Avinash
>>>
>>>
>>>
>>> On Thursday, July 5, 2012 10:55:05 PM UTC+5:30, Avinash wrote:
>>>>
>>>> Awesome................!!!!
>>>>
>>>>
>>>> Jhakaaaaaaaaaassssssssssssssss.............!!!
>>>>
>>>>
>>>> Thank You So Much...!:)
>>>>
>>>>
>>>>
>>>>
>>>> On Thursday, July 5, 2012 10:51:16 PM UTC+5:30, ashish wrote:
>>>>>
>>>>> try something like this
>>>>>
>>>>>
>>>>> a2 = sheet name
>>>>> b2 = cell adress
>>>>>
>>>>>
>>>>> Sub test()
>>>>> Sheets(Range("a2").Value).Range(Range("b2").Value).Select
>>>>> End Sub
>>>>>
>>>>>
>>>>>
>>>>> On Thu, Jul 5, 2012 at 10:47 PM, Avinash <avinash007pa...@gmail.com>wrote:
>>>>>
>>>>>> Hi all,
>>>>>>
>>>>>> i want help regarding range selection from worksheet.
>>>>>>
>>>>>> i have created 1 macro to select specific range from sheet and for
>>>>>> that matter i have used below mentioned code
>>>>>>
>>>>>> Sheets("sheet1").Select
>>>>>> ActiveSheet.Range("C7:BS37").Select
>>>>>>
>>>>>>
>>>>>> my macro should work on range which i have mentioned in the worksheet
>>>>>> not in macro module because if the range gets change i have to make
>>>>>> correction in my macro code
>>>>>>
>>>>>> but instead of writing this code in to the macro i want something
>>>>>> like this
>>>>>>
>>>>>> all macro related things are there in the settings sheet and
>>>>>>
>>>>>> macro should capture like
>>>>>>
>>>>>>
>>>>>> *Sheet Name* *Defined Range* Sheet1 C7:BS37 Sheet2 C8:BS37 Sheet3
>>>>>> C9:BS37 Sheet4 C10:BS37
>>>>>>
>>>>>>
>>>>>> and it should use range from worksheet not from macro module i hope u
>>>>>> understand what i am trying to tell u guys.
>>>>>>
>>>>>> please help me on this
>>>>>>
>>>>>> Regards,
>>>>>>
>>>>>> Avinash
>>>>>>
>>>>>> --
>>>>>> FORUM RULES (986+ members already BANNED for violation)
>>>>>>
>>>>>> 1) Use concise, accurate thread titles. Poor thread titles, like
>>>>>> Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need
>>>>>> Advice will not get quick attention or may not be answered.
>>>>>>
>>>>>> 2) Don't post a question in the thread of another member.
>>>>>>
>>>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>>>> measure.
>>>>>>
>>>>>> 4) Acknowledge the responses you receive, good or bad.
>>>>>>
>>>>>> 5) Cross-promotion of, or links to, forums competitive to this forum
>>>>>> in signatures are prohibited.
>>>>>>
>>>>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>>>>> Forum owners and members are not responsible for any loss.
>>>>>>
>>>>>>
>>>>>> ------------------------------------------------------------------------------------------------------
>>>>>> To post to this group, send email to excel-macros@googlegroups.com
>>>>>>
>>>>>> To unsubscribe, send a blank email to
>>>>>> excel-macros+unsubscr...@googlegroups.com
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> *Regards*
>>>>> **
>>>>> *Ashish Koul*
>>>>> *http://www.excelvbamacros.com/*
>>>>> *http://www.accessvbamacros.com/* <http://www.accessvbamacros.com/>
>>>>>
>>>>>
>>>>> P Before printing, think about the environment.
>>>>>
>>>>>
>>>>>
>>>> --
>>> FORUM RULES (986+ members already BANNED for violation)
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>>
>>> 2) Don't post a question in the thread of another member.
>>>
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>>
>>> 4) Acknowledge the responses you receive, good or bad.
>>>
>>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>>> signatures are prohibited.
>>>
>>> NOTE : Don't ever post personal or confidential data in a workbook.
>>> Forum owners and members are not responsible for any loss.
>>>
>>>
>>> ------------------------------------------------------------------------------------------------------
>>> To post to this group, send email to excel-macros@googlegroups.com
>>>
>>> To unsubscribe, send a blank email to
>>> excel-macros+unsubscr...@googlegroups.com
>>>
>> --
>> FORUM RULES (986+ members already BANNED for violation)
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>>
>> 2) Don't post a question in the thread of another member.
>>
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>>
>> 4) Acknowledge the responses you receive, good or bad.
>>
>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.
>>
>>
>> ------------------------------------------------------------------------------------------------------
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> To unsubscribe, send a blank email to
>> excel-macros+unsubscr...@googlegroups.com
>>
> --
> FORUM RULES (986+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> ------------------------------------------------------------------------------------------------------
> To post to this group, send email to excel-macros@googlegroups.com
>
> To unsubscribe, send a blank email to
> excel-macros+unsubscr...@googlegroups.com
>
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
