How I can find string in excel with vba?

Vba

• QUESTION: I am using2007 ThroughVisual Basic I need to go to http://www.google.com/finance/historical?q=AAPL&startdate=Nov+18%2C+2008&enddate=Nov+19%2C+2009&num=30 but I would like the link to change based on the text of specific cells. Notice in the link above there are two dates and a ticker symbol "AAPL." So for example if I want the data on the website to begin with June 12 2000 instead of November 18th 2008 (see these text values in the link above), I would like to type in these values in designated cells on my spreadsheet and have jun replace nov, 12 replace 18 and 2000 replace 2008 in the link above. I need these 3 items to change for the start and end dates (the two dates seen in the link above) and also I would like to be able to do this for any ticker so I would want to type in a ticker of any company (in a designated cell) and it would replace the text "AAPL" (Apple) in the above link. Once the link is prepared I would like to open the link. - I am kind of new at this and I certainly do not know about web navigation or actions through VBA. Thus far I can open the web page with: Sub DoBrowse1() Dim TargetFrame As String Dim IE As Object Set IE = CreateObject("Internetexplorer.Application") IE.Visible = True IE.Navigate "http://www.google.com/finance/historical?q=AAPL&startdate=Nov+18%2C+2008&enddate=Nov+19%2C+2009&num=30" End Sub This seems to only work when I already have a browser open. The point is, I simply want the link to be altered by input into specific cells before opening the link. This seems like it should be so simple but I am very unfamiliar with the program. Thanks a Ton ANSWER: Example: say the value "AAPL" is in cell A1 of the tab named Sheet1. Within your code, you'd have Dim strSymbol as String, strURL as String strSymbol = ThisWorkbook.Sheets("Sheet1").Range("A1").value strURL = "http://www.google.com/finance/historical?q=" & strSymbol & _ "&startdate=Nov+18%2C+2008&enddate=Nov+19%2C+2009&num=30" IE.Navigate strURL Any other variables you want to have within the URL (the start and end date etc) are handled the same way. ---------- FOLLOW-UP ---------- QUESTION: The code kept on giving me an error, highlighting IE.Navigate strURL I was searching around online and I tried placing this: Set IE = CreateObject("InternetExplorer.Application") before the error line and it seems to work. Should I need this? Is this proper or is it not correct? Should it work without this?? If I have a link typed into a cell as text, how can I automate the link being opened in a webbrowser using VBA? I am trying to use VBA to loop through a list of hyperlinks (which link to a worksheets), and the copy and paste what is in those sheets into excel. Any ideas? This is my code but it keeps on falling over at this stage Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True and only does the 1st copy and paste. Sub Macro2() Dim i As Integer 'Select cell A5, *first line of data*. Range("A5").Select ' Set Do loop to stop when an empty cell is reached. Do Until IsEmpty(ActiveCell) Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Windows("CMA Build.xls").Activate Range("I3").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _  :=False, Transpose:=False ' Step down 1 row from present location. i = i = 1 Loop End Sub Appreciate your help I am trying to use VBA to loop through a list of hyperlinks (which link to a worksheets), and the copy and paste what is in those sheets into excel. Any ideas? This is my code but it keeps on falling over at this stage Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True and only does the 1st copy and paste. Sub Macro2() Dim i As Integer 'Select cell A5, *first line of data*. Range("A5").Select ' Set Do loop to stop when an empty cell is reached. Do Until IsEmpty(ActiveCell) Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Windows("CMA Build.xls").Activate Range("I3").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _  :=False, Transpose:=False ' Step down 1 row from present location. i = i = 1 Loop End Sub Appreciate your help. Rume QUESTION: I'm trying to make a macro that copies a list from one sheet to another ONLY if none of the cells have equal values. I already have a cell telling me whether there are any duplicates in the list, but how can I reference this in a macro so that the list only copies when this particular cell = 0? (I've called that cell "my cell" for this explanation) Currently, the macro (shortened) is Sub validate() Let n = 0 Do Until n = 1 If = 0 Then Let n = 1 Else: Calculate End If Exit Do Loop End Sub The list involves the RAND function, so if is not 0, then the list should recalculate and then the macro should restart, but it doesn't. How do I fix the loop? ANSWER: Phil I don't quite understand what you are doing. Apparently the Rand function causes the list to change? Is this a one shot deal; you look at the list, if there are no dupes you copy the whole list and you are done? But, if there are dupes you quit? If that is the case is seems as though you could just nest you existing code in an IF THEN structure that runs if mycell indicates no dupes and exits if mycell indicates dupes. Explain it a little more and I will consider it again. ---------- FOLLOW-UP ---------- QUESTION: I don't want the macro to exit until the copy completes, ie I need it to recalculate the randomly generated cells and recheck until there are no duplicates, then copy and paste the list. eg. There is one pair of duplicates in the list. The macro will check the value of mycell and find that it is not 0, so it recalculates (causing a new list to be generated). There are no duplicates in the new list, so the macro copies it and pastes it in the desired location. Is there any way to do this? ANSWER: Phil When does it stop? Can you explain what you are trying to accomplish, big picture? Maybe there is a more direct approach? ---------- FOLLOW-UP ---------- QUESTION: It only stops after a single, valid list has been copied. Basically, the list simulates packs of cards. The 15 cards in the pack are taken randomly from a list of 250, but none of the cards in the pack should be duplicates. I can't think of any other way to approach this, it is going to need to revolve around an if statement, but my attempt to get the statement to loop if the condition is not met doesn't work. QUESTION: I have the following code Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 4 And Target.Row > 7 Then If LCase(Cells(Target.Row - 0, 7).Value) <> "yes" And LCase(Cells(Target.Row - 0, 7).Value) <> "no" Then MsgBox "Must Enter Yes or No" Cells(Target.Row - 0, 7).Activate End If End If End Sub I don't want the user to be able to proceed unless they enter yes or no. What do I need to do? ANSWER: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 4 And Target.Row > 7 Then If LCase(Cells(Target.Row - 0, 7).Value) <> "yes" And LCase(Cells(Target.Row - 0, 7).Value) <> "no" Then holdvar="" while lcase(holdvar)<>"yes" and lcase(holdvar)<>"no" holdvar=inputbox("Yes or No") wend End If End If End Sub ---------- FOLLOW-UP ---------- QUESTION: I think this will work but I want the Yes or No to populate a cell when it is entered. And I don't want the person inputing the data to be able to move on to the next entry unless they enter Yes or No. QUESTION: Anibtu wrote: Tom Ogilvy - 4/8/2009 Question Can you please show how to do the above subject using vba coding in Excel 2007. Numbers staring from 000000 to 999999 with each number from A1 B1 C1 D1 E1 F1 until A1000000-F1000000 Thanks for our kind help Anibtu Code that was given was: Sub gennumbers() Dim rw, i, j, k, l, m, n rw = 1 For i = 0 To 9 For j = 0 To 9 For k = 0 To 9 For l = 0 To 9 For m = 0 To 9 For n = 0 To 9 Cells(rw, 1) = i Cells(rw, 2) = j Cells(rw, 3) = k Cells(rw, 4) = l Cells(rw, 5) = m Cells(rw, 6) = n rw = rw + 1 If rw > Rows.Count Then MsgBox "Max rows reached at " & rw - 1 Exit Sub End If Next n Next m Next l Next k Next j Next i End Sub This will require2007 since earlier versions only have 65K rows. -- Tom Ogilvy Like Anibtu this for Lottery Pick 6 generator. I would like to select only 28 or 30 numbers from 49 to enter for the code to grenerate combinations of 6 based on the selected numbers, each number in its own cell from A1 B1 C1 D1 E1 F1 then continue to H1 I1 J1 K1 L1 M1 then O1 P1 Q1 R1 S1 T1 Many Laurent ANSWER: Laurent, I assume you mean fill up columns A:F, then move over to H1:M1 and fill up columns H:M and then move over to O1:T1 and so forth. You need to open a new workbook with two worksheets. One sheet named Sheet1 and one sheet named Sheet2 put your numbers in the first row of Sheet1 starting in cell A1 now do Alt+F11 to get to the visual basic editor. Select your workbook by looking in the left pane and finding your workbook name in the pane. Click on the entry for your workbook/project Now in the menu in the visual basic editor, choose Insert=>Module In the module that results past in this code (copied from my allexperts posting) Sub Combinations() Dim n As Integer, m As Integer Dim v As Variant, rng As Range Dim sh1 As Worksheet numcomb = 0 Set sh1 = Worksheets("Sheet1") Set rng = sh1.Range("A1", sh1.Cells(1, sh1.Columns.Count).End(xlToLeft)) 'Set rng = Range("A1:T1") 'Set rng = rng.Resize(1, 5) v = Application.Transpose(Application _ .Transpose(rng)) n = UBound(v, 1) 'm = InputBox("Taken how many at a time?", "Combinations") m = 6 Worksheets("sheet2").Select Range("A1").Select Comb2 n, m, 1, "'", v End Sub 'Generate combinations of integers k..n taken m at a time, recursively Sub Comb2(ByVal n As Integer, ByVal m As Integer, _ ByVal k As Integer, ByVal s As String, v As Variant) Dim v1 As Variant If m > n - k + 1 Then Exit Sub If m = 0 Then 'Debug.Print "->" & s & " ActiveSheet.Rows.Count - 2 Then Cells(1, ActiveCell.Column + 7).Select End If Exit Sub End If Comb2 n, m - 1, k + 1, s & k & " ", v Comb2 n, m, k + 1, s, v End Sub Now do Alt+F11 to get back to Excel. Save this new workbook Make sure this new workbook is the activeworkbook inwith Sheet1 as the activesheet and sheet1 containing your numbers in the first row starting in cell A1. Now in Excel 2003 and earlier, go to Tools=>macro>Macros, select Combinations and click the run button. in Excel 2003, click on the View tab. On the far right should be a Macro command button. click on the dropdown arrow and select View Macros. Then find Combinations in the resulting dialog. click on that and click Run. This should run for a long time depending on how many numbers you placed in row1. I would suggest starting with say 10 numbers and running the macro just to see if it works as you want. If so, clear Sheet2 and add more numbers in sheet1 and run it. By the way, Anibtu's question doesn't look like a lottery question to me and what he wanted is significantly different from you want - so modifying the code I wrote for that is no really appropriate. ---------- FOLLOW-UP ---------- QUESTION: How do I debug the Run Time Error '13' Type mismatch? When I go to debug the break mode yellow arrow is pointing to: n = UBound(v, 1) Best Laurent QUESTION: Hope you can advise on the below. I have maintain the following on Sheet2 of my Excel, in order to create a validation drop-down list in Sheet1 Col A Col B Yellow 80 Red 90 Blue 55 How can i write a VBA to reflect the corresponding value in Col B sheet1 each time i select Col D from the drop down list e.g. Sheet 1(click Col D drop down, Col B change accordingly. Col B Col D (Drop down) 80 Yellow 55 Blue ANSWER: Ken, You don't need VBA. Assume in sheet2, yellow is in A2 and 80 is in B2 just to orient you data to illustrate the formula you would use assume in Sheet1, the dropdown is in D2 (where you show yellow) to get 80 in cell B2 you would have a formula like this =if($D2="","",Index(Sheet2!$A$2:$A$5,match($D2,Sheet2!$B$2:$B$5,0),1)) then drag fill that formula down column B in sheet1. This is much more efficient than trying to write a macro to do it. If you really want a macro then right click on the sheet1 tab and select view code In the resulting module (associated with Sheet1), put in code like this: Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range, rA As Range, rB As Range Dim res As Variant Set r = Worksheets("Sheet1").Range("D2:D20") ' change to the range where cells have dropdowns If Target.Count > 1 Then Exit Sub If Not Intersect(Target, r) Is Nothing Then With Worksheets("Sheet2") Set rA = .Range("A2:A5") Set rB = .Range("B2:B5") End With res = Application.Index(rA, Application.Match(Target, rB, 0), 1) Application.EnableEvents = False If Not IsError(res) Then Target.Offset(0, -2).Value = res End If Application.EnableEvents = True End If End Sub that worked for me with the data being in the locations I stated. The formula also worked for me. ---------- FOLLOW-UP ---------- QUESTION: I try to use your VBA code but it doesnt work as there is nothing in col B sheet 1 when i select the drop down. My dropdown list is reference to Sheet2. e.g.=Sheet2!$A$2:$A$5 (and defined as "test") so my validation dropdown on sheet 1 col D is e.g. Validation>List>=test The below code is perfect, I need some changes in it as the below code is giving value of MAX/MIN on sheet2 of a specified time I need the value of OPEN/CLOSE also, I have change range from B to C now i am getting MAX value in Column C and MIN value in Column D, I need open in column B and close value in column E, suppose values between 10:00:00 to 10:01:00... OPEN (Value of 10:00:00) MAX (Value of Max between 10:00:00 to 10:01:00 ) MIN (Value of Min between 10:00:00 to 10:01:00 ) CLOSE (Value of 10:00:59) and so on ..... Thanx a tons in advance... Sub ABC() Dim r1 As Range, r2 As Range, hr As Single, minu As Single Dim etime As Single, stime As Single Dim min As Variant, max As Variant Dim v As Variant, v1 As Variant With Worksheets("Sheet1") Set r1 = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp)) End With With Worksheets("Sheet2") Set r2 = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0) End With hr = Hour(Now()) minu = Minute(Now()) etime = TimeSerial(hr, minu, 0) stime = TimeSerial(hr, minu - 1, 0) 'Debug.Print Format(etime, "hh:mm:ss"), Format(stime, "hh:mm:ss") v = r1.Offset(0, -1).Resize(, 2) min = 10000 max = -10000 For i = LBound(v, 1) To UBound(v, 1) v1 = v(i, 1) ' time stamp v2 = v(i, 2) ' updated value thistime = TimeSerial(Hour(v1), Minute(v1), Second(v1)) If thistime = stime Then If v2 max Then max = v2 End If Next r2.Offset(0, -1).Value = etime r2.Offset(0, 1).Value = min r2.Value = max End Sub Inthere is a short cut key Ctrl+Shift+ c to copy format and then Ctrl+Shift+v to paste format. I want to create the same short cut key invia a macro. The code I thought would be Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False This however does not work. I use word/excel 2007. Your assistance is appreciated Chris Dear Me again! Sorry. Following on from your previous help:- With your help, I now have Selection.FormulaR1C1= _ "=rank('Points Formula Sheet'!R2c4:r" & FinalRow & "c4) This works fine. However, I want to have a shortcut for ('Points Formula Sheet'!r2c4:r" & FinalRow & "c4) by using some sort of variable. I have been trying for hours, but once I have set the variable it will not work in the formula. Help please!! Lesley Ola Adelaide,tudo bem? :) Estou a criar um codigo para exportar uns graficos depara word. Tenho o seguinte codigo: Sub PasteToWord() Dim a, b, c, d As String Dim WDApp As Word.Application Set WDApp = CreateObject("Word.Application") WDApp.Visible = True WDApp.Documents.Add 'capa Sheets("word").Select With ThisWorkbook.Sheets("word") Range("e8:e19").Select Selection.Copy WDApp.Selection.Paste WDApp.Selection.TypeParagraph Range("g36:l42").CopyPicture xlScreen, xlBitmap WDApp.Selection.Paste WDApp.Selection.TypeParagraph Range("a1").Select Selection.Copy WDApp.Selection.Paste WDApp.Selection.TypeParagraph .Range("e50:e51").Select Selection.Copy WDApp.Selection.Paste WDApp.Selection.TypeParagraph End With 1?Para colocar espa?os entre os graficos o "TypeParagraph" ¨¦ bom ou sugere outro? 2?Como posso colocar cabe?alhos e rodap¨¦s no meu novo ficheiroatrav¨¦s do vba no excel? Pode-me ajudar? Obrigado Hugo

