$$Excel-Macros$$ Re: Need help on syntax
Try the link below: http://www.ozgrid.com/News/excel-validation-tips.htm Norman On Thu, May 28, 2009 at 12:09 PM, Ahmet Yalcin ahmetyalc...@gmail.comwrote: Hello Paul, Thank you. What I am trying is to gather data from same address on many sheets in my workbook. The name of the file is 2008-04 BIL.xls and within I have sheets called KM, KS and KD (of course this is just a representative picture of a larger project). Can you kindly suggest what I should do? Also thank you for pointing out for starting with 0 declaring an array. Thanks again Ahmet 2009/5/28 Paul Schreiner schreiner_p...@att.net you're trying to insert the sheet name??? I assume the . . . is including code that is selecting a different cell. so the ActiveCell for loop x = 1 is different than the Active Cell when x = 3. try recording a macro. Also, keep in mind that when you declare an array, the indexes start at 0, not 1. so, Dim Company(3) has values in Company(0), Company(1) and Company(2). there IS no Company(3) Next + means to ADD two values. what you mean to do is to concatenate two strings using try: Sub test() Dim Company(4), x Company(1) = KM Company(2) = KS Company(3) = KD For x = 1 To 3 Cells(x, 1).Select ActiveCell.FormulaR1C1 = ='[2008-04 BIL.xls] Company(x) '!R7C4 Next x End Sub Paul -- *From:* Ahmet Yalcin ahmetyalc...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Thursday, May 28, 2009 10:19:08 AM *Subject:* $$Excel-Macros$$ Need help on syntax Hello, Please help me with the right syntax writing the below ActiveCell.FormulaR1C1 = ='[2008-04 BIL.xls]+Company(x)'!D7 line. I wish to insert the Sheet name as I did (Company(x) ) but keep on getting an error. Thank you in advance Regards Ahmet - Dim Company(3) Company(1) = KM Company(2) = KS Company(3) = KD For x= 1 to 3 . . . ActiveCell.FormulaR1C1 = ='[2008-04 BIL.xls]+Company(x)'!D7 . . . Next x --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Need help on syntax
I'm not sure if this will work. Try replacing the + with Norman On Thu, May 28, 2009 at 10:19 AM, Ahmet Yalcin ahmetyalc...@gmail.comwrote: Hello, Please help me with the right syntax writing the below ActiveCell.FormulaR1C1 = ='[2008-04 BIL.xls]+Company(x)'!D7 line. I wish to insert the Sheet name as I did (Company(x) ) but keep on getting an error. Thank you in advance Regards Ahmet - Dim Company(3) Company(1) = KM Company(2) = KS Company(3) = KD For x= 1 to 3 . . . ActiveCell.FormulaR1C1 = ='[2008-04 BIL.xls]+Company(x)'!D7 . . . Next x --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Need help on syntax
you're trying to insert the sheet name??? I assume the . . . is including code that is selecting a different cell. so the ActiveCell for loop x = 1 is different than the Active Cell when x = 3. try recording a macro. Also, keep in mind that when you declare an array, the indexes start at 0, not 1. so, Dim Company(3) has values in Company(0), Company(1) and Company(2). there IS no Company(3) Next + means to ADD two values. what you mean to do is to concatenate two strings using try: Sub test() Dim Company(4), x Company(1) = KM Company(2) = KS Company(3) = KD For x = 1 To 3 Cells(x, 1).Select ActiveCell.FormulaR1C1 = ='[2008-04 BIL.xls] Company(x) '!R7C4 Next x End Sub Paul From: Ahmet Yalcin ahmetyalc...@gmail.com To: excel-macros@googlegroups.com Sent: Thursday, May 28, 2009 10:19:08 AM Subject: $$Excel-Macros$$ Need help on syntax Hello, Please help me with the right syntax writing the below ActiveCell.FormulaR1C1 = ='[2008-04 BIL.xls]+Company(x)'!D7 line. I wish to insert the Sheet name as I did (Company(x) ) but keep on getting an error. Thank you in advance Regards Ahmet - Dim Company(3) Company(1) = KM Company(2) = KS Company(3) = KD For x= 1 to 3 . . . ActiveCell.FormulaR1C1 = ='[2008-04 BIL.xls]+Company(x)'!D7 . . . Next x --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Need help on syntax
Hello Paul, Thank you. What I am trying is to gather data from same address on many sheets in my workbook. The name of the file is 2008-04 BIL.xls and within I have sheets called KM, KS and KD (of course this is just a representative picture of a larger project). Can you kindly suggest what I should do? Also thank you for pointing out for starting with 0 declaring an array. Thanks again Ahmet 2009/5/28 Paul Schreiner schreiner_p...@att.net you're trying to insert the sheet name??? I assume the . . . is including code that is selecting a different cell. so the ActiveCell for loop x = 1 is different than the Active Cell when x = 3. try recording a macro. Also, keep in mind that when you declare an array, the indexes start at 0, not 1. so, Dim Company(3) has values in Company(0), Company(1) and Company(2). there IS no Company(3) Next + means to ADD two values. what you mean to do is to concatenate two strings using try: Sub test() Dim Company(4), x Company(1) = KM Company(2) = KS Company(3) = KD For x = 1 To 3 Cells(x, 1).Select ActiveCell.FormulaR1C1 = ='[2008-04 BIL.xls] Company(x) '!R7C4 Next x End Sub Paul -- *From:* Ahmet Yalcin ahmetyalc...@gmail.com *To:* excel-macros@googlegroups.com *Sent:* Thursday, May 28, 2009 10:19:08 AM *Subject:* $$Excel-Macros$$ Need help on syntax Hello, Please help me with the right syntax writing the below ActiveCell.FormulaR1C1 = ='[2008-04 BIL.xls]+Company(x)'!D7 line. I wish to insert the Sheet name as I did (Company(x) ) but keep on getting an error. Thank you in advance Regards Ahmet - Dim Company(3) Company(1) = KM Company(2) = KS Company(3) = KD For x= 1 to 3 . . . ActiveCell.FormulaR1C1 = ='[2008-04 BIL.xls]+Company(x)'!D7 . . . Next x --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---