Easy way to convert formula into vba/macro?

Akira181 · Dec 22, 2021

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 · Dec 22, 2021

Almost any formula can be converted into VBA, it really depends on what the formula is as to how hard it is.

Akira181 · Dec 24, 2021

offthelip said:
Almost any formula can be converted into VBA, it really depends on what the formula is as to how hard it is.

I was hoping there would be an easy way to do it, similar to recording a macro. Below is my formula, will it be difficult to set up manually for a novice at VBA?

=IFNA(IFS(AND('Sheet1'!$F8="Single",'Sheet1'!$G8='Sheet2'!F$3),'Sheet 1'!$L8,AND('Sheet1'!$F8="Repeating",'Sheet1'!$G8<='Sheet2'!F$3,'Sheet1'!$G8+'Sheet1'!$H8>'Sheet2'!F$3),'Sheet1'!$L8/IF(ISNUMBER('Sheet1'!$H8),'Sheet1'!$H8,1)),)

I'm basically taking a spend forecast from Sheet 1, checking if it's a "Single" spend and putting it into the relative calendar week cell for that line item in Sheet 2. If it's "Repeating", I divide the total spend by the number of repeating weeks and then put that cost into the respective cells in Sheet 2.

IFNA is to keep all the outputs in number format as the cells are then used to calculate other stuff and returns #VALUE errors if I don't

offthelip · Dec 24, 2021

When converting a formula to VBa, one of the first things to be aware of is that every access to the worksheet takes quite along, time. For the formula you have given if is probably insignificant, but if ti is in a loop then it could get a bit slow. The way to avoid this is the load all the data into a variant array and access that instead. I assume that this isn't the case here so I will use multiply accesses
I have tried to contricut this to closely match your orginal equations:

VBA Code:

Sub test()
'=IFNA(IFS(AND('Sheet1'!$F8="Single",'Sheet1'!$G8='Sheet2'!F$3),'Sheet 1'!$L8,
'AND('Sheet1'!$F8="Repeating",'Sheet1'!$G8<='Sheet2'!F$3,'Sheet1'!$G8+'Sheet1'!$H8>'Sheet2'!F$3),'Sheet1'!$L8/IF(ISNUMBER('Sheet1'!$H8),'Sheet1'!$H8,1)),)
'load all the variables
With Worksheets("Sheet2")
 F3 = .Range(.Cells(3, 6), .Cells(3, 6))
End With
With Worksheets("Sheet1")
 F8 = .Range(.Cells(8, 6), .Cells(8, 6))
 G8 = .Range(.Cells(8, 7), .Cells(8, 7))
 H8 = .Range(.Cells(8, 8), .Cells(8, 8))
 L8 = .Range(.Cells(8, 12), .Cells(8, 12))
  ansr = ""

'=IFNA(IFS(AND('Sheet1'!$F8="Single",'Sheet1'!$G8='Sheet2'!F$3),'Sheet 1'!$L8,
    If (F8 = "Single" And G8 = F3) Then
      ansr = L8
    Else
'AND('Sheet1'!$F8="Repeating",'Sheet1'!$G8<='Sheet2'!F$3,'Sheet1'!$G8+'Sheet1'!$H8>'Sheet2'!F$3),'Sheet1'!$L8/IF(ISNUMBER('Sheet1'!$H8),'Sheet1'!$H8,1)),)
     If F8 = "Repeating" And G8 <= F3 And (G8 + H8) > F3 Then
       ''Sheet1'!$L8/IF(ISNUMBER('Sheet1'!$H8),'Sheet1'!$H8,1
       If IsNumeric(H8) Then
        ansr = L8 / H8
       Else
        ansr = L8
       End If
     End If
    End If
 if not(isnumeric(ansr)) then
  ansr=0
end if
  MsgBox ansr

End With

Akira181 · Dec 28, 2021

offthelip said:
When converting a formula to VBa, one of the first things to be aware of is that every access to the worksheet takes quite along, time. For the formula you have given if is probably insignificant, but if ti is in a loop then it could get a bit slow. The way to avoid this is the load all the data into a variant array and access that instead. I assume that this isn't the case here so I will use multiply accesses
I have tried to contricut this to closely match your orginal equations:

VBA Code:

