| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I can't get this to work
I get a runtime error 9 but it should work, its worked in the past.
-------------------------------------------------------------------------------------------- Dim CDetail As String CDetail = "ClaimsDetail.xls" Msg1 = "Enter the NDC_Quarter (Excel file name)" Dfl1 = "00065-0266-25_20041" MyInput1 = InputBox(Msg1, , Dfl1) If MyInput1 = Cancel Then GoTo LastLine Workbooks.Open ("W:\Exports\" & MyInput1 & ".xls") Workbooks(CDetail).Activate c = 1 Do While Windows(MyInput1).RangeSelection(1, c) < 0 If Windows(MyInput1).RangeSelection(1, c) = "orig_qtr_pd" Then c1 = c If Windows(MyInput1).RangeSelection(1, c) = "dte_quarter" Then c2 = c If Windows(MyInput1).RangeSelection(1, c) = "id_provider" Then c3 = c If Windows(MyInput1).RangeSelection(1, c) = "dte_dispensed" Then c4 = c If Windows(MyInput1).RangeSelection(1, c) = "cde_icn" Then c5 = c If Windows(MyInput1).RangeSelection(1, c) = "num_prescrip" Then c6 = c If Windows(MyInput1).RangeSelection(1, c) = "qty_units_billed" Then c7 = c If Windows(MyInput1).RangeSelection(1, c) = "hcfa_units" Then c8 = c If Windows(MyInput1).RangeSelection(1, c) = "amt_alwd" Then c9 = c If Windows(MyInput1).RangeSelection(1, c) = "amt_reimbursement" Then c10 = c c = c + 1 Loop r = 8 Do While Windows(MyInput1).RangeSelection(r - 6, 1) < 0 Cells(r, 1) = Left(Windows(MyInput1).RangeSelection(r - 6, c1), 4) & "/" & Right(Windows(MyInput1).RangeSelection(r - 6, c1), 1) Cells(r, 2) = Left(Windows(MyInput1).RangeSelection(r - 6, c2), 4) & "/" & Right(Windows(MyInput1).RangeSelection(r - 6, c2), 1) Cells(r, 3) = Windows(MyInput1).RangeSelection(r - 6, c3) Cells(r, 4) = Left(Windows(MyInput1).RangeSelection(r - 6, c4), 4) & "/" & Right(Left(Windows(MyInput1).RangeSelection(r - 6, c4), 6), 2) & "/" & Right(Windows(MyInput1).RangeSelection(r - 6, c4), 2) Cells(r, 5) = Windows(MyInput1).RangeSelection(r - 6, c5) Cells(r, 6) = Windows(MyInput1).RangeSelection(r - 6, c6) Cells(r, 7) = Windows(MyInput1).RangeSelection(r - 6, c7) Cells(r, 8) = Windows(MyInput1).RangeSelection(r - 6, c8) Cells(r, 11) = Windows(MyInput1).RangeSelection(r - 6, c9) Cells(r, 12) = Windows(MyInput1).RangeSelection(r - 6, c10) r = r + 1 Loop Workbooks(MyInput1).Close Cells(1, 1) = "W:\ClaimsDetail" Cells(2, 1) = MyInput1 & "_OK.xls" Cells(1, 12) = Date Cells(2, 12) = Time Cells(5, 1) = "NDC: " & Left(MyInput1, 13) Cells(6, 1) = "Quarter: " & Cells(8, 1) Cells(r + 1, 6) = "Total:" Cells(r + 1, 7) = "=Sum(G8:G" & r - 1 & ")" Cells(r + 1, 8) = "=Sum(H8:H" & r - 1 & ")" Cells(r + 1, 11) = "=Sum(K8:K" & r - 1 & ")" Cells(r + 1, 12) = "=Sum(L8:L" & r - 1 & ")" Range(Cells(r + 1, 6), Cells(r + 1, 12)).Font.Bold = True Range(Cells(r + 1, 6), Cells(r + 1, 12)).Borders(xlEdgeTop).LineStyle = xlContinuous Msg3 = "Do you want to save this Excel file?" Stl3 = vbYesNo Message = MsgBox(Msg3, Stl3) If Message = vbYes Then Workbooks("ClaimsDetail").SaveCopyAs "W:\ClaimsDetail\" & MyInput1 & "_OK.xls" End If Windows("ClaimsDetail").Close SaveChanges:=False LastLine: End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I can't get this to work
Which line generates the error...
-- HTH... Jim Thomlinson "E-Town" wrote: I get a runtime error 9 but it should work, its worked in the past. -------------------------------------------------------------------------------------------- Dim CDetail As String CDetail = "ClaimsDetail.xls" Msg1 = "Enter the NDC_Quarter (Excel file name)" Dfl1 = "00065-0266-25_20041" MyInput1 = InputBox(Msg1, , Dfl1) If MyInput1 = Cancel Then GoTo LastLine Workbooks.Open ("W:\Exports\" & MyInput1 & ".xls") Workbooks(CDetail).Activate c = 1 Do While Windows(MyInput1).RangeSelection(1, c) < 0 If Windows(MyInput1).RangeSelection(1, c) = "orig_qtr_pd" Then c1 = c If Windows(MyInput1).RangeSelection(1, c) = "dte_quarter" Then c2 = c If Windows(MyInput1).RangeSelection(1, c) = "id_provider" Then c3 = c If Windows(MyInput1).RangeSelection(1, c) = "dte_dispensed" Then c4 = c If Windows(MyInput1).RangeSelection(1, c) = "cde_icn" Then c5 = c If Windows(MyInput1).RangeSelection(1, c) = "num_prescrip" Then c6 = c If Windows(MyInput1).RangeSelection(1, c) = "qty_units_billed" Then c7 = c If Windows(MyInput1).RangeSelection(1, c) = "hcfa_units" Then c8 = c If Windows(MyInput1).RangeSelection(1, c) = "amt_alwd" Then c9 = c If Windows(MyInput1).RangeSelection(1, c) = "amt_reimbursement" Then c10 = c c = c + 1 Loop r = 8 Do While Windows(MyInput1).RangeSelection(r - 6, 1) < 0 Cells(r, 1) = Left(Windows(MyInput1).RangeSelection(r - 6, c1), 4) & "/" & Right(Windows(MyInput1).RangeSelection(r - 6, c1), 1) Cells(r, 2) = Left(Windows(MyInput1).RangeSelection(r - 6, c2), 4) & "/" & Right(Windows(MyInput1).RangeSelection(r - 6, c2), 1) Cells(r, 3) = Windows(MyInput1).RangeSelection(r - 6, c3) Cells(r, 4) = Left(Windows(MyInput1).RangeSelection(r - 6, c4), 4) & "/" & Right(Left(Windows(MyInput1).RangeSelection(r - 6, c4), 6), 2) & "/" & Right(Windows(MyInput1).RangeSelection(r - 6, c4), 2) Cells(r, 5) = Windows(MyInput1).RangeSelection(r - 6, c5) Cells(r, 6) = Windows(MyInput1).RangeSelection(r - 6, c6) Cells(r, 7) = Windows(MyInput1).RangeSelection(r - 6, c7) Cells(r, 8) = Windows(MyInput1).RangeSelection(r - 6, c8) Cells(r, 11) = Windows(MyInput1).RangeSelection(r - 6, c9) Cells(r, 12) = Windows(MyInput1).RangeSelection(r - 6, c10) r = r + 1 Loop Workbooks(MyInput1).Close Cells(1, 1) = "W:\ClaimsDetail" Cells(2, 1) = MyInput1 & "_OK.xls" Cells(1, 12) = Date Cells(2, 12) = Time Cells(5, 1) = "NDC: " & Left(MyInput1, 13) Cells(6, 1) = "Quarter: " & Cells(8, 1) Cells(r + 1, 6) = "Total:" Cells(r + 1, 7) = "=Sum(G8:G" & r - 1 & ")" Cells(r + 1, 8) = "=Sum(H8:H" & r - 1 & ")" Cells(r + 1, 11) = "=Sum(K8:K" & r - 1 & ")" Cells(r + 1, 12) = "=Sum(L8:L" & r - 1 & ")" Range(Cells(r + 1, 6), Cells(r + 1, 12)).Font.Bold = True Range(Cells(r + 1, 6), Cells(r + 1, 12)).Borders(xlEdgeTop).LineStyle = xlContinuous Msg3 = "Do you want to save this Excel file?" Stl3 = vbYesNo Message = MsgBox(Msg3, Stl3) If Message = vbYes Then Workbooks("ClaimsDetail").SaveCopyAs "W:\ClaimsDetail\" & MyInput1 & "_OK.xls" End If Windows("ClaimsDetail").Close SaveChanges:=False LastLine: End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I can't get this to work
Sorry about that, its this one:
----------------- Workbooks(CDetail).Activate ----------------- I've tried Windows(CDetail).Activate but same result. There's a known issue with default printers but nobody can give me a fix, so I thought there might be a coding workaround. thanks, "Jim Thomlinson" wrote: Which line generates the error... -- HTH... Jim Thomlinson "E-Town" wrote: I get a runtime error 9 but it should work, its worked in the past. -------------------------------------------------------------------------------------------- Dim CDetail As String CDetail = "ClaimsDetail.xls" Msg1 = "Enter the NDC_Quarter (Excel file name)" Dfl1 = "00065-0266-25_20041" MyInput1 = InputBox(Msg1, , Dfl1) If MyInput1 = Cancel Then GoTo LastLine Workbooks.Open ("W:\Exports\" & MyInput1 & ".xls") Workbooks(CDetail).Activate c = 1 Do While Windows(MyInput1).RangeSelection(1, c) < 0 If Windows(MyInput1).RangeSelection(1, c) = "orig_qtr_pd" Then c1 = c If Windows(MyInput1).RangeSelection(1, c) = "dte_quarter" Then c2 = c If Windows(MyInput1).RangeSelection(1, c) = "id_provider" Then c3 = c If Windows(MyInput1).RangeSelection(1, c) = "dte_dispensed" Then c4 = c If Windows(MyInput1).RangeSelection(1, c) = "cde_icn" Then c5 = c If Windows(MyInput1).RangeSelection(1, c) = "num_prescrip" Then c6 = c If Windows(MyInput1).RangeSelection(1, c) = "qty_units_billed" Then c7 = c If Windows(MyInput1).RangeSelection(1, c) = "hcfa_units" Then c8 = c If Windows(MyInput1).RangeSelection(1, c) = "amt_alwd" Then c9 = c If Windows(MyInput1).RangeSelection(1, c) = "amt_reimbursement" Then c10 = c c = c + 1 Loop r = 8 Do While Windows(MyInput1).RangeSelection(r - 6, 1) < 0 Cells(r, 1) = Left(Windows(MyInput1).RangeSelection(r - 6, c1), 4) & "/" & Right(Windows(MyInput1).RangeSelection(r - 6, c1), 1) Cells(r, 2) = Left(Windows(MyInput1).RangeSelection(r - 6, c2), 4) & "/" & Right(Windows(MyInput1).RangeSelection(r - 6, c2), 1) Cells(r, 3) = Windows(MyInput1).RangeSelection(r - 6, c3) Cells(r, 4) = Left(Windows(MyInput1).RangeSelection(r - 6, c4), 4) & "/" & Right(Left(Windows(MyInput1).RangeSelection(r - 6, c4), 6), 2) & "/" & Right(Windows(MyInput1).RangeSelection(r - 6, c4), 2) Cells(r, 5) = Windows(MyInput1).RangeSelection(r - 6, c5) Cells(r, 6) = Windows(MyInput1).RangeSelection(r - 6, c6) Cells(r, 7) = Windows(MyInput1).RangeSelection(r - 6, c7) Cells(r, 8) = Windows(MyInput1).RangeSelection(r - 6, c8) Cells(r, 11) = Windows(MyInput1).RangeSelection(r - 6, c9) Cells(r, 12) = Windows(MyInput1).RangeSelection(r - 6, c10) r = r + 1 Loop Workbooks(MyInput1).Close Cells(1, 1) = "W:\ClaimsDetail" Cells(2, 1) = MyInput1 & "_OK.xls" Cells(1, 12) = Date Cells(2, 12) = Time Cells(5, 1) = "NDC: " & Left(MyInput1, 13) Cells(6, 1) = "Quarter: " & Cells(8, 1) Cells(r + 1, 6) = "Total:" Cells(r + 1, 7) = "=Sum(G8:G" & r - 1 & ")" Cells(r + 1, 8) = "=Sum(H8:H" & r - 1 & ")" Cells(r + 1, 11) = "=Sum(K8:K" & r - 1 & ")" Cells(r + 1, 12) = "=Sum(L8:L" & r - 1 & ")" Range(Cells(r + 1, 6), Cells(r + 1, 12)).Font.Bold = True Range(Cells(r + 1, 6), Cells(r + 1, 12)).Borders(xlEdgeTop).LineStyle = xlContinuous Msg3 = "Do you want to save this Excel file?" Stl3 = vbYesNo Message = MsgBox(Msg3, Stl3) If Message = vbYes Then Workbooks("ClaimsDetail").SaveCopyAs "W:\ClaimsDetail\" & MyInput1 & "_OK.xls" End If Windows("ClaimsDetail").Close SaveChanges:=False LastLine: End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I can't get this to work
Do you have a file called ClaimsDetail.xls open when this line of code
executes? Is ClaimsDetail.xls the workbook that is executing the code? -- HTH... Jim Thomlinson "E-Town" wrote: Sorry about that, its this one: ----------------- Workbooks(CDetail).Activate ----------------- I've tried Windows(CDetail).Activate but same result. There's a known issue with default printers but nobody can give me a fix, so I thought there might be a coding workaround. thanks, "Jim Thomlinson" wrote: Which line generates the error... -- HTH... Jim Thomlinson "E-Town" wrote: I get a runtime error 9 but it should work, its worked in the past. -------------------------------------------------------------------------------------------- Dim CDetail As String CDetail = "ClaimsDetail.xls" Msg1 = "Enter the NDC_Quarter (Excel file name)" Dfl1 = "00065-0266-25_20041" MyInput1 = InputBox(Msg1, , Dfl1) If MyInput1 = Cancel Then GoTo LastLine Workbooks.Open ("W:\Exports\" & MyInput1 & ".xls") Workbooks(CDetail).Activate c = 1 Do While Windows(MyInput1).RangeSelection(1, c) < 0 If Windows(MyInput1).RangeSelection(1, c) = "orig_qtr_pd" Then c1 = c If Windows(MyInput1).RangeSelection(1, c) = "dte_quarter" Then c2 = c If Windows(MyInput1).RangeSelection(1, c) = "id_provider" Then c3 = c If Windows(MyInput1).RangeSelection(1, c) = "dte_dispensed" Then c4 = c If Windows(MyInput1).RangeSelection(1, c) = "cde_icn" Then c5 = c If Windows(MyInput1).RangeSelection(1, c) = "num_prescrip" Then c6 = c If Windows(MyInput1).RangeSelection(1, c) = "qty_units_billed" Then c7 = c If Windows(MyInput1).RangeSelection(1, c) = "hcfa_units" Then c8 = c If Windows(MyInput1).RangeSelection(1, c) = "amt_alwd" Then c9 = c If Windows(MyInput1).RangeSelection(1, c) = "amt_reimbursement" Then c10 = c c = c + 1 Loop r = 8 Do While Windows(MyInput1).RangeSelection(r - 6, 1) < 0 Cells(r, 1) = Left(Windows(MyInput1).RangeSelection(r - 6, c1), 4) & "/" & Right(Windows(MyInput1).RangeSelection(r - 6, c1), 1) Cells(r, 2) = Left(Windows(MyInput1).RangeSelection(r - 6, c2), 4) & "/" & Right(Windows(MyInput1).RangeSelection(r - 6, c2), 1) Cells(r, 3) = Windows(MyInput1).RangeSelection(r - 6, c3) Cells(r, 4) = Left(Windows(MyInput1).RangeSelection(r - 6, c4), 4) & "/" & Right(Left(Windows(MyInput1).RangeSelection(r - 6, c4), 6), 2) & "/" & Right(Windows(MyInput1).RangeSelection(r - 6, c4), 2) Cells(r, 5) = Windows(MyInput1).RangeSelection(r - 6, c5) Cells(r, 6) = Windows(MyInput1).RangeSelection(r - 6, c6) Cells(r, 7) = Windows(MyInput1).RangeSelection(r - 6, c7) Cells(r, 8) = Windows(MyInput1).RangeSelection(r - 6, c8) Cells(r, 11) = Windows(MyInput1).RangeSelection(r - 6, c9) Cells(r, 12) = Windows(MyInput1).RangeSelection(r - 6, c10) r = r + 1 Loop Workbooks(MyInput1).Close Cells(1, 1) = "W:\ClaimsDetail" Cells(2, 1) = MyInput1 & "_OK.xls" Cells(1, 12) = Date Cells(2, 12) = Time Cells(5, 1) = "NDC: " & Left(MyInput1, 13) Cells(6, 1) = "Quarter: " & Cells(8, 1) Cells(r + 1, 6) = "Total:" Cells(r + 1, 7) = "=Sum(G8:G" & r - 1 & ")" Cells(r + 1, 8) = "=Sum(H8:H" & r - 1 & ")" Cells(r + 1, 11) = "=Sum(K8:K" & r - 1 & ")" Cells(r + 1, 12) = "=Sum(L8:L" & r - 1 & ")" Range(Cells(r + 1, 6), Cells(r + 1, 12)).Font.Bold = True Range(Cells(r + 1, 6), Cells(r + 1, 12)).Borders(xlEdgeTop).LineStyle = xlContinuous Msg3 = "Do you want to save this Excel file?" Stl3 = vbYesNo Message = MsgBox(Msg3, Stl3) If Message = vbYes Then Workbooks("ClaimsDetail").SaveCopyAs "W:\ClaimsDetail\" & MyInput1 & "_OK.xls" End If Windows("ClaimsDetail").Close SaveChanges:=False LastLine: End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I can't get this to work
That means there is no open workbook named ClaimsDetail.xls.
Maybe it's a simple typo??? E-Town wrote: Sorry about that, its this one: ----------------- Workbooks(CDetail).Activate ----------------- I've tried Windows(CDetail).Activate but same result. There's a known issue with default printers but nobody can give me a fix, so I thought there might be a coding workaround. thanks, "Jim Thomlinson" wrote: Which line generates the error... -- HTH... Jim Thomlinson "E-Town" wrote: I get a runtime error 9 but it should work, its worked in the past. -------------------------------------------------------------------------------------------- Dim CDetail As String CDetail = "ClaimsDetail.xls" Msg1 = "Enter the NDC_Quarter (Excel file name)" Dfl1 = "00065-0266-25_20041" MyInput1 = InputBox(Msg1, , Dfl1) If MyInput1 = Cancel Then GoTo LastLine Workbooks.Open ("W:\Exports\" & MyInput1 & ".xls") Workbooks(CDetail).Activate c = 1 Do While Windows(MyInput1).RangeSelection(1, c) < 0 If Windows(MyInput1).RangeSelection(1, c) = "orig_qtr_pd" Then c1 = c If Windows(MyInput1).RangeSelection(1, c) = "dte_quarter" Then c2 = c If Windows(MyInput1).RangeSelection(1, c) = "id_provider" Then c3 = c If Windows(MyInput1).RangeSelection(1, c) = "dte_dispensed" Then c4 = c If Windows(MyInput1).RangeSelection(1, c) = "cde_icn" Then c5 = c If Windows(MyInput1).RangeSelection(1, c) = "num_prescrip" Then c6 = c If Windows(MyInput1).RangeSelection(1, c) = "qty_units_billed" Then c7 = c If Windows(MyInput1).RangeSelection(1, c) = "hcfa_units" Then c8 = c If Windows(MyInput1).RangeSelection(1, c) = "amt_alwd" Then c9 = c If Windows(MyInput1).RangeSelection(1, c) = "amt_reimbursement" Then c10 = c c = c + 1 Loop r = 8 Do While Windows(MyInput1).RangeSelection(r - 6, 1) < 0 Cells(r, 1) = Left(Windows(MyInput1).RangeSelection(r - 6, c1), 4) & "/" & Right(Windows(MyInput1).RangeSelection(r - 6, c1), 1) Cells(r, 2) = Left(Windows(MyInput1).RangeSelection(r - 6, c2), 4) & "/" & Right(Windows(MyInput1).RangeSelection(r - 6, c2), 1) Cells(r, 3) = Windows(MyInput1).RangeSelection(r - 6, c3) Cells(r, 4) = Left(Windows(MyInput1).RangeSelection(r - 6, c4), 4) & "/" & Right(Left(Windows(MyInput1).RangeSelection(r - 6, c4), 6), 2) & "/" & Right(Windows(MyInput1).RangeSelection(r - 6, c4), 2) Cells(r, 5) = Windows(MyInput1).RangeSelection(r - 6, c5) Cells(r, 6) = Windows(MyInput1).RangeSelection(r - 6, c6) Cells(r, 7) = Windows(MyInput1).RangeSelection(r - 6, c7) Cells(r, 8) = Windows(MyInput1).RangeSelection(r - 6, c8) Cells(r, 11) = Windows(MyInput1).RangeSelection(r - 6, c9) Cells(r, 12) = Windows(MyInput1).RangeSelection(r - 6, c10) r = r + 1 Loop Workbooks(MyInput1).Close Cells(1, 1) = "W:\ClaimsDetail" Cells(2, 1) = MyInput1 & "_OK.xls" Cells(1, 12) = Date Cells(2, 12) = Time Cells(5, 1) = "NDC: " & Left(MyInput1, 13) Cells(6, 1) = "Quarter: " & Cells(8, 1) Cells(r + 1, 6) = "Total:" Cells(r + 1, 7) = "=Sum(G8:G" & r - 1 & ")" Cells(r + 1, 8) = "=Sum(H8:H" & r - 1 & ")" Cells(r + 1, 11) = "=Sum(K8:K" & r - 1 & ")" Cells(r + 1, 12) = "=Sum(L8:L" & r - 1 & ")" Range(Cells(r + 1, 6), Cells(r + 1, 12)).Font.Bold = True Range(Cells(r + 1, 6), Cells(r + 1, 12)).Borders(xlEdgeTop).LineStyle = xlContinuous Msg3 = "Do you want to save this Excel file?" Stl3 = vbYesNo Message = MsgBox(Msg3, Stl3) If Message = vbYes Then Workbooks("ClaimsDetail").SaveCopyAs "W:\ClaimsDetail\" & MyInput1 & "_OK.xls" End If Windows("ClaimsDetail").Close SaveChanges:=False LastLine: End Sub -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how can i automatically generate work order numbers from work orde | Excel Discussion (Misc queries) | |||
| HOW TO MAKE A LIST OF WORK SHEET IN WORK BOOK IN EXCEL 2007 | Excel Programming | |||
| If I have a work sheet protected and try to run a macro to hide rows or columns it won't work. Correct? | Excel Programming | |||
| Counting dates in multiple work sheets and work books | Excel Discussion (Misc queries) | |||
| Is there away to keep "auto save" from jumping to the first work sheet in the work book? | New Users to Excel | |||
Linear Mode
