Search results for query: *

Thanks to the suggestions on this site the process is down to about 2 hours!!!! I changed to an update query: <code> strSQL = "INSERT INTO TNResidentClaimNumbers ( CLYEAR, CLMONTH, CLDAY, CLACLMNO, CLPARTIC, CLGROUP )" _ & " SELECT CLAIMCTL.CNYEAR...

PHV - No SMARTHEALTH.CLPARTIC is not indexed. But it should for this operation. SMARTHEALTH is the first of 10+ tables I have to bump against. None have an index on CLPARTIC. Is it possible to add an index to a recordset? The PK on the table is SMARTHEALTH.CLYEAR, SMARTHEALTH.CLMONTH...

I am bumping a rather large recordset (8000 + records) to find records in another table using DAO. What I have set up will take 30 hours to run. I think there has to be a better way. <code> Set rsSource = db.OpenRecordset("SELECT TNResidentsTest.*" _ & "...

Thanks to all for the suggestions. Here is what I did. The query that is the recordset for the form prompts the user, if nothing is entered records may be added with no ProvType. Private Sub Form_Current() If Me.NewRecord = True Then 'set txtProvType to SH or NP...

I have a bound form where the record source query prompts the user to enter a value to filter the recordset. The Values are SH or NP. After the user enters a value all the correct records are available for edit. What I want is IF the user clicks on the add new record button to have the text box...

Hi Skip - I was attempting to AutoFill the column with the formula in, the code above successfully placed it in the first cell(row 4) in the column. Driving home I thought I had it figured out because there was no .Address reference in my line of code: Range(.Cells(4, (intLastColSht1 +...

Hi, I am this far: Public Function Format_PayBill_Audit_Pivot_Report() 'Created by Joel Hardesty Apr 2013 'Creates 2 WB's with pivot tables Dim intLastColPrev As Integer Dim intLastColSht1 As Integer Dim strWB_Report As String Dim strWB_Ref As String Dim lngLastRowPrev As Long Dim...

Thanks Skip - Yes I am creating a macro to accomplish this task. The entire process takes the end users about 3.5 hours a week. The macro request is about 15 months old. I have been working in the sheet to learn, or try to learn, how the Index Match combo works. I was told to use the VLOOKUP. In...

I am at the stage of adding the formula to the cell. I am using: ActiveCell.Formula = strFormula where strFormula is: "=INDEX(Previous!.Range(.Cells(4, 40), .Cells(115, 40)),MATCH(GE!D4,PREVIOUS!.Range(.Cells( 4 , 4), .Cells( 115 , 4),0),1)" I am getting a 1004 error "Application Defined or...

Hi Skip - Thanks for sticking with me on this. Your Post: From what I see you would return the column next to column AN but the column to match on is in ..... Ahhhh, OK, I might see it now. AN4:AN115 is the return range or the data being sought. And the match contains the 2 sheet column...

Skip - Regarding your last post: then if I wanted to return the same coumn 40 as the result to my formula (What I want to do) how would I represent that with the single column reference like:=INDEX(PREVIOUS!D4:D115,MATCH(GE!D4,PREVIOUS!D4:D115,0),40). I left the 40 in and it returns a Ref error...

BTW - I have been assured by my managers that using relative & absolute references will not affect the accuracy of the data that is returned. I am still trying to wrap my head that. I do not beleive it but I am supposed to code under that assumption. Joel

No Good. Changing my formula to:=INDEX(PREVIOUS!D4:D115,MATCH(GE!D4,PREVIOUS!D4:D115,0),40) results in a Ref error. Is it possible Skip/Brad that the return value your formulas would produce is True/False or a cell number? I am missing something here still, sorry but thanks for your help. Joel

Brad - I think this scenario is what is happening with the 40: But the 40, from what I can deduce, is the offset for the return column or value returned if the Match is found in the Index. That is the way it is working according to my meek interpretation. But possibly you are trying to tell...

I have it working manually in 2 columns. The index method: =INDEX(PREVIOUS!A4:AN115,MATCH(GE!D4,PREVIOUS!D4:D115,0),40) VLOOKUP; =VLOOKUP(D4,PREVIOUS!D4:AN115,37,FALSE) Both pull in the correct value with the formula in the GE spreadsheet. with Skip's code: =INDEX(tPrev[Product...

The explanations explain why this does not work: ActiveCell.Formula = "=INDEX(" & rngPreviousShtRange & ",MATCH(" & strSearchCell & "," & rngPrevShtMatchCol & ",0)," & strDestinationColOffsetfromSearchColumn & ")" or this...

Now the question becomes how to add the $ character to my range. Anybody know an easy way? I'm thinking looping through the range string and adding them. Joel

=INDEX(PREVIOUS!A5:AN116,MATCH(GE!D5,PREVIOUS!D5:D116,0),40) Changed to: =INDEX(PREVIOUS!$A$4:$AN$115,MATCH(GE!D4,PREVIOUS!$D$4:$D$115,0),40) When incremented by autofill on the next line gives this: =INDEX(PREVIOUS!$A$4:$AN$115,MATCH(GE!D5,PREVIOUS!$D$4:$D$115,0),40) The $ makes Excel handle...

OK This works for the Index/Match: =INDEX(PREVIOUS!A4:AN115,MATCH(GE!D4,PREVIOUS!D4:D115,0),40) but when I copy down the formula it changes the ranges: =INDEX(PREVIOUS!A5:AN116,MATCH(GE!D5,PREVIOUS!D5:D116,0),40) How do I keep the range the same and only change the Match column? Thanks in...

I finally got a VLOOKUP to work: =VLOOKUP(D4,PREVIOUS!D4:AN115,37,FALSE) This brings in the correct value. But since I have heard the Index and Match combined is a better method I am trying to figure out why this does not work...