• Answer:

  My answer was not intended as the entire code. In your original question, you already had Set IE = CreateObject("InternetExplorer.Application") My answer was to show you how to use a string variable (in this case, strURL) so that the argument for the IE.Navigate line could be dependent on a value on the spreadsheet. You initial question said, "I simply want the link to be altered by input into specific cells," so that's the part I answered. Jason, Not totally clear what you are asking, but if you have a URL in in cell A1 of sheet1 With Activeworkbook .FollowHyperlink address:=worksheets("Sheet1").Range("A1").Text End With here are the arguments to followhyperlink from help expression.FollowHyperlink(Address, SubAddress, NewWindow, AddHistory, ExtraInfo, Method, HeaderInfo) you might want to add , NewWindow:=True depending on what your link is you might need to use SubAddress. You can test by turning on the macro recorder and manually create it as an actuall hyperlink and see howsets it up. Hope that is what you need. Rume, This is the answer I gave when you last asked me the question. I don't see anything different in this question so I am confused why you 1) didn't use anything I told you 2) are asking me again since you didn't use anything I told you It appears this question was routed to me from the question pool where it was placed by someone else you asked the question of. so disregard what I have said above since the question was not directed to me but apparently was incorrectly re-sent to me by some flaw in the allexperts system. Hopefully the answer I gave on the 27th has been useful to you. Rume, Step down 1 row from present location. i = i = 1 seems flawed I would guess you want i = i + 1 however, that will not change the activecell. Also, you start out on one sheet, hyperlink to another and copy to a workbook CMA Build.xls. Now this may just bring you back to the orginal sheet - I don't know. Anyway, I suspect that is your problem (it isn't bringing you back to the original sheet). If that is the case, then when you go to get the next hyperlink, you are on some sheet in the workbook CMA Build.xls and that isn't the sheet with the hyperlinks. (of course I could be wrong - hard to tell with code that uses selection) I would try to do it something like this Sub Macro3() Dim sh1 As Worksheet, sh As Worksheet Dim cell As Range, r As Range Dim r1 As Range, r2 As Range ' change the 1 in Worksheets to reflect the sheet ' name where you want to gather the data Set sh1 = Workbooks("CMA Build.xls").Worksheets(1) Set sh = ActiveSheet ' assumes more than one cell with a hyperlink, hyperlinks ' start in cell A5 Set r = sh.Range("A5", sh.Range("A5").End(xlDown)) For Each cell In r cell.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True Set r1 = Selection.CurrentRegion Set r2 = sh1.Cells(sh1.Rows.Count, "I").End(xlUp) If r2.Row Next End Sub I have assumed that you will have more than one hyperlink. That the sheet iwth the hyperlinks is the activesheet when you start the macro. That when you copy data, you don't want to overwrite the data you just copied from the previous sheet, but want to paste underneath it and that column I in the destination sheet can be used to tell where the last copied data ended (it has values in the last copied row in column I of the destination sheet). Also, your code shows you start copying from whatever cell your hyperlink takes you to. I have assumed that this is the top left cell of the populated range. IN that case selection.Current region is equivalent to your code. If there is data above or to the left of that location, then it could copy more data than you want - then you can replace my code using current region with your original code that selects the data to copy . If this doesn't work, then perhaps you can elaborate on your situation in a followup. Phil How about if sort your cards randomly then take them from the top of the list, as you need them? The look for duplicates method is obviously quite messy. You could have your "cards" in a column, in the adjacent column, put a random number, convert to values and sort by that column. The "draw" form the top and delete after drawing. Good luck. Sorry, that was silly of me while lcase(holdvar)<>"yes" and lcase(holdvar)<>"no" holdvar=inputbox("Yes or No") wend cells(target.row-0,7).value=holdvar should have been what I typed - in other words, you wait till you get the answer then you store the answer and move on. Laurent, The code works fine if you set up the workbook as I described. It was tested. this line set a range reference to the first row of sheet1 where the list of numbers you want to use to be generated into 6 number combinations are supposed to be located starting in cell A1 Set rng = sh1.Range("A1", sh1.Cells(1, sh1.Columns.Count).End(xlToLeft)) that range is then converted to a one dimension array of those numbers in this line: v = Application.Transpose(Application _ .Transpose(rng)) now we need to determine how many numbers you put in the first row. this will be stored in the variable n and will be found out by getting the Upper bound of the array v Your error (type mismatch) indicates that v is not an array. This would be true if you did not place your numbers in row 1 of sheet1 starting in A1 as the instructions indicated. Obviously you would need to have at least 6 numbers in cell A1 to cell F1 in Sheet1 (you can have more numbers than that extending to the right - that is the minimum I would expect). Itlooks like I have the match going to the wrong column. I have reversed them in the below. Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range, rA As Range, rB As Range Dim res As Variant Set r = Worksheets("Sheet1").Range("D2:D20") ' change to the range where cells have dropdowns If Target.Count > 1 Then Exit Sub If Not Intersect(Target, r) Is Nothing Then With Worksheets("Sheet2") Set rA = .Range("A2:A5") Set rB = .Range("B2:B5") End With res = Application.Index(rB, Application.Match(Target, rA, 0), 1) Application.EnableEvents = False If Not IsError(res) Then Target.Offset(0, -2).Value = res End If Application.EnableEvents = True End If End Sub and for completeness, here is the revised formula: =IF($D2="","",INDEX(Sheet2!$B$2:$B$5,MATCH($D2,Sheet2!$A$2:$A$5,0),1)) Yogesh Wadhwani Sub ABC() Dim r1 As Range, r2 As Range, hr As Single, minu As Single Dim etime As Single, stime As Single Dim min As Variant, max As Variant Dim v As Variant, v1 As Variant Dim openv As Variant, closev As Variant With Worksheets("Sheet1") Set r1 = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp)) End With With Worksheets("Sheet2") Set r2 = .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0) End With hr = Hour(Now()) minu = Minute(Now()) etime = TimeSerial(hr, minu, 0) stime = TimeSerial(hr, minu - 1, 0) 'Debug.Print Format(etime, "hh:mm:ss"), Format(stime, "hh:mm:ss") v = r1.Offset(0, -1).Resize(, 2) min = 10000 max = -10000 openv = 0 closev = 0 For i = LBound(v, 1) To UBound(v, 1) v1 = v(i, 1) ' time stamp v2 = v(i, 2) ' updated value thistime = TimeSerial(Hour(v1), Minute(v1), Second(v1)) If thistime = stime Then If openv = 0 Then openv = v2 If v2 max Then max = v2 closev = v2 End If Next r2.Offset(0, -1).Value = etime ' column A r2.Value = openv ' column B r2.Offset(0, 1).Value = max ' column C r2.Offset(0, 2).Value = min ' column D r2.Offset(0, 3).Value = closev ' column E End Sub should do what you want. you need to select first a cell for example: Range("C7").Select Anyway, I don;t know exactly why you need a Macro but, have you tried this: Copy: CTRL + C and then Paste: 1.-Format: Alternate menu, s, t, enter (one after other) note: the alternate menu is the button with an arrow and a menu between right ctrl and alt buttons Lesley, Generally, when they talk about shortcut, people mean they want to be able to hit a predefined key combination and have the macro run. If that is what you mean, then in xl2003 and earlier, go to Tools=>Macro=>Macros and select your macro in the dialog. Then click the options button and it will have a place where you can specify the key combination you want to use. if you mean you want a variable that contains the string: ('Points Formula Sheet'!r2c4:r" & FinalRow & "c4") Then I am not sure how you would use that. Basically, you can't have a variable hold executable code/commands. That is not a complete string because it contains the concatenation of 3 strings together. So you can hold in in a variable unless you get a result Public s as String FinalRow = cells(rows.count,"A").End(xlup).row s = "'Points Formula Sheet'!r2c4:r" & FinalRow & "c4" now if FinalRow has a value of 100, then s would hold the string "'Points Formula Sheet'!r2c4:r100c4" and you could use Selection.FormulaR1C1="=Rank" & s & ")" if you can clarify what you are trying to achieve, perhaps I can help. If it would help, send an simple sample workbook to [email protected] with an explanation of what you are trying to achieve. (also specify what version ofyou are using). One other thought, but it isn't VBA. Maybe you are trying to create a dynamic range Insert=>Name=>Define (in Excel 2003 and earlier) Name: MyList Refersto: =Offset('Points Formula Sheet'!$D$2,0,0,count('Points Formula Sheet'!$D$D)-1,1) Now in VBA you can use ActiveCell.Formula = "=Rank(Mylist)" so in a sense, the defined range/named range MyList is a variable. It will expand if more data is added in column D of 'Points Formula Sheet' Hi Hugo (Ol¨¢) Como est¨¢? /How are you? Tente por favor / Please try the following Sub PasteToWord() Dim a, b, c, d As String Dim WDApp As Word.Application Set WDApp = CreateObject("Word.Application") WDApp.Visible = True WDApp.Documents.Add 'capa Sheets("word").Select With ThisWorkbook.Sheets("word") Range("e8:e19").Select Selection.Copy WDApp.Selection.Paste WDApp.Selection.TypeParagraph Range("g36:l42").CopyPicture xlScreen, xlBitmap WDApp.Selection.Paste WDApp.Selection.TypeParagraph Range("a1").Select Selection.Copy WDApp.Selection.Paste WDApp.Selection.TypeParagraph .Range("e50:e51").Select Selection.Copy WDApp.Selection.Paste WDApp.Selection.TypeParagraph WDApp.ActiveDocument.ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader WDApp.Selection.TypeText Text:="aaaaaa" End With End Sub Se sabe Portugu¨ºs, veja o meu livro ( procure Office em www.fca.pt) 978-972-722-619-1 Carvalho, Adelaide (2009) Programa??o compara Economia & Gest?o - Vol. II que tem algum c¨®digo para manipular o Word,.ea partir do Excel Adelaide