Sub test() '=IFNA(IFS(AND('Sheet1'!$F8="Single",'Sheet1'!$G8='Sheet2'!F$3),'Sheet 1'!$L8, 'AND('Sheet1'!$F8="Repeating",'Sheet1'!$G8<='Sheet2'!F$3,'Sheet1'!$G8+'Sheet1'!$H8>'Sheet2'!F$3),'Sheet1'!$L8/IF(ISNUMBER('Sheet1'!$H8),'Sheet1'!$H8,1)),) 'load all the variables With Worksheets("Sheet2") F3 = .Range(.Cells(3, 6), .Cells(3, 6)) End With With Worksheets("Sheet1") F8 = .Range(.Cells(8, 6), .Cells(8, 6)) G8 = .Range(.Cells(8, 7), .Cells(8, 7)) H8 = .Range(.Cells(8, 8), .Cells(8, 8)) L8 = .Range(.Cells(8, 12), .Cells(8, 12)) ansr = "" '=IFNA(IFS(AND('Sheet1'!$F8="Single",'Sheet1'!$G8='Sheet2'!F$3),'Sheet 1'!$L8, If (F8 = "Single" And G8 = F3) Then ansr = L8 Else 'AND('Sheet1'!$F8="Repeating",'Sheet1'!$G8<='Sheet2'!F$3,'Sheet1'!$G8+'Sheet1'!$H8>'Sheet2'!F$3),'Sheet1'!$L8/IF(ISNUMBER('Sheet1'!$H8),'Sheet1'!$H8,1)),) If F8 = "Repeating" And G8 <= F3 And (G8 + H8) > F3 Then ''Sheet1'!$L8/IF(ISNUMBER('Sheet1'!$H8),'Sheet1'!$H8,1 If IsNumeric(H8) Then ansr = L8 / H8 Else ansr = L8 End If End If End If if not(isnumeric(ansr)) then ansr=0 end if MsgBox ansr End With

thanks for writing that and commenting it so I can understand, very much appreciated! I'll give it a try in the next days and read up on what you mean by variant arrays and multiply accesses.

My niece and nephew are staying over and they're an absolute handful!

Akira181 · Jan 4, 2022

offthelip said:
When converting a formula to VBa, one of the first things to be aware of is that every access to the worksheet takes quite along, time. For the formula you have given if is probably insignificant, but if ti is in a loop then it could get a bit slow. The way to avoid this is the load all the data into a variant array and access that instead. I assume that this isn't the case here so I will use multiply accesses
I have tried to contricut this to closely match your orginal equations:

VBA Code:

Sub test() '=IFNA(IFS(AND('Sheet1'!$F8="Single",'Sheet1'!$G8='Sheet2'!F$3),'Sheet 1'!$L8, 'AND('Sheet1'!$F8="Repeating",'Sheet1'!$G8<='Sheet2'!F$3,'Sheet1'!$G8+'Sheet1'!$H8>'Sheet2'!F$3),'Sheet1'!$L8/IF(ISNUMBER('Sheet1'!$H8),'Sheet1'!$H8,1)),) 'load all the variables With Worksheets("Sheet2") F3 = .Range(.Cells(3, 6), .Cells(3, 6)) End With With Worksheets("Sheet1") F8 = .Range(.Cells(8, 6), .Cells(8, 6)) G8 = .Range(.Cells(8, 7), .Cells(8, 7)) H8 = .Range(.Cells(8, 8), .Cells(8, 8)) L8 = .Range(.Cells(8, 12), .Cells(8, 12)) ansr = "" '=IFNA(IFS(AND('Sheet1'!$F8="Single",'Sheet1'!$G8='Sheet2'!F$3),'Sheet 1'!$L8, If (F8 = "Single" And G8 = F3) Then ansr = L8 Else 'AND('Sheet1'!$F8="Repeating",'Sheet1'!$G8<='Sheet2'!F$3,'Sheet1'!$G8+'Sheet1'!$H8>'Sheet2'!F$3),'Sheet1'!$L8/IF(ISNUMBER('Sheet1'!$H8),'Sheet1'!$H8,1)),) If F8 = "Repeating" And G8 <= F3 And (G8 + H8) > F3 Then ''Sheet1'!$L8/IF(ISNUMBER('Sheet1'!$H8),'Sheet1'!$H8,1 If IsNumeric(H8) Then ansr = L8 / H8 Else ansr = L8 End If End If End If if not(isnumeric(ansr)) then ansr=0 end if MsgBox ansr End With

Hi again, hope you had a happy new year!

Unfortunately, either I'm too much of a novice with VBA to get this working properly or I haven't explained myself too well. I've posted a snapshot of what I'm trying to do below, hopefully you can tell me where I'm going wrong!

Sheet 1
Basically the user will enter the below details into a new line for each group identified (it's unlikely this list will go beyond 20 or 30 line items). Columns A to G contain information that isn't relevant to this problem.

Spend type only has the three displayed options available.

Book2

A

B

C

D

E

F

G

H

I

J

K

L

1

2

3

4

5

6

A

B

C

D

Spend Type

Spend Week

Frequency

E

F

G

Cost

7

8

Entry 1

Recurring

1

4

£ 4,000.00

9

Entry 2

Single

2

£ 2,500.00

10

Entry 3

Manual Input

£ 300.00

11

Entry X

Sheet1

Sheet 2
This sheet is where I have my formula that I'm trying to convert to VBA. The table has columns up to Week 52.

The formula I posted above takes the spend information entered in Sheet 1 and splits it into the relative calendar week in Sheet 2. The formula works but the problem starts with the "Manual Input" option from Sheet 1.

Manual input is for irregular spending, so the user will go into Sheet 2 and manually enter the spend in the respective weeks (the yellow cells). This will require the user to delete the formula within the cell.

I was hoping I could convert the formula in the various cells to VBA and only have it work if the required cell in Sheet 2 is empty (i.e. no Manual Input data entered). Can this be done or should I rethink my approach for "Manual Inputs"?

Book2

A

B

C

D

E

F

G

H

I

J

1

2

Month

Jan

Feb

3

A

B

Sub Total

WK.

1

2

3

4

5

4

5

Entry 1

(recurring cost)

£ 4,000.00

£ 1,000.00

£ -

6

Entry 2

(single cost)

7

Entry 3

(manual input)

£ 300.00

£ -

£ 50.00

£ -

£ 200.00

£ 50.00

8

Entry X

Sheet2

Cell Formulas
Range	Formula
F2:J2	F2	=TEXT(DATE(YEAR(NOW()),1,F3*7-2),"mmm")
I5:J6,H5:H7,F5:F7,G5:G6	I5	=IFNA(IFS(AND(Sheet1!$F8="Single",Sheet1!$G8=Sheet2!I$3),Sheet1!$L8,AND(Sheet1!$F8="Recurring",Sheet1!$G8<=Sheet2!I$3, Sheet1!$G8+Sheet1!$H8>Sheet2!I$3), Sheet1!$L8/IF(ISNUMBER(Sheet1!$H8), Sheet1!$H8,1)),)
D5:D7	D5	=SUM(F5:J5)

*Can't remember why I used a formula in F2 to get the month but there was a reason that I've since forgotten. Probably not relevant though

offthelip · Jan 4, 2022

The way I would do that is to just use VBA to write the values into sheet 2 for Recurring and single costs automatically, so there will no formula in D5 to where ever your last column is. These would be updated if any of the values in F , G or H are updated. If manual is selected then all values on the row in sheet2 would be cleared.
I do have a few question:
1: Is columm J the last column if not what is the last column.
2: Is column F always week 1?
3: In your examples here you have £1000 expenditure for weeks 1 to 4 while your entry on on sheet 1 seems to be £4000 , what is it supposed to be??
4: sheet 1 column H is label Frequency yet your example seems to be using a count of payments going out weekly , Is this correct. I would expect Frequency for be weekly, fortnightly, every four weeks, monthly ( which woud cause a problem) , quarterly,
6: Do you need Frequency and count??

offthelip · Jan 4, 2022

I have assumed the following answer to my questions;
1: Is columm J the last column if not what is the last column. BE
2: Is column F always week 1? Yes
3: In your examples here you have £1000 expenditure for weeks 1 to 4 while your entry on on sheet 1 seems to be £4000 , what is it supposed to be?? £4000 each week
4: sheet 1 column H is label Frequency yet your example seems to be using a count of payments going out weekly , Is this correct. I would expect Frequency for be weekly, fortnightly, every four weeks, monthly ( which woud cause a problem) , quarterly, Assume it is a count and always weekly
6: Do you need Frequency and count?? No
try this code just put it in the worksheet change code on workhseet 1

VBA Code:

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, 52)) = ""   ' Clear entire row of week entries
  outarr = .Range(.Cells(rowno - 3, 6), .Cells(rowno - 3, 52))
  amnt = inarr(1, 7)   ' amount
  swk = inarr(1, 2)    ' spend week
  frq = inarr(1, 3)
   For i = swk To swk + frq - 1
      outarr(1, i) = amnt       ' write out weekly stuff in a loop
   Next i
  .Range(.Cells(rowno - 3, 6), .Cells(rowno - 3, 52)) = 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, 52)) = ""   ' Clear entire row of week entries
  outarr = .Range(.Cells(rowno - 3, 6), .Cells(rowno - 3, 52))
  amnt = inarr(1, 7)   ' amount
  swk = inarr(1, 2)    ' spend week
      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, 52)) = outarr
 End If
 '******************************************************
 If inarr(1, 1) = "Manual" Then
  .Range(.Cells(rowno - 3, 6), .Cells(rowno - 3, 52)) = ""   ' Clear entire row of week entries
  .Range(.Cells(rowno - 3, 3), .Cells(rowno - 3, 3)) = "Manual"
 
 End If
 
 End With
