# Easy way to convert formula into vba/macro?

#### Akira181

##### Board Regular
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.

### Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Almost any formula can be converted into VBA, it really depends on what the formula is as to how hard it is.

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

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)),)
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``````

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)),)
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!

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)),)
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
ABCDEFGHIJKL
1
2
3
4
5
6ABCDSpend TypeSpend WeekFrequencyEFGCost
7
8Entry 1Recurring14£ 4,000.00
9Entry 2Single2£ 2,500.00
10Entry 3Manual Input£ 300.00
11Entry 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
ABCDEFGHIJ
1
2MonthJanJanJanJanFeb
3ABSub TotalWK.12345
4
5Entry 1(recurring cost)£ 4,000.00£ 1,000.00£ 1,000.00£ 1,000.00£ 1,000.00£ -
6Entry 2(single cost)£ 2,500.00£ -£ 2,500.00£ -£ -£ -
7Entry 3(manual input)£ 300.00£ -£ 50.00£ -£ 200.00£ 50.00
8Entry X
Sheet2
Cell Formulas
RangeFormula
F2:J2F2=TEXT(DATE(YEAR(NOW()),1,F3*7-2),"mmm")
I5:J6,H5:H7,F5:F7,G5:G6I5=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:D7D5=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

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??

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``````

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

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

Replies
3
Views
115
Replies
0
Views
157
Replies
11
Views
310
Replies
1
Views
76
Replies
2
Views
156

### Forum statistics

1,203,460
Messages
6,055,556
Members
444,797
Latest member
18ecooley ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back