Easy way to convert formula into vba/macro?

[Akira181]

I have a table of 52 columns 45 rows with a rather long formula in there to build the data. I'm having an issue where sometimes the data will need to be manually entered, which will delete the formula, which I don't want.

Is there an easy way to convert the formula for the whole table into VBA/Macro?

I tried recording a macro > highlighting the table > F2 > enter but it only worked for one cell. The formula is essentially identical throughout apart from the cell references.

 

[offthelip]

Your modifications all look good, the only suggestion I could make is: in your pop up you could say which row is going to be cleared by changing the code to::

rspn = MsgBox("Clear any data in Sheet 2 row    " &  rowno-3 & "?", vbYesNo)   ' Popup warning of clearing data ***

 

[Akira181]

Your modifications all look good, the only suggestion I could make is: in your pop up you could say which row is going to be cleared by changing the code to::

rspn = MsgBox("Clear any data in Sheet 2 row    " &  rowno-3 & "?", vbYesNo)   ' Popup warning of clearing data ***

Didn't know you could do that, good suggestion. Thanks!

Just found a bug though, haven't been able to figure it out yet. If I make frequency 52, I get a run-time error, subscript out of range. Doesn't seem to matter what number is in spend week column either. Any suggestions?

*edit
The error appears once frequency is 48 or above. Can't see any reason for it yet

 

[offthelip]

The bug is my fault, column Be is column 57 NOT column 52 , so every occurence of 52 shoud be changed to 57.

 

[Akira181]

amazing, I don't know how long it would have taken me to figure that out, if at all.

It's doing what I want it to now, thank you so much!

 

[offthelip]

Glad to be of help! Can I suggest you update your profile to include the version of Office that you are using because some solutions depend on which version is available.

 

[Akira181]

oops, thought I'd done that already. Updated now

 

[Akira181]

Your modifications all look good, the only suggestion I could make is: in your pop up you could say which row is going to be cleared by changing the code to::

rspn = MsgBox("Clear any data in Sheet 2 row    " &  rowno-3 & "?", vbYesNo)   ' Popup warning of clearing data ***

Think I've created a bug with the pop up. When "No" is selected, I've written

If rspn = vbNo Then Exit Sub

I don't think "Exit Sub" was the right statement(?) to use. It ends the VBA and any changes I make afterwards does nothing until I close and reopen the excel file and make another change.

Is it possible to make it undo the change (so "Manual" is reverted back to the previous value if "No" is selected) and not end the VBA?

 

[Akira181]

also getting a runtime error when I select "Single" and the "Spend Week" column is empty although I don't get that issue with "Recurring" and the code is virtually the same. So confusing!

 

[offthelip]

The probelm is the exit sub doesn't execute the application enable events code so No triggers happen at all unitl you have restarted the workbook. I have modified the code to ensure all exits go through the enable events code: I have also put in some error checking to avoid the runtime errors.

Private Sub Worksheet_Change(ByVal Target As Range)
colno = Target.Column
rowno = Target.Row
If rowno < 8 Then Exit Sub ' exit is not changing an entry
If colno = 6 Or colno = 7 Or colno = 8 Or colno = 12 Then
 inarr = Range(Cells(rowno, 6), Cells(rowno, 12)) ' pict up inputs from the line that has changed
 Application.EnableEvents = False
 With Worksheets("Sheet2")
' **************************************************
 If inarr(1, 1) = "Recurring" Then
  .Range(.Cells(rowno - 3, 6), .Cells(rowno - 3, 57)) = ""   ' Clear entire row of week entries
  outarr = .Range(.Cells(rowno - 3, 6), .Cells(rowno - 3, 57))
  amnt = inarr(1, 7)   ' amount
  swk = inarr(1, 2) ' spend week
  If swk < 1 Or swk = "" Then
    MsgBox (" You must enter a value for Spend week")
    GoTo enableexit
  End If
  frq = inarr(1, 3)
  If frq < 1 Or frq = "" Then
    MsgBox (" You must enter a value for Frequency")
    GoTo enableexit
  End If
   For i = swk To swk + frq - 1
      outarr(1, i) = amnt / frq      ' write out weekly stuff in a loop   ***
   Next i
  .Range(.Cells(rowno - 3, 6), .Cells(rowno - 3, 57)) = outarr
  .Range(.Cells(rowno - 3, 3), .Cells(rowno - 3, 3)) = "Recurring"

 End If
 '***************************************************************
 If inarr(1, 1) = "Single" Then
  .Range(.Cells(rowno - 3, 6), .Cells(rowno - 3, 57)) = ""   ' Clear entire row of week entries
  outarr = .Range(.Cells(rowno - 3, 6), .Cells(rowno - 3, 57))
  amnt = inarr(1, 7)   ' amount
  swk = inarr(1, 2)    ' spend week
   If swk < 1 Or swk = "" Then
    MsgBox (" You must enter a value for Spend week")
    GoTo enableexit
  End If
   outarr(1, swk) = amnt       ' write out weekly stuff in a loop
  .Range(.Cells(rowno - 3, 3), .Cells(rowno - 3, 3)) = "Single"
  .Range(.Cells(rowno - 3, 6), .Cells(rowno - 3, 57)) = outarr
            Range("H" & Target.Row).ClearContents   ' Clear frq cell if "Single" selected ***
 End If
 '******************************************************
 If inarr(1, 1) = "Manual" Then
    rspn = MsgBox("Clear any data in Sheet 2 row?    ", vbYesNo)   ' Popup warning of clearing data ***
    If rspn = vbNo Then GoTo enableexit    '***
  .Range(.Cells(rowno - 3, 6), .Cells(rowno - 3, 57)) = ""   ' Clear entire row of week entries
  .Range(.Cells(rowno - 3, 3), .Cells(rowno - 3, 3)) = "Manual"
            Range("G" & Target.Row).ClearContents     '[/COLOR]   ***
            Range("H" & Target.Row).ClearContents   ' Clear frq and swk cells if "Manual" selected  ***
            Sheets("Sheet2").Activate   ' Activate Sheet 2   ***
            ActiveSheet.Range("F" & Target.Row - 3).Select   ' Select first week of active row   ***
 
 End If
 
 End With
End If
enableexit:
Application.EnableEvents = True
End Sub

If you get into the state where you have switched off the events due to an error and you wnat to getthem going again all you have to do is run this macros:

Sub en()
Application.EnableEvents = True
End Sub

 

[Akira181]

ah, google led me as far as GoTo but wasn't sure what to reference. I was trying to refer back to the first line, which was clearly wrong. Thanks for explaining what the application.enableevents bit does, makes sense now. Should have started my Google efforts there!

Also used On Error Resume Next for the single spend week issue, which wasn't ideal as all errors end up hidden, your suggestion is naturally better. Fingers crossed that's everything!