End If
Application.EnableEvents = True
End Sub

Akira181 · Jan 4, 2022

offthelip said:
The way I would do that is to just use VBA to write the values into sheet 2 for Recurring and single costs automatically, so there will no formula in D5 to where ever your last column is. These would be updated if any of the values in F , G or H are updated. If manual is selected then all values on the row in sheet2 would be cleared.
I do have a few question:
1: Is columm J the last column if not what is the last column. BE
2: Is column F always week 1? Yes
3: In your examples here you have £1000 expenditure for weeks 1 to 4 while your entry on on sheet 1 seems to be £4000 , what is it supposed to be?? The 4,000 is the total cost, divided over 4 weeks, so 1,000 a week starting from week 1.
4: sheet 1 column H is label Frequency yet your example seems to be using a count of payments going out weekly , Is this correct. I would expect Frequency for be weekly, fortnightly, every four weeks, monthly ( which woud cause a problem) , quarterly, as above
6: Do you need Frequency and count?? as above again or have I misunderstood?

Thanks for the fast response! My answers are in the quote, you got most of them right. Only a misunderstanding with 3 and 4. I'm not sure I understand the last questions but I think it's been answered.

In Sheet 1, for "Recurring" the Cost in column L is the total cost. This is then divided by the Frequency in column H and the result is put into the respective cells in Sheet 2, starting from the week entered into "Spend Week" in Column G.

