INDEX function works in cell but not in VBA

[MileHiMark]

Hello all,

I've been working on a payroll program for my small biz. I'm close to done BUT...
When I use the Index Match function in a cell it works great. However I need to copy it down a column to work for pay periods going forward. When I copy it down of course it puts zeros or N/A.

Columns:

Gross Fed FICA Medicare State Net
F G H I J K


Gross is user input. FICA Medicare State and Net are simply calculated on the sheet. But to get Fed Income tax I use the Index Match function and it works perfectly. The rows increment properly with each new user input.

Code:

=INDEX(SingleWH!C$6:M$140,MATCH(G7,SingleWH!A$6:A$140,1),MATCH(E$1,SingleWH!C$4:M$4,0))

This is the code in Column F. You can see where it uses the result in Column G to look for a value in sheets("SingleWH").

My question: Fed Income tax is dependent on what the Gross is in order to look up the tables with the Index Match function. How do I get the result of the function into column G? Can I increment up Column G and ignore the zeros that dragging the function down G creates?
I'm sure there are other ways to do what I need as well.



Any and all input would be greatly appreciated by this brand new <acronym title="visual basic for applications">VBA</acronym> guy.

 

[Andrew Poulsom]

I don't understand where VBA comes into your scenario. Also how can the formula be in column G when it references G7?

 

[MileHiMark]

Sorry, good eye. The formula is in G but refernces F. I've been trying VBA because I can't get the result of the formula into column G. I know I'm trying to do something simple and am making it very hard.

1. User inputs hours
2. employee sheet updates all taxes based on hours times rate (gross)
3. quick calc on Federal tax not possible - must use index to lookup on tables
4. User input goes to last unused row.
5. Easy to put Fed Tax in first G cell along with hours and other taxes.
6. Don't know how to put Fed tax in g2, g3, g4 etc as user inputs new hours for new period. Say row 3 is payperiod 03/24/2013. Hours entered
equals 40 everything on row 3 calculates correctly. Fed Tax is looked up and put in G3 because formula is in G3.

Next pay period. Everything is now on row 4. How do I get the Fed Tax on row 4? I could drag the formula down the G column but then the lastRow code would not put last row where I need it. Last row would go 1 row below where I dragged the formula.

 

[Andrew Poulsom]

How are you determining the last row?

 

[MileHiMark]

ADVERTISEMENT

Code for determing last row:

LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

Different code I've used to get what I need done:

Cells(LastRow, 7).Formula = "=Index("SingleWH!C$6:M$140"),Match(Worksheets("TempSheet").Range("LastRow:6"),("SingleWH!A$6:A$140"), 1), Match("TempSheet!C$1"), Worksheets("SingleWH!C$4:M$4,0")))"

Cells(LastRow, 7) = Application.WorksheetFunction.Index(Worksheets("SingleWH").Range("C$6:M$140"),.Match(Worksheets("TempSheet").Range("LastRow:6"), Worksheets("SingleWH").Range("A$6:A$140"), 1),.Match(Worksheets("TempSheet").Range("C$1"), Worksheets("SingleWH").Range("C$4:M$4,0")))

 

[Andrew Poulsom]

Try using:

LastRow = Range("A" & Rows.Count).End(xlUp).Row + 1

 

[MileHiMark]

ADVERTISEMENT

The row is not incrementing. Next input writes over old input ie everything keeps going on row 2.

 

[Andrew Poulsom]

Where are the hours being put? If it's not column A change the range reference to that column.

 

[MileHiMark]

Perfect! Thanks Andrew. Could you tell me why that works compared to the first last row formula I used?