Posts by Bubbis Thedog

    Re: [Microsoft][ODBC Microsoft Access Driver]Number value out of range (null)


    Thanks for the response, Will. Below are both Access queries that I want to retrieve data from in Excel, beginning with the one that 'works.' They're monsters, but the only differences between them are the final WHERE clause.


    Quote


    SELECT AGT_PROF_BB_20051031.AGENT, AGT_PROF_BB_20051031.YEARBOOK, AGT_PROF_BB_20051031.AGENTNAME, AGT_PROF_BB_20051031.AGNTTYPE, AGT_PROF_BB_20051031.AGNTSTATUS, AGT_PROF_BB_20051031.CONTRTS AS ContVol, AGT_PROF_BB_20051031.AMOUNTFIN AS AmtFin, AGT_PROF_BB_20051031.PRPFEARN AS PRPF_Earn, AGT_PROF_BB_20051031.RPMEARN, AGT_PROF_BB_20051031.[COMMISS$] AS Commission, (COFandVOC.COF_Perc*AGT_PROF_BB_20051031.[TOTARAVG$]*(-1)) AS COF, (PRPF_Earn+COF) AS Net_Int_Margin, AGT_PROF_BB_20051031.BADDEBT AS Bad_Debt, (Net_Int_Margin+Bad_Debt) AS Credit_Margin, (COFandVOC.Var_Op_Cost*ContVol*(-1)) AS VarOpCost, (Credit_Margin+VarOpCost) AS Net_Earnings, (AGT_PROF_BB_20051031.CANCELS/ContVol) AS CXLPerc, ((-1)*(Bad_Debt/(PRPF_Earn+AGT_PROF_BB_20051031.RPMEARN+Commission))) AS Loss_Ratio, ((-1)*(Bad_Debt/AGT_PROF_BB_20051031.[TOTARAVG$])) AS Charge_Off_Perc, (AmtFin/ContVol) AS AvgAmtFin, AGT_PROF_BB_20051031.PL06CONT, AGT_PROF_BB_20051031.PL06CNCL, AGT_PROF_BB_20051031.[PL06PREM$], AGT_PROF_BB_20051031.[PL06DPAY$], AGT_PROF_BB_20051031.PL06AMFN, AGT_PROF_BB_20051031.[PL06EARN$], AGT_PROF_BB_20051031.[PL06LOSS$], AGT_PROF_BB_20051031.[PL06FLAG#], AGT_PROF_BB_20051031.[PL06FLAG$], AGT_PROF_BB_20051031.[PL06BILL#], AGT_PROF_BB_20051031.[PL06BILL$], AGT_PROF_BB_20051031.[PL06ARBL#], AGT_PROF_BB_20051031.[PL06ARBL$], AGT_PROF_BB_20051031.PL12CONT, AGT_PROF_BB_20051031.PL12CNCL, AGT_PROF_BB_20051031.[PL12PREM$], AGT_PROF_BB_20051031.[PL12DPAY$], AGT_PROF_BB_20051031.PL12AMFN, AGT_PROF_BB_20051031.[PL12EARN$], AGT_PROF_BB_20051031.[PL12LOSS$], AGT_PROF_BB_20051031.[PL12FLAG#], AGT_PROF_BB_20051031.[PL12FLAG$], AGT_PROF_BB_20051031.[PL12BILL#], AGT_PROF_BB_20051031.[PL12BILL$], AGT_PROF_BB_20051031.[PL12ARBL#], AGT_PROF_BB_20051031.[PL12ARBL$], AGT_PROF_BB_20051031.PLCMCONT, AGT_PROF_BB_20051031.PLCMCNCL, AGT_PROF_BB_20051031.[PLCMPREM$], AGT_PROF_BB_20051031.[PLCMDPAY$], AGT_PROF_BB_20051031.PLCMAMFN, AGT_PROF_BB_20051031.[PLCMEARN$], AGT_PROF_BB_20051031.[PLCMLOSS$], AGT_PROF_BB_20051031.[PLCMFLAG#], AGT_PROF_BB_20051031.[PLCMFLAG$], AGT_PROF_BB_20051031.[PLCMBILL#], AGT_PROF_BB_20051031.[PLCMBILL$], AGT_PROF_BB_20051031.[PLCMARBL#], AGT_PROF_BB_20051031.[PLCMARBL$], AGT_PROF_BB_20051031.FLDREP, AGT_PROF_BB_20051031.FLDREPNME, AGT_PROF_BB_20051031.[T06ARBAL#], AGT_PROF_BB_20051031.[T06ARBAL$], AGT_PROF_BB_20051031.[T06ARAVG$], AGT_PROF_BB_20051031.[T12ARBAL#], AGT_PROF_BB_20051031.[T12ARBAL$], AGT_PROF_BB_20051031.[T12ARAVG$], AGT_PROF_BB_20051031.[TCMARBAL#], AGT_PROF_BB_20051031.[TCMARBAL$], AGT_PROF_BB_20051031.[TCMARAVG$], AGT_PROF_BB_20051031.[TOTARBAL#], AGT_PROF_BB_20051031.[TOTARBAL$], AGT_PROF_BB_20051031.[TOTARAVG$]
    FROM AGT_PROF_BB_20051031 LEFT JOIN COFandVOC ON AGT_PROF_BB_20051031.YEARBOOK = COFandVOC.Year
    WHERE (((AGT_PROF_BB_20051031.AGNTTYPE)="B" Or (AGT_PROF_BB_20051031.AGNTTYPE)="M"));


    Quote


    SELECT AGT_PROF_BB_20051031.AGENT, AGT_PROF_BB_20051031.YEARBOOK, AGT_PROF_BB_20051031.AGENTNAME, AGT_PROF_BB_20051031.AGNTTYPE, AGT_PROF_BB_20051031.AGNTSTATUS, AGT_PROF_BB_20051031.CONTRTS AS ContVol, AGT_PROF_BB_20051031.AMOUNTFIN AS AmtFin, AGT_PROF_BB_20051031.PRPFEARN AS PRPF_Earn, AGT_PROF_BB_20051031.RPMEARN, AGT_PROF_BB_20051031.[COMMISS$] AS Commission, (COFandVOC.COF_Perc*AGT_PROF_BB_20051031.[TOTARAVG$]*(-1)) AS COF, (PRPF_Earn+COF) AS Net_Int_Margin, AGT_PROF_BB_20051031.BADDEBT AS Bad_Debt, (Net_Int_Margin+Bad_Debt) AS Credit_Margin, (COFandVOC.Var_Op_Cost*ContVol*(-1)) AS VarOpCost, (Credit_Margin+VarOpCost) AS Net_Earnings, (AGT_PROF_BB_20051031.CANCELS/ContVol) AS CXLPerc, ((-1)*(Bad_Debt/(PRPF_Earn+AGT_PROF_BB_20051031.RPMEARN+Commission))) AS Loss_Ratio, ((-1)*(Bad_Debt/AGT_PROF_BB_20051031.[TOTARAVG$])) AS Charge_Off_Perc, (AmtFin/ContVol) AS AvgAmtFin, AGT_PROF_BB_20051031.PL06CONT, AGT_PROF_BB_20051031.PL06CNCL, AGT_PROF_BB_20051031.[PL06PREM$], AGT_PROF_BB_20051031.[PL06DPAY$], AGT_PROF_BB_20051031.PL06AMFN, AGT_PROF_BB_20051031.[PL06EARN$], AGT_PROF_BB_20051031.[PL06LOSS$], AGT_PROF_BB_20051031.[PL06FLAG#], AGT_PROF_BB_20051031.[PL06FLAG$], AGT_PROF_BB_20051031.[PL06BILL#], AGT_PROF_BB_20051031.[PL06BILL$], AGT_PROF_BB_20051031.[PL06ARBL#], AGT_PROF_BB_20051031.[PL06ARBL$], AGT_PROF_BB_20051031.PL12CONT, AGT_PROF_BB_20051031.PL12CNCL, AGT_PROF_BB_20051031.[PL12PREM$], AGT_PROF_BB_20051031.[PL12DPAY$], AGT_PROF_BB_20051031.PL12AMFN, AGT_PROF_BB_20051031.[PL12EARN$], AGT_PROF_BB_20051031.[PL12LOSS$], AGT_PROF_BB_20051031.[PL12FLAG#], AGT_PROF_BB_20051031.[PL12FLAG$], AGT_PROF_BB_20051031.[PL12BILL#], AGT_PROF_BB_20051031.[PL12BILL$], AGT_PROF_BB_20051031.[PL12ARBL#], AGT_PROF_BB_20051031.[PL12ARBL$], AGT_PROF_BB_20051031.PLCMCONT, AGT_PROF_BB_20051031.PLCMCNCL, AGT_PROF_BB_20051031.[PLCMPREM$], AGT_PROF_BB_20051031.[PLCMDPAY$], AGT_PROF_BB_20051031.PLCMAMFN, AGT_PROF_BB_20051031.[PLCMEARN$], AGT_PROF_BB_20051031.[PLCMLOSS$], AGT_PROF_BB_20051031.[PLCMFLAG#], AGT_PROF_BB_20051031.[PLCMFLAG$], AGT_PROF_BB_20051031.[PLCMBILL#], AGT_PROF_BB_20051031.[PLCMBILL$], AGT_PROF_BB_20051031.[PLCMARBL#], AGT_PROF_BB_20051031.[PLCMARBL$], AGT_PROF_BB_20051031.FLDREP, AGT_PROF_BB_20051031.FLDREPNME, AGT_PROF_BB_20051031.[T06ARBAL#], AGT_PROF_BB_20051031.[T06ARBAL$], AGT_PROF_BB_20051031.[T06ARAVG$], AGT_PROF_BB_20051031.[T12ARBAL#], AGT_PROF_BB_20051031.[T12ARBAL$], AGT_PROF_BB_20051031.[T12ARAVG$], AGT_PROF_BB_20051031.[TCMARBAL#], AGT_PROF_BB_20051031.[TCMARBAL$], AGT_PROF_BB_20051031.[TCMARAVG$], AGT_PROF_BB_20051031.[TOTARBAL#], AGT_PROF_BB_20051031.[TOTARBAL$], AGT_PROF_BB_20051031.[TOTARAVG$]
    FROM AGT_PROF_BB_20051031 LEFT JOIN COFandVOC ON AGT_PROF_BB_20051031.YEARBOOK=COFandVOC.Year
    WHERE (((AGT_PROF_BB_20051031.AGNTSTATUS)="X" Or (AGT_PROF_BB_20051031.AGNTSTATUS)="C"));


    Both the AGNTTYPE and AGNTSTATUS columns include no nulls and are of the 'text' type.


    Thank you for assisting me, and sorry about the enormity of the Access queries.


    Regards,


    bubbis

    Quote

    [Microsoft][ODBC Microsoft Access Driver]Number value out of range (null)


    I get this message when using Excel to query an Access DB. I specify what query, not table, I want to retrieve. For one request, the data is returned; for another request on a query with nearly identical SQL, I get the message above. I've replaced all null cells in the source Excel sheet with hyphens --same message.


    I've been able to find nothing regarding a fix for this error message. Could someone please tell me what the heck is going on here?


    Thank you very much in advance for any assistance that you can provide.


    Regards,


    Bubbis

    Re: Overwriting XML Data...


    Hello again.


    I changed the code to


    It works obviously, but overwriting would be ideal.

    Hello, all!


    When I run this code,


    cells A5:G5 are populated with XML data -just a unique agency name (specified by the variable Chnger in the code), and some contact information. When I change cell A1 to, let's say, 1204, I would expect the the old XML data in A5:G5 to be overwritten by the new agent 1204 information. But instead i get the error

    Quote

    The operation could not be completed because the result would overlap an existing XML mapping.


    What am I doing wrong here?


    Thanks so much for any assistance.

    Re: Apply Macro to Multiple Workbooks...


    Thanks for the reply, 4508. I think, instead of the Add-In, I'll for now go with creating a custom button on the toolbar, and assign my macro to it. This makes the process drastically quicker --though I still must open every workbook.


    Thanks again,


    Bubbis

    Re: Apply Macro to Multiple Workbooks...


    Thanks for the suggestion, xlite. I saved the workbook containing the macro as an .xla file in the default AddIns location. So, now I have the option to checkmark it in the Add Ins dialog box.


    Where I'm a tad confused right now is what to do next. When I checkmark the box next to my Add In, I've noticed that the Add In project appears in the VBE for every workbook that I open thereafter. If I uncheck the box, and then close and reopen Excel, the project is gone. So I know how to activate it, and deactivate it. But a couple of Qs:


    1) In order to run the Add In, is it necessary for me to run it from the VBE (by pressing the "Play" button, for instance), or is there a way to run it from the toolbar?


    2) How do I add a description to the Add In? The Properties for the file will does not have a section to do so.


    Thanks for your assistance. I learn a great deal from all of you every day.


    Bubbis

    I have 20 workbooks that need one macro applied to them. I've already written the macro, which resides in a standard module. How do I modify the macro that I've written to apply to the other workbooks?


    This is probably a simple request, but I can't seem to find the answers I need in the help files.


    Thanks for any assistance.


    Bubbis

    Re: Index Match using < operator


    Heck, I'd just subtract 1 from every value in column B, get rid of the 'less than' signs, and then use the formula


    =INDEX('Charges Table'!$C$2:$R$13,SUM((MATCH(I4,'Charges Table'!$B$2:$B$13,1)),1),MATCH(C4,'Charges Table'!$C$1:$R$1,0))


    in column J. Hope that helps.


    Bubbis Thedog

    Re: PasteSpecial to Different Sheets, etc...


    Thanks for the advice, shades. I cut the code from the Worksheet module, and pasted it into a new Module 1, then pasted your code into the Worksheet module. What do I change "MyOtherCode" to?


    I got my code almost working, by the way. It seems as if there's something wrong directly following the first copy and paste that's causing the name in the combobox to disappear. This, in turn, turns formulas depending on that name to #N/A, and the #N/As are what's getting pasted into my Database worksheet. Here's my code if you, or anyone else, can find out what I'm doing wrong. I've also attached the revised file.



    Thanks for any assistance!


    Bubbis Thedog

    Re: If "X=1",?


    If you only have Xs in the column, you can use COUNTA(A1:A6). If you have other values in there, use COUNTIF(A1:A6,"X").

    Re: PasteSpecial to Different Sheets, etc...


    Thanks for the reply, Shades.


    Actually, that's what I attempted --specifying what sheet the data is to be pasted into. One thing that I tried was adding Sheets("Database").Select after both lines that contain Selection.Copy, and that did not work.


    I thought that you may be able to add the to-be-pasted-into sheet (Database in my case) in the PasteSpecial line. I just don't know where to put it, and how to put it in there.


    Thanks again,


    Bubbis Thedog

    Hello, all.


    I was messing around trying to get better with VBA and hit a stumbling block. I have attached the simple workbook that I was using, and pasted the code below if you'd like to check that out first:



    K, now here are my issues:


    1) For both PasteSpecials, I need the copied data from the Invoice worksheet to be pasted into the Database worksheet. I've tried and tried to modify the code to do this, but have been unsuccessful being that I'm an amateur at this.


    2) The "Finished" button in Invoices is what triggers the Finished_Click macro. This basically adds a record to the database, line by line, each time it's selected. Now, I'd like to have 5 ComboBoxes in the invoices sheet, because obviously more than one Component may be ordered. So, instead of having 5 "Finished" buttons to add individual records to Database, I'd like there to be one "Finished" button that ONLY sends the records for whatever Components are ordered. The only way I know how to get the information for each Component into Database right now is to copy a range from Invoices, and paste the whole range. So, I'd potentially be pasting empty records into Database if only 3 Components are ordered. How do I get around this?


    I'd appreciate any help with this. Thanks.


    Bubbis Thedog

    Re: VLOOKUP() Using Multiple Sheets?


    Thanks for the response, milyenabox.


    Let's say we have two worksheets: WS1 and WS2. In a cell in WS2, I input the formula


    =VLOOKUP(A1,'WS1'!A1:F118,6)


    A1 will always match a value in column A of WS1.


    I'd think this formula would return the value in column F of WS1 whose column A value (the eventual row reference) matches the value of A1 in WS2. It does return a value from WS1, and from column F; but the value is from a different row than I'd expect. That is, the value that is used for a row reference in WS1 is not the same as A1. All of my column A values are unique, by the way. Any ideas on what may be causing this?


    Thanks, you all!


    Bubbis Thedog

    Quick question:


    When using VLOOKUP, is it possible use the reference value (parameter one) from one sheet, and then use an array to look in in a diffrent sheet? Or is this function only applicable to one sheet? I can't get it to work in multiple sheets, but was just curious. Is this an advantage of using INDEX(MATCH(), MATCH()) [which I'm using]?


    Thanks for any input!


    Bubbis Thedog

    Re: Removing an Item from a list box


    To remove an item from a listbox, you can 1) remove the item(s) from the actual list on the worksheet, as prescribed by the Input Range that you created, or 2) modify the Input Range field such that the cell reference(s) that you wish to remove are deleted. Not sure what you mean by a "remove" button. Am I missing something, friend?


    Bubbis Thedog

    Hello, all.


    I have a worksheet with about 55000 rows and 15 columns --10 of which contain formulas in nearly every row. When I open the workbook up with the Automatic calculation enabled, it Calculates on the status bar rather quickly: maybe a few seconds.


    At the bottom of the worksheet I need three rows: total, count, and average. When I try to AutoFill the total (SUM) and count (COUNT) formulas across the columns, everything works fine. But when I try to do the average and AutoFill (using either AVERAGE or SUBTOTAL), the status bar reads "Calculating 0%" and Excel locks up (stops responding). Also, if I try to copy an AVERAGE cell and then paste it, Excel locks up.


    Of course, if I set the Calculation to Manual, everything works fine. But my question is, why did I have no problems until I tried to calculate an average?


    Another question: Will changing the Iterations make any difference?


    Thanks for any help with this issue.


    Bubbis Thedog