So in the example, Starting from week 1, for 4 weeks, a total cost of 4.000. Then that

Akira181 · Jan 5, 2022

Played around with your code and think I figured it out. Added a popup warning for "Manual" selection too and some other minor stuff. Probably not the neatest way of doing it but it seems to work. I've posted my code below in case what I've done potentially causes issues that I'm not aware of.

Many thanks for your help, greatly appreciated!

VBA Code:

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, 52)) = ""   ' Clear entire row of week entries
  outarr = .Range(.Cells(rowno - 3, 6), .Cells(rowno - 3, 52))
  amnt = inarr(1, 7)   ' amount
  swk = inarr(1, 2)    ' spend week
  frq = inarr(1, 3)
   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, 52)) = 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, 52)) = ""   ' Clear entire row of week entries
  outarr = .Range(.Cells(rowno - 3, 6), .Cells(rowno - 3, 52))
  amnt = inarr(1, 7)   ' amount
  swk = inarr(1, 2)    ' spend week
      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, 52)) = 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 Exit Sub    ***
  .Range(.Cells(rowno - 3, 6), .Cells(rowno - 3, 52)) = ""   ' 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
Application.EnableEvents = True
End Sub

*edit
My modifications to your code are marked with ***. Didn't know you couldn't bold or highlight text within the code tags

Easy way to convert formula into vba/macro?

Akira181

Board Regular

Excel Facts

offthelip

Well-known Member

Akira181

Board Regular

offthelip

Well-known Member

Akira181

Board Regular

Akira181

Board Regular

offthelip

Well-known Member

offthelip

Well-known Member

Akira181

Board Regular

Akira181

Board Regular

Similar threads

Forum statistics

Share this page

Easy way to convert formula into vba/macro?

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Board Regular

Well-known Member

Well-known Member

Board Regular

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock