0

please let me know if I need to be more specific. I am entering a weeknum() formula on my workbook isum on sheet stock. I want this formula to be evaluated for everything in the "X" column starting at row 2 since the top row will be a label. I used a macro recorder, but I am getting an error 1004 because my "select method of range class failed". There is probably a better way of writing this code. After, I want to make any weeknum value that is less than 27 in this column "Past". isum is my workbook and stock is my sheet in this workbook. I just made the range up to a certain amount rather than the used amount that I want to have. Here is my code so far: isum.Sheets("stock").Range("X2").Select ActiveCell.FormulaR1C1 = "=WEEKNUM(RC[-9])" Selection.AutoFill Destination:=Range("X2:X900").Select Range("X2:X900").Select

1 Answers1

1

Your code could be simplified to:

'Declare variables
dim wb as Workbook
dim ws as Worksheet
dim rng as Range

'Set variables
set wb = Workboooks("isum.xlsx") 'check the file extension & name is correct
set ws = wb.Worksheets("stock")
set rng = ws.range("X2:X900")

'Fill range with formula
rng.FormulaR1C1 = "=WEEKNUM(RC[-9])"

(Link - Here's a great reference on how/why to avoid using SELECT in VBA)

Edit: another way to set the workbook:

set wb = ThisWorkbook
girlvsdata
  • 1,596
  • 11
  • 21
  • If the workbook needs to bet set, I would just use `Set wb = Thisworkbook` – urdearboy Jul 02 '18 at 23:58
  • @urdearboy do you know if using `Thisworkbook` or hardcoding the workbook name is best practice? I was trying to see if anyone's asked the question on SO, but I haven't found a definitive answer. I usually set the workbook name as the variable because I thought it was best practice, but `Thisworkbook` would obviously work better if you ever want to change the name of the workbook. – girlvsdata Jul 03 '18 at 01:30
  • @urdearboy I guess it also depends if OP has their code saved in the workbook or an external "Macro Book" – girlvsdata Jul 03 '18 at 01:36
  • I only use the book name when referring to a separate workbook (I.e. any workbook that isn’t the book that houses the code). When referring to the book that houses the code, I would say **Thisworkbook** is easier. For instance, if the book (in your solution) is saved as macro enabled file, your code will not reach end sub (run time error) next time the book is opened since it will be looking for a **.xlsx** file instead of a **.xlsm** file. – urdearboy Jul 03 '18 at 01:39
  • @urdearboy good point on the file extension changes, I think I'll start using `Thisworkbook` more often. I'll edit the answer. – girlvsdata Jul 03 '18 at 01:41
  • @girlvsdata I am unfortunately getting an error 9 on the `set rng = ws.range("X2:X900")` line. I followed the exact code. –  Jul 03 '18 at 03:29
  • Have you got any other code in the same sub? I just ran the code in a new workbook with the same sheet name and it ran without errors. – girlvsdata Jul 03 '18 at 03:37
  • That's odd. The code I have before your simplified code is copying and pasting from one workbook to another. Could it be workbook activating that's the problem? I am not sure. The code before: `isum.Sheets("stock").Range("A2:X3000").ClearContents PO.Sheets("Finance").Range("A2:W3000").Copy Destination:=isum.Sheets("stock").Range("A2")` where "isum" is a variable I set for workbook1 and "PO" is workbook2 copying from the "Finance" sheet to the "stock" sheet –  Jul 03 '18 at 04:14
  • That's really funny :'D, I think the code I had before @urdearboy gave me a tip probably would work better in this case. `ThisWorkbook` calls the workbook that the code is saved in, so my guess is that you're running into problems from calling an external workbook. I'll update the answer to include both options, let me know if it helps or not. – girlvsdata Jul 03 '18 at 04:21
  • Oh, that sounds like that may work since the code is from another workbook. Sorry, what should I replace `ThisWorkbook` with instead? –  Jul 03 '18 at 04:26
  • Maybe including my previous code will do the trick. `Dim isum as work book Dim PO as workbook Set isum = Workbooks.Open("\\sales\internal\summary.xlsb") Set PO = Workbooks.Open("\\global\prod\Open US.xlsx") isum.Sheets("stock").Range("A2:X3000").ClearContents PO.Sheets("Finance").Range("A2:w3000").Copy Destination:=isum.Sheets("stock").Range("A2") Dim wbk As Workbook Dim ws As Worksheet Dim rng As Range Set wbk = Workbooks("isum.xlsb") Set ws = wbk.Worksheets("stock") Set rng = ws.Range("X2:X900") rng.FormulaR1C1 = "=WEEKNUM(RC[-9])"` –  Jul 03 '18 at 04:45
  • It still showed an error at the same area. Also I am so sorry I am not sure how to separate each line of code on a different line here –  Jul 03 '18 at 04:46
  • Sorry last comment is that I may know the error and will update after testing (there is a lot of data so it takes a while) –  Jul 03 '18 at 04:54
  • The error is because your workbook "isum" is called "summary.xlsb." Replace this line: `Set wbk = Workbooks("isum.xlsb")` with `set wbk = isum` or just delete that line and replace any time you call "wbk" with "isum" – girlvsdata Jul 03 '18 at 05:00
  • Update: I did that and it all worked! Oddly enough, it still highlights the same line saying that there is an error even though it works. If it gets in the way, i'll add another thread. Thank you! –  Jul 03 '18 at 15:44
  • Great to hear! Please mark my answer as the chosen one if it worked for you :) Thanks! – girlvsdata Jul 03 '18 at 22:14