Search results

OK, I have this code that loops through several workbooks in a folder. The search range is dynamic. The issue is that the data returned from the SourceRange is coming from outside the SourceRange. The way each workbook is laid out is there is a date in column A that begins in row 24. This range...

I have it figured out. Thanks for the thoughts. It helped me figure it out.

Oooops, a typo, If the salary is $150,000 then the answer would be Max or anything equal to $136,173 or above would be Max. If the salary were $85,108 or lower the answer would be Min. If the salary were between $85,109 to $136,173 the answer would be Mid.

Hey Deepak, I was able to modify the formula to get it to work. Thanks a bunch!

OK, I have this spreadsheet, Sheet1 contains salaries. In column A is the base rate, column C is the position name. In Sheet 2, column B is the position name, column D is the min pay range, column F is the mid pay range, and column H is the max pay range. If Sheet 1 column A has $150,000, then...

Pretty slick Deepak! I have some cells where there are 3 lines. Example: Cell View: Accounts Payable 21420 East Main StreetSomwhere, VA 24012 Formula Bar View: Accounts Payable 21420 East Main Street Somewhere, VA 24012

I have a spreadsheet that is several thousand rows long. It contains addresses and they look like this: In the cell: 600 Rutherford Ave NESomewhere, VA 24018 In the formula bar: 600 Rutherford Ave NE Somewhere, VA 24018 What I want to do is put the street address in say column B and the city...

No offense taken. It was great to see how you dissected the code to find the error of my ways. This was great learning. I learned several tricks when writing this code and from your help several more. Thank you so much for your time.

SUCCESS!!!!! I owe you a box of Krisy Kreams! They are awful good! In going through the code how in the world did you figure it out?

One more thing, if you take one of the workbooks like Abbott and name it Luke and run the macro you will see that it will copy all the IC data into it when it should do nothing.

Here are more workbooks... BTW, the macro workbook is the original code before we started modifying it.

OK Luke, I made some workbooks for you. I could only download 3. I am going to put some more in another reply. Maybe this will clear things up a bit. On the macro reading teh wrong column, that was because when the macro runs it copies one column at a time using the array. So it found...

Here are the results of the Debug: # of results for Derrick: 19 Client Name was found in col # :1 We should be looking in column 4.

OK, fixed my mistake. Got a Run-time Error 1004 Aplication-defined orobject-defined error On this line: .Columns(ColNum).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy Destination:=wb.Worksheets("IC").Cells(2, i + 1)

Hold on I made a mistake...

I get a Run-Time Error 438 Object dosen't support this property or method On this line: .Columns(1).SpecialCells(xlCellTypeVisible).Count

May the force be with you Luke. I amended the code. Ran it and no records copied. Here are the results from the Immediate window: Initializing resources for Office Edition (Excel) LocalizationContext object created successfully About to laod resources from local machine. Successfully loaded...

"So, if the AutoFilter has no match then all cells are visible and it copys them." I meant to say all cells are "not" visible. When I run the macro without SpecialCells(xlCellTypeVisible) the macro copies the data fine for any match but all the data when a match is not found. When I add...

By the way, I used the original code.

OK, it looks like here is where things are going wrong: For i = LBound(ColHeads) To UBound(ColHeads) ColNum = .Rows(1).Find(ColHeads(i)).Column .Columns(ColNum).Offset(1).Resize(.Rows.Count - 1).Copy Destination:=wb.Worksheets("IC").Cells(2, i + 1) What the code is doing is copying the...

Luke my pal it only copied the headers, no data in any of the workbooks.

Luke, RepName would be an existing workbook named Smith 04-15.xlxs This line of code extracts the name only: RepName = Left(myFile, InStr(myFile, " ") - 1) 'extracts the Rep Name from the file name. -1 for removing space I have about 40 workbooks for the different reps where I get RepName...

Correct Luke. I added the additional lines of code and it still is doing the samething. I need the code to somehow say if the RepName does not match the auto filter then do nothing. I am wondering if this line needs changing? If .Rows.Count > 1 Then 'there is at least 1 row which meets...

I have some code that uses Auto Filter to copy the matching data from the master workbook called Intercompany or wsIC in the code to a matching workbook called myFile. My issue is if I have a workbook in the folder and there is no match for the RepName in wsIC the code writes all the data in...

MAN! I knew it was something simple! Works great!