vba needed to store range but cells in range has formulas, can this be done?

greegan · Jul 19, 2013

HTML:

Excel 2010BCD1IncomePay PeriodFed22153Semi-monthlyCA3537[CENTER][COLOR=#161120][B]Tax Tables PDF Calcs[/B][/COLOR][/CENTER]

B1 refers to Income linked to another worksheet.
C2 is a drop list of four Pay Period choices.
D2 is a drop list with only two choices, CA or OC. CA will be used most often.

Table Base Number

HTML:

Excel 2010ABCDE5XWeeklyBiweeklySemi-MonthlyMonthly6AB36072078015597BC36673179215828CA24849553710729MB18036039078010NB19138141382511NL17234337174112NS17334537374613NT277553599119714NU255508550110015OC248495537107216ON285569617123317PE15631233867418QC000019SK312623675134820YT2484965371073[CENTER][COLOR=#161120][B]Tax Tables PDF Calcs[/B][/COLOR][/CENTER]

There are 58 different tax tables (We are excluding OC and QC for the purpose of this thread, we are including CA representing Canada) and each table has six pages. The first of the six pages begins with a range of numbers (the income must fall within this range), greater than zero (0) and less than... the number specified by the table above. ie looking for the first range for BC's Biweekly table would be 731.

HTML:

Excel 2010BCDE23WeeklyBiweeklySemi-MonthlyMonthly2424482548818268161834271224265228163234702920404486[CENTER][COLOR=#161120][B]Tax Tables PDF Calcs[/B][/COLOR][/CENTER]

Depending on the pay period the ranges on each table (on each of six pages) increase depending on the page you view.
For example on BC's Biweekly table, we might see the starting range of 0-731, because its biweekly, the next range for the rest of the page (a total of 55 rows including the first) will be a difference of 4, so you will see the ranges 0-731,731-735,735-739 and so on. The less than value of each range is actually a value of minus one cent however the table appears as stated. With this said, the next page (line one, page two) will begin with 947-955. I'm hoping you see where this is going.

This is where it gets tricky for me. It works with formulas and the worksheet I have added the ranges to, but I'm hoping someone could tell me how to store all of this in vba (an array or something?).

HTML:

Excel 2010ABCDE32NA0537FALSEFALSE33NA537753FALSEFALSE34NA7531193FALSEFALSE35Base11932183TRUE436NA21833613FALSEFALSE37NA36135483FALSEFALSE38NA54837903FALSEFALSE[CENTER][COLOR=#161120][B]Tax Tables PDF Calcs[/B][/COLOR][/CENTER]

Worksheet FormulasCellFormulaC32=D$3D32=IF(tween(B$2,B$32,C$32-0.01)=TRUE,TRUE,FALSE)E32=IF(D$32=TRUE,COUNTA(D$32:D$32),FALSE)A32=IF(tween(B$2,B32,C32-0.01)=TRUE,"Base","NA")A33=IF(tween(B$2,B33,C33-0.01)=TRUE,"Base","NA")A34=IF(tween(B$2,B34,C34-0.01)=TRUE,"Base","NA")A35=IF(tween(B$2,B35,C35-0.01)=TRUE,"Base","NA")A36=IF(tween(B$2,B36,C36-0.01)=TRUE,"Base","NA")A37=IF(tween(B$2,B37,C37-0.01)=TRUE,"Base","NA")A38=IF(tween(B$2,B38,C38-0.01)=TRUE,"Base","NA")B33=C$32C33=B$33+54*(HLOOKUP(C$2,B$23:E$29,COUNT(B$33:B$33)+1,FALSE))D33=IF(tween(B$2,B$33,C$33-0.01)=TRUE,TRUE,FALSE)E33=IF(D$33=TRUE,COUNTA(D$32:D$33),FALSE)B34=C$33C34=B$34+55*(HLOOKUP(C$2,B$23:E$29,COUNT(B$33:B$34)+1,FALSE))D34=IF(tween(B$2,B$34,C$34-0.01)=TRUE,TRUE,FALSE)E34=IF(D$34=TRUE,COUNTA(D$32:D$34),FALSE)B35=C$34C35=B$35+55*(HLOOKUP(C$2,B$23:E$29,COUNT(B$33:B$35)+1,FALSE))D35=IF(tween($B$2,B$35,C$35-0.01)=TRUE,TRUE,FALSE)E35=IF(D$35=TRUE,COUNTA(D$32:D$35),FALSE)B36=C$35C36=B$36+55*(HLOOKUP(C$2,B$23:E$29,COUNT(B$33:B$36)+1,FALSE))D36=IF(tween(B$2,B$36,C$36-0.01)=TRUE,TRUE,FALSE)E36=IF(D$36=TRUE,COUNTA(D$32:D$36),FALSE)B37=C$36C37=B$37+55*(HLOOKUP(C$2,B$23:E$29,COUNT(B$33:B$37)+1,FALSE))D37=IF(tween(B$2,B$37,C$37-0.01)=TRUE,TRUE,FALSE)E37=IF(D$37=TRUE,COUNTA(D$32:D$37),FALSE)B38=C$37C38=B$38+55*(HLOOKUP(C$2,B$23:E$29,COUNT(B$33:B$38)+1,FALSE))D38=IF(tween(B$2,B$38,C$38-0.01)=TRUE,TRUE,FALSE)E38=IF(D$38=TRUE,COUNTA(D$32:D$38),FALSE)

I'm hoping I've done this HTML correctly

HTML:

Excel 2010ABCDE32NA0537FALSEFALSE33NA537753FALSEFALSE34NA7531193FALSEFALSE35Base11932183TRUE436NA21833613FALSEFALSE37NA36135483FALSEFALSE38NA54837903FALSEFALSE[CENTER][COLOR=#161120][B]Tax Tables PDF Calcs[/B][/COLOR][/CENTER]

This range is using the previous tables to determine what table page the Income falls on, then with the next range (below) which of the 55 lines the income falls within.

HTML:

Excel 2010ABCD4211932147.002165.002156.000431211441229451247461265471283481301491319501337511355521373531391541409551427561445571463581481591499601517611535621553631571641589651607661625671643681661691679701697711715721733731751741769751787761805771823781841791859801877811895821913831931841949851967861985872003882021892039902057912075922093932111942129952147962165[CENTER][COLOR=#161120][B]Tax Tables PDF Calcs[/B][/COLOR][/CENTER]

Worksheet FormulasCellFormulaA42=VLOOKUP("Base",A$32:D$38,2,0)B42=LOOKUP(B$2,A$42:A$96)C42=B42+HLOOKUP(C2,B23:E29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)D42=MEDIAN(B42,C42)A43=IFERROR(A42+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A44=IFERROR(A43+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A45=IFERROR(A44+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A46=IFERROR(A45+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A47=IFERROR(A46+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A48=IFERROR(A47+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A49=IFERROR(A48+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A50=IFERROR(A49+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A51=IFERROR(A50+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A52=IFERROR(A51+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A53=IFERROR(A52+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A54=IFERROR(A53+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A55=IFERROR(A54+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A56=IFERROR(A55+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A57=IFERROR(A56+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A58=IFERROR(A57+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A59=IFERROR(A58+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A60=IFERROR(A59+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A61=IFERROR(A60+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A62=IFERROR(A61+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A63=IFERROR(A62+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A64=IFERROR(A63+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A65=IFERROR(A64+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A66=IFERROR(A65+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A67=IFERROR(A66+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A68=IFERROR(A67+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A69=IFERROR(A68+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A70=IFERROR(A69+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A71=IFERROR(A70+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A72=IFERROR(A71+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A73=IFERROR(A72+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A74=IFERROR(A73+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A75=IFERROR(A74+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A76=IFERROR(A75+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A77=IFERROR(A76+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A78=IFERROR(A77+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A79=IFERROR(A78+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A80=IFERROR(A79+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A81=IFERROR(A80+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A82=IFERROR(A81+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A83=IFERROR(A82+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A84=IFERROR(A83+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A85=IFERROR(A84+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A86=IFERROR(A85+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A87=IFERROR(A86+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A88=IFERROR(A87+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A89=IFERROR(A88+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A90=IFERROR(A89+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A91=IFERROR(A90+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A92=IFERROR(A91+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A93=IFERROR(A92+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A94=IFERROR(A93+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A95=IFERROR(A94+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)A96=IFERROR(A95+(HLOOKUP(C$2,B$23:E$29,VLOOKUP(TRUE,D$32:E$38,2,0),FALSE)),0)

It will first determine the base number for the table page (A42) and run all 55 rows greater than values. Then we find the less than value and find the median value from there. Another macro runs the median value as the income and determines the income tax deducted from source. The macro should be (with our tests it has been) within 2 cents of the tax found on the table.

I need this data stored in an array or something (replacing the worksheet ranges and formulas) so the application is less dependant on a specific worksheet.

Can someon please assist me with this?

Thank you

-- g

sijpie · Jul 23, 2013

I understand nothing of the code you display above, as it is very difficult to read. It isn't any VBA i can recognise...

Anyway, yes you can store the formulas of a range, or write formulas to a range using the Formula or FormulaR1C1 property of a range. Here a small demo :

Sub write_Formulas() Dim rR As Range Set rR = Range("A24:G24") ' the following will put formulas in range _ to sum all rows from row 2 to the row just below the formula. _ This shows how powerfull the R1C1 formula is to fill in a whole _ range with one simple repeating relative formula rR.FormulaR1C1 = "=SUM(R2C:R[-1]C)" Set rR = Range("A26") rR = "Grand Total:" ' put 'standard' type formula in another cell rR.Offset(0, 1).Formula = "=SUM(A24:G24)" Set rR = Nothing End Sub Sub read_Formulas() Dim rR As Range Dim aArray As Variant ' The following will read the formulas in a range into _ an array (variant type). then later the formulas are _ written back to another range. Set rR = Range("A24:G24") aArray = rR.FormulaR1C1 Set rR = Range("J30:P30") rR.FormulaR1C1 = aArray End Sub

greegan · Jul 23, 2013

Thank you for the reply sijpie.
The code included there was HTML? so that my ranges would be compatible with mrexcel.com. I'm understanding now that I did it wrong.
Regarding the ranges. I don't want the ranges to appear on the worksheet at all. I would like to code each and every value into vba so I don't need a reference to that worksheet.
Although I am very likely able to use your suggestion on another application, referencing the cells won't work for this project.
Please ask more questions so I can better explain to attain your skilled assistance.

Thank you

-- g

sijpie · Jul 24, 2013

I don't know how the tables are constructed, but this will create an array with (what I understand) the first elements of the table, depending on the state and period required.

Option Explicit Sub demoTableFill() Dim vTaxTbl As Variant vTaxTbl = GetTable("BC", "BiWeekly") ' print table to sheet Range("A1").Resize(UBound(vTaxTbl, 1), UBound(vTaxTbl, 2)) = vTaxTbl 'of course don't need to put the table on a sheet _ can also use it in memory MsgBox "20th element = " & vTaxTbl(20, 1) End Sub '---------------------------------------------------------------- Function GetTable(sState As String, sPeriod As String) As Variant ' Function sets up values to construct tax table, then calls _ buildTable to fill table. Function returns the table as array '---------------------------------------------------------------- Dim lStart As Long, l2nd As Long, lLast As Long Dim iIncr As Integer Dim aTaxPage() As Long Select Case UCase(sState) Case "BC" Select Case LCase(sPeriod) Case "weekly" lStart = 0 l2nd = 731 iIncr = 4 lLast = 947 Case "biweekly" lStart = 947 l2nd = 955 iIncr = 8 lLast = 1347 Case "semi-monthly" lStart = 950 l2nd = 958 iIncr = 8 lLast = 1347 Case "monthly" lStart = 1120 l2nd = 1136 lLast = 1947 iIncr = 16 End Select Case "CA" Select Case LCase(sPeriod) Case "weekly" lStart = 0 l2nd = 725 iIncr = 4 lLast = 943 Case "biweekly" lStart = 944 l2nd = 955 iIncr = 8 lLast = 1347 Case "semi-monthly" lStart = 950 l2nd = 958 iIncr = 8 lLast = 1347 Case "monthly" lStart = 1120 l2nd = 1136 iIncr = 16 lLast = 1947 End Select ' Case "OC" etc for all states End Select 'put table in aTaxpage buildTable aTaxPage, lStart, l2nd, iIncr, lLast 'return the table GetTable = aTaxPage End Function '------------------------------------------------------------------ Sub buildTable(ByRef aTable() As Long, lStart As Long, _ l2ndVal As Long, iIncr As Integer, lMax As Long) ' Sub to build the tax table into an array. The array is passed by _ reference, meaning that we work with the actual array passed _ and not with a copy. The other terms define the starting, ending _ and increment of the table '------------------------------------------------------------------- Dim li As Long, lj As Long li = (lMax - l2ndVal) / iIncr + 1 'number of elements in the table lj = 2 ' this may have to change, _ I don't know what else is in the table columns ' now size the array in order to fill it. We could resize _ the array for each new entry, but it is more efficient if _ we do it in one: ReDim aTable(1 To li, 1 To lj) ' fill the table. For the 2nd element of each ro I am filling out _ just something to demonstrate. _ As the first two elements are often different, we will fill it out _ seperately, not in the loop aTable(1, 1) = lStart aTable(1, 2) = lj * 3 ' arbitrary value for demo aTable(2, 1) = lStart aTable(2, 2) = lj * 3 ' arbitrary value for demo For li = 3 To UBound(aTable, 1) aTable(li, 1) = aTable(li - 1, 1) + iIncr aTable(li, 2) = lj * 3 ' aTable(li, 3) = etc ' more cases if table has more columns lj = lj + 1 ' for the demo Next li ' now the table is filled End Sub 

Put in a code module in an empty workbook to play with.
Read through the comments to see what is happening

greegan · Jul 24, 2013

This is awesome. I'm not very good at writing vba but I think I actually understand what's been done here. Thank you.

-- g

greegan · Jul 24, 2013

I do expect to have more questions about the code offered here. Thank you again

-- g

sijpie · Jul 24, 2013

feel free

sijpie · Jul 24, 2013

See also my short guide:
http://www.mrexcel.com/forum/excel-...t-guide-better-visual-basic-applications.html

greegan · Jul 24, 2013

can a case be added within a case? reason for this is that with each page of 55 rows, the increments change, they increase for each page.

-- g

sijpie · Jul 25, 2013

My code above shows already the use of select case within another select case. As long as you know what you want to check against, Select Case is ver, very flexible. You can do things like

Code:

Select Case True
   Case iVar > 100
      ....
   Case iVar < 20
      ....
   Case Else
      ....
End Select

The only thing to be aware of is that if one item in the select case is tested OK, then the following items will not be checked

vba needed to store range but cells in range has formulas, can this be done?

greegan

Well-known Member

Excel Facts

sijpie

Well-known Member

greegan

Well-known Member

sijpie

Well-known Member

greegan

Well-known Member

greegan

Well-known Member

sijpie

Well-known Member

sijpie

Well-known Member

greegan

Well-known Member

sijpie

Well-known Member

Similar threads

Forum statistics

Share this page

vba needed to store range but cells in range has formulas, can this be done?

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

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