0

I've attempted to develop a macro that creates new workbooks for every iteration a sheet can have based on a dynamic number of inputs, found below. When running the program, the first time I try to update a formula I receive the error "Application-Defined or Object-Defined Error". I'm guessing this has something to do with my global variables? I'm new to vba so any help would be appreciated!

Sub create_jvs()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim achWS, refWS As Worksheet
Dim j, fRow, lRow As Integer

Set refWS = wb.Sheets("References")
Set achWS = wb.Sheets("ACHLinkage")

'get first and last row of constraint range in ACHLinkage
fRow = 2
lRow = achWS.Columns("AB").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row

'loop to produce separate jvs
For j = -29 To (-29 + (lRow - fRow)) Step 1
    
    refWS.Range("R31").Select            'select the first reference cell 
    
    'update formula 1
    ActiveCell.FormulaR1C1 = "=IF(ACHLinkage!R[j]C[10] = """",  """", IF(ACHLinkage!R[j]C[10]=""N"",ROUNDDOWN(References!R30C19*ACHLinkage!R[j]C[11],2),ROUNDUP(References!R30C19*ACHLinkage!R[j]C[11],2)))"
    
    ActiveCell.Offset(1).Select
    
    'update formula 2
    ActiveCell.FormulaR1C1 = "=IF(ACHLinkage!R[j-1]C[10] = """", """", IF(ACHLinkage!R[j-1]C[10] = ""N"", ROUNDDOWN(References!R28C19*ACHLinkage!R[j-1]C[11],2), ROUNDUP(References!R28C19*ACHLinkage!R[j-1]C[11],2)))"
    
    ActiveCell.Offset(1).Select
    
    'update formula 3
    ActiveCell.FormulaR1C1 = "=IF(ACHLinkage!R[j-2]C[10] = """", """", IF(ACHLinkage!R[j-2]C[10]=""N"",ROUNDDOWN(USERFORM!R26C6*ACHLinkage!R[j-2]C[11],2),ROUNDUP(USERFORM!R26C6*ACHLinkage!R[j-2]C[11],2)))"
    
    ActiveCell.Offset(1).Select
    
    'update formula 4
    ActiveCell.FormulaR1C1 = "=IF(ACHLinkage!R[j-3]C[10] = """", """", IF(ACHLinkage!R[j-3]C[10]=""N"",ROUNDDOWN(USERFORM!R26C7*ACHLinkage!R[j-3]C[11],2),ROUNDUP(USERFORM!R26C7*ACHLinkage!R[j-3]C[11],2)))"
    
    ActiveCell.Offset(1).Select
    
    'update formula 5
    ActiveCell.FormulaR1C1 = "=IF(ACHLinkage!R[j-4]C[10] = """", """", IF(ACHLinkage!R[j-4]C[10]=""N"",ROUNDDOWN(USERFORM!R26C8*ACHLinkage!R[j-4]C[11],2),ROUNDUP(USERFORM!R26C8*ACHLinkage!R[j-4]C[11],2)))"
    
    ThisWorkbook.Sheets("ACH JV").Copy   'copy jvWS to new wb
    
Next j

'reset parameters to starting row
refWS.Range("R31").Select
ActiveCell.FormulaR1C1 = "=IF(ACHLinkage!R[-29]C[10] = """",  """", IF(ACHLinkage!R[-29]C[10]=""N"",ROUNDDOWN(References!R30C19*ACHLinkage!R[-29]C[11],2),ROUNDUP(References!R30C19*ACHLinkage!R[-29]C[11],2)))"
ActiveCell.Offset(1).Select
ActiveCell.FormulaR1C1 = "=IF(ACHLinkage!R[-30]C[10] = """", """", IF(ACHLinkage!R[-30]C[10] = ""N"", ROUNDDOWN(References!R28C19*ACHLinkage!R[-30]C[11],2), ROUNDUP(References!R28C19*ACHLinkage!R[-30]C[11],2)))"
ActiveCell.Offset(1).Select
ActiveCell.FormulaR1C1 = "=IF(ACHLinkage!R[-31]C[10] = """", """", IF(ACHLinkage!R[-31]C[10]=""N"",ROUNDDOWN(USERFORM!R26C6*ACHLinkage!R[-31]C[11],2),ROUNDUP(USERFORM!R26C6*ACHLinkage!R[-31]C[11],2)))"
ActiveCell.Offset(1).Select
ActiveCell.FormulaR1C1 = "=IF(ACHLinkage!R[-32]C[10] = """", """", IF(ACHLinkage!R[-32]C[10]=""N"",ROUNDDOWN(USERFORM!R26C7*ACHLinkage!R[-32]C[11],2),ROUNDUP(USERFORM!R26C7*ACHLinkage!R[-32]C[11],2)))"
ActiveCell.Offset(1).Select
ActiveCell.FormulaR1C1 = "=IF(ACHLinkage!R[-33]C[10] = """", """", IF(ACHLinkage!R[-33]C[10]=""N"",ROUNDDOWN(USERFORM!R26C8*ACHLinkage!R[-33]C[11],2),ROUNDUP(USERFORM!R26C8*ACHLinkage!R[-33]C[11],2)))"
   
End Sub
inscoder
  • 11
  • 3
  • 2
    The problem is `j`, `j-1`, `j-2`, etc. fall within the quotes. Variables belong outside quotes and need concatenated with `&`. – BigBen Oct 22 '21 at 19:11
  • So how would I be able to accomplish this? I need to use j to move through the different values I'm updating – inscoder Oct 22 '21 at 19:15
  • 1
    See [this](https://stackoverflow.com/questions/42503316/how-can-i-insert-variable-into-formula-in-vba). – BigBen Oct 22 '21 at 19:16
  • Avoid using `.select` and what is your intention with this line `xlCell.Offset(1).Select` it should be either `xlCell.Offset(1,0)` or `xlCell.Offset(0,1)" depending on whether you want to move columns or rows – Glenn G Oct 22 '21 at 19:19
  • Thanks Ben! And I'm new to vba - could you explain why I shouldn't use .select and what I would use instead? Also, it was my understanding that xlCell.Offset(1) is the same as xlCell.Offset(1,0) - I was just lazy and didn't include the column indicator, which makes it automatically 0. – inscoder Oct 22 '21 at 19:23
  • https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba This has tons of information on why you should not use `.select` Basically, it slows down your code and is often the cause of errors – Glenn G Oct 22 '21 at 19:28
  • Thanks everyone! – inscoder Oct 22 '21 at 19:33

0 Answers0