Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
401k Spreadsheets help
Hello,
WorksheetA (http://www.specialty-risk.com/Spread...WorksheetA.xls) is the worksheet created by QuickBooks. WorksheetB (http://www.specialty-risk.com/Spread...WorksheetB.xls) is a worksheet I use to keep track of monthly info, I also update the SS# and names field manually for now. WorksheetC (http://www.specialty-risk.com/Spread...WorksheetC.xls) is the worksheet required for upload to our 401k company. I would like to match a name/SS# on WorksheetA with the name on WorksheetB, update the fields on WorksheetB, then copy that info to WorksheetC. I can move info from WorksheetB to WorksheetC, I need help going from WorksheetA to WorksheetB. Please any help would be appreciated. Let me know if their are any other questions. Thanks, Kevin Porter |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
401k Spreadsheets help
It woulde be a lot siomplier if you had SS# on worksheet A. Here is the
start of the code. Sub move_data() Const Grss_Comp_Col = "F" Const Grss_Comp_Row = 20 With Sheets("WorksheetA") StartCol = .Range("E1").Column ColCount = StartCol Do While .Cells(1, ColCount) < "TOTAL" MyName = .Cells(1, ColCount) LastName = Trim(Left(MyName, InStr(MyName, ",") - 1)) FirstName = Trim(Mid(MyName, InStr(MyName, ",") + 1)) If InStr(FirstName, " ") 0 Then MiddleName = Trim(Mid(FirstName, InStr(FirstName, " ") + 1)) FirstName = Trim(Left(FirstName, InStr(FirstName, " ") - 1)) Else MiddleName = "" End If Gross = .Cells(Grss_Comp_Row, ColCount).Offset(0, 2) With Sheets("WorksheetB") StartRow = 7 RowCount = StartRow Found = False Do While .Range("A" & RowCount) < "" FirstN = .Range("C" & RowCount) MiddleN = .Range("D" & RowCount) LastN = .Range("B" & RowCount) If FirstName = FirstN And _ MiddleName = MiddleN And _ LastName = LastN Then .Range("F" & RowCount) = Gross Found = True Exit Do End If Loop If Found = False Then MsgBox ("Did not find: " & FirstName & MiddleName & LastName) End If End With ColCount = ColCount + 4 Loop End With End Sub "Kevin Porter" wrote: Hello, WorksheetA (http://www.specialty-risk.com/Spread...WorksheetA.xls) is the worksheet created by QuickBooks. WorksheetB (http://www.specialty-risk.com/Spread...WorksheetB.xls) is a worksheet I use to keep track of monthly info, I also update the SS# and names field manually for now. WorksheetC (http://www.specialty-risk.com/Spread...WorksheetC.xls) is the worksheet required for upload to our 401k company. I would like to match a name/SS# on WorksheetA with the name on WorksheetB, update the fields on WorksheetB, then copy that info to WorksheetC. I can move info from WorksheetB to WorksheetC, I need help going from WorksheetA to WorksheetB. Please any help would be appreciated. Let me know if their are any other questions. Thanks, Kevin Porter |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
401k Spreadsheets help
I left out one line that increments rowcount. see code below
Sub move_data() Const Grss_Comp_Col = "F" Const Grss_Comp_Row = 20 With Sheets("WorksheetA") StartCol = .Range("E1").Column ColCount = StartCol Do While .Cells(1, ColCount) < "TOTAL" MyName = .Cells(1, ColCount) LastName = Trim(Left(MyName, InStr(MyName, ",") - 1)) FirstName = Trim(Mid(MyName, InStr(MyName, ",") + 1)) If InStr(FirstName, " ") 0 Then MiddleName = Trim(Mid(FirstName, InStr(FirstName, " ") + 1)) FirstName = Trim(Left(FirstName, InStr(FirstName, " ") - 1)) Else MiddleName = "" End If Gross = .Cells(Grss_Comp_Row, ColCount).Offset(0, 2) With Sheets("WorksheetB") StartRow = 7 RowCount = StartRow Found = False Do While .Range("A" & RowCount) < "" FirstN = .Range("C" & RowCount) MiddleN = .Range("D" & RowCount) LastN = .Range("B" & RowCount) If FirstName = FirstN And _ MiddleName = MiddleN And _ LastName = LastN Then .Range("F" & RowCount) = Gross Found = True Exit Do End If RowCount = RowCount + 1 Loop If Found = False Then MsgBox ("Did not find: " & FirstName & MiddleName & LastName) End If End With ColCount = ColCount + 4 Loop End With End Sub "Joel" wrote: It woulde be a lot siomplier if you had SS# on worksheet A. Here is the start of the code. Sub move_data() Const Grss_Comp_Col = "F" Const Grss_Comp_Row = 20 With Sheets("WorksheetA") StartCol = .Range("E1").Column ColCount = StartCol Do While .Cells(1, ColCount) < "TOTAL" MyName = .Cells(1, ColCount) LastName = Trim(Left(MyName, InStr(MyName, ",") - 1)) FirstName = Trim(Mid(MyName, InStr(MyName, ",") + 1)) If InStr(FirstName, " ") 0 Then MiddleName = Trim(Mid(FirstName, InStr(FirstName, " ") + 1)) FirstName = Trim(Left(FirstName, InStr(FirstName, " ") - 1)) Else MiddleName = "" End If Gross = .Cells(Grss_Comp_Row, ColCount).Offset(0, 2) With Sheets("WorksheetB") StartRow = 7 RowCount = StartRow Found = False Do While .Range("A" & RowCount) < "" FirstN = .Range("C" & RowCount) MiddleN = .Range("D" & RowCount) LastN = .Range("B" & RowCount) If FirstName = FirstN And _ MiddleName = MiddleN And _ LastName = LastN Then .Range("F" & RowCount) = Gross Found = True Exit Do End If Loop If Found = False Then MsgBox ("Did not find: " & FirstName & MiddleName & LastName) End If End With ColCount = ColCount + 4 Loop End With End Sub "Kevin Porter" wrote: Hello, WorksheetA (http://www.specialty-risk.com/Spread...WorksheetA.xls) is the worksheet created by QuickBooks. WorksheetB (http://www.specialty-risk.com/Spread...WorksheetB.xls) is a worksheet I use to keep track of monthly info, I also update the SS# and names field manually for now. WorksheetC (http://www.specialty-risk.com/Spread...WorksheetC.xls) is the worksheet required for upload to our 401k company. I would like to match a name/SS# on WorksheetA with the name on WorksheetB, update the fields on WorksheetB, then copy that info to WorksheetC. I can move info from WorksheetB to WorksheetC, I need help going from WorksheetA to WorksheetB. Please any help would be appreciated. Let me know if their are any other questions. Thanks, Kevin Porter |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
401k Spreadsheets help
Yes, things would be a lot easier if I could get the SS# on the first one.
It is a file created by Quickbooks, whose report creation leaves something to be desired. All the info I want is in QB, but I cannot create a report that shows me that info in those rows. I would have to create several reports then combine them. (Shaking fist angrily at QuickBook creators). Thanks for the help here. I am getting ready to plug this in, adjust and see where I end up. "Joel" wrote: It woulde be a lot siomplier if you had SS# on worksheet A. Here is the start of the code. Sub move_data() Const Grss_Comp_Col = "F" Const Grss_Comp_Row = 20 With Sheets("WorksheetA") StartCol = .Range("E1").Column ColCount = StartCol Do While .Cells(1, ColCount) < "TOTAL" MyName = .Cells(1, ColCount) LastName = Trim(Left(MyName, InStr(MyName, ",") - 1)) FirstName = Trim(Mid(MyName, InStr(MyName, ",") + 1)) If InStr(FirstName, " ") 0 Then MiddleName = Trim(Mid(FirstName, InStr(FirstName, " ") + 1)) FirstName = Trim(Left(FirstName, InStr(FirstName, " ") - 1)) Else MiddleName = "" End If Gross = .Cells(Grss_Comp_Row, ColCount).Offset(0, 2) With Sheets("WorksheetB") StartRow = 7 RowCount = StartRow Found = False Do While .Range("A" & RowCount) < "" FirstN = .Range("C" & RowCount) MiddleN = .Range("D" & RowCount) LastN = .Range("B" & RowCount) If FirstName = FirstN And _ MiddleName = MiddleN And _ LastName = LastN Then .Range("F" & RowCount) = Gross Found = True Exit Do End If Loop If Found = False Then MsgBox ("Did not find: " & FirstName & MiddleName & LastName) End If End With ColCount = ColCount + 4 Loop End With End Sub "Kevin Porter" wrote: Hello, WorksheetA (http://www.specialty-risk.com/Spread...WorksheetA.xls) is the worksheet created by QuickBooks. WorksheetB (http://www.specialty-risk.com/Spread...WorksheetB.xls) is a worksheet I use to keep track of monthly info, I also update the SS# and names field manually for now. WorksheetC (http://www.specialty-risk.com/Spread...WorksheetC.xls) is the worksheet required for upload to our 401k company. I would like to match a name/SS# on WorksheetA with the name on WorksheetB, update the fields on WorksheetB, then copy that info to WorksheetC. I can move info from WorksheetB to WorksheetC, I need help going from WorksheetA to WorksheetB. Please any help would be appreciated. Let me know if their are any other questions. Thanks, Kevin Porter |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
401k Spreadsheets help
I only did Gross Income. Left the rest of the code for you to write. I
tested this code with the sheets you posted on the webpages and works perfrectly. You may need to change the worksheet names I used (worksheetA and WorksheetB). "Kevin Porter" wrote: Yes, things would be a lot easier if I could get the SS# on the first one. It is a file created by Quickbooks, whose report creation leaves something to be desired. All the info I want is in QB, but I cannot create a report that shows me that info in those rows. I would have to create several reports then combine them. (Shaking fist angrily at QuickBook creators). Thanks for the help here. I am getting ready to plug this in, adjust and see where I end up. "Joel" wrote: It woulde be a lot siomplier if you had SS# on worksheet A. Here is the start of the code. Sub move_data() Const Grss_Comp_Col = "F" Const Grss_Comp_Row = 20 With Sheets("WorksheetA") StartCol = .Range("E1").Column ColCount = StartCol Do While .Cells(1, ColCount) < "TOTAL" MyName = .Cells(1, ColCount) LastName = Trim(Left(MyName, InStr(MyName, ",") - 1)) FirstName = Trim(Mid(MyName, InStr(MyName, ",") + 1)) If InStr(FirstName, " ") 0 Then MiddleName = Trim(Mid(FirstName, InStr(FirstName, " ") + 1)) FirstName = Trim(Left(FirstName, InStr(FirstName, " ") - 1)) Else MiddleName = "" End If Gross = .Cells(Grss_Comp_Row, ColCount).Offset(0, 2) With Sheets("WorksheetB") StartRow = 7 RowCount = StartRow Found = False Do While .Range("A" & RowCount) < "" FirstN = .Range("C" & RowCount) MiddleN = .Range("D" & RowCount) LastN = .Range("B" & RowCount) If FirstName = FirstN And _ MiddleName = MiddleN And _ LastName = LastN Then .Range("F" & RowCount) = Gross Found = True Exit Do End If Loop If Found = False Then MsgBox ("Did not find: " & FirstName & MiddleName & LastName) End If End With ColCount = ColCount + 4 Loop End With End Sub "Kevin Porter" wrote: Hello, WorksheetA (http://www.specialty-risk.com/Spread...WorksheetA.xls) is the worksheet created by QuickBooks. WorksheetB (http://www.specialty-risk.com/Spread...WorksheetB.xls) is a worksheet I use to keep track of monthly info, I also update the SS# and names field manually for now. WorksheetC (http://www.specialty-risk.com/Spread...WorksheetC.xls) is the worksheet required for upload to our 401k company. I would like to match a name/SS# on WorksheetA with the name on WorksheetB, update the fields on WorksheetB, then copy that info to WorksheetC. I can move info from WorksheetB to WorksheetC, I need help going from WorksheetA to WorksheetB. Please any help would be appreciated. Let me know if their are any other questions. Thanks, Kevin Porter |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
401k Spreadsheets help
On Mon, 24 Mar 2008 08:08:04 -0700, Kevin Porter
wrote: Hello, WorksheetA (http://www.specialty-risk.com/Spread...WorksheetA.xls) is the worksheet created by QuickBooks. Kevin: I didn't look at your sheets (it looks like Joel has you covered), but I thought I'd share some information with you. I use QB Enterprise 8.0 and under Reports Employees and Payroll there is a Summarize Payroll Data in Excel option. In the resulting workbook, there is a Data worksheet (you may have to unhide) with all the data in a nice table format for you to use. It may help you eliminate some steps. The downside is that it's horribly slow transferring data from QB to Excel - slower than just dumping a report into Excel. I also don't know if that option is available in all QB versions, so you may not have it -- Dick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
401k Match | Excel Worksheet Functions | |||
401k withdraw calculation | Excel Programming | |||
Calculating a tiered 401k Match | Excel Worksheet Functions | |||
401k formula | Excel Worksheet Functions | |||
Help! 401k match formula | New Users to Excel |