YouTubersLoveExcel#26: Text To Column Trouble

I have a sheet that we use as an invoce. It has 2 tables side by side each populated by a different query. The one table is in columns A:F. The other Table is in Columns H:J. They both start on row 11. Under the tables is a text box that goes from columns A:J. My problem is that the text box will float down when the 1st table expands but does not with the 2nd table. How can I get it to float to four rows below either table depending on which one has the most data in it. Please note: the tables change each time they are refreshed.

I want to run a macro that will find the cell location of the first Value between 14 and 18 in column B. I want to store the location so I can use it later on to copy the value from that cell. Im trying to use the Find method but its a bit confusing.

Hi guys- Im currently working on a small project and have a problem.
Column A is a list of 537 companies.
Column B is a list of 2790 companies with prices.

I only want Column B to have the Row A companies. Simple, but the problem is that most of the companies aren't exactly alike, example Column A is "AT&T" and Column B will be "AT & T" or "AT&T Worldwide", or Column B will have endings like "LLC" and "LP" but Column A doesn't (its a less official list). Is there someway to do a compare that won't exactly match the columns but still work for me? I really don't want to go through all 2790 companies to see if they match.

Thanks for any help!

I cant work out why my code doesnt find the next colulmn in my sheet "Roubaix", viz;
Code:

LastCol = Sheets("Roubaix").Cells(1, Columns.Count).End(xlToLeft)

Any ideas??

Greetings,

I have a list of the following format:

L1-100001
L1-100002
L1-100003
L2-100001
L3-100001
L3-100002
L4-100001, etc.

I need to find a way to extract from this list (with a cell formula or series of cell formulae) the highest index for a particular prefix (i.e. 100003 for L1, or 100002 for L2).

I've tried so many different things and I just can't get any to work correctly. I'm familiar with arrays, and VLOOKUP and so forth but I must be doing something wrong.

I am blowing the budget on this task and I really need to move on ... please help.

TIA
William DeLeo

Please reference this thread solved previously :

http://www.excelforum.com/excel-prog...ml#post2508388

I have added one sheet to my workbook as a summary since my last thread, and am trying to adapt the code you have already provided me with no luck. So I have gone with a very basic format.
The sheet is named "Team Member Dashboard"

I am running into one issue, in getting the test if Text function to work. As this sheet doesnt have the same basic structure as the others, and 8 different criteria and matching colouring tests, it all went haywire ....

Below is one section of the code, and I am just changing the ranges and repeating the process all the way down the sheet. It still wont change the text cells to white on black though? The entire workbook still works perfectly apart from this one sheet that I added, any advice would be greatly appreciated?

I tried the cell.type test, but it errored saying "not accepted."

The sheet i have added is the Team Member Dashboard, and each of the rows in the table has the relevant colour coding in rows 29:42.


Code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRange As Range, Cell As Range
Set MyRange = Range("b2:m2")
ActiveSheet.Unprotect Password:="Virgin11"
For Each Cell In MyRange
    
        If Cell.Value <= [i29].Value Then
            Cell.Interior.ColorIndex = 3
        End If
        
        If Cell.Value >= [f29].Value Then
            Cell.Interior.ColorIndex = 45
        End If
                
        If Cell.Value >= [d29].Value Then
            Cell.Interior.ColorIndex = 27
        End If
                
        If Cell.Value >= [b29].Value Then
            Cell.Interior.ColorIndex = 4
        End If
        
        If Cell.Value = "" Then
            Cell.Interior.ColorIndex = 2
        End If
        
        If Cell.Value = vbString Then
              Cell.Interior.ColorIndex = 1
              Cell.Font.ColorIndex = 2
        End If
        
    Next

Looking for formula that returns "Y" in colm D if "Y" is in any of colms A through C.

Nothing has worked yet. thanks.

A B C D 1 #1 #2 #3 Y 2 Y Y Y 3 4 Y Y 5 Y Y Y 6

I recently had my computer upgraded at work and they loaded a new version of Access on it. I have worked with Access in the past and I not able to do a very simple step that I have done MULTIPLE times in the past.

I have a text file (headers included) that I would like to bring into Access and create a table with it. Unfortunately, I click on File>Get External Data>Import and the window pops up but Text is not an option. I only have the following 4 options:
Microsoft Office Access (*.mdb, *.adp, *.mda,...)
Windows Sharepoint Services()
XML (*.xml, *.xsd)
ODBC Databases

I never had an issue with this in the past when I was using an older version of Access.

I am importing 4 text files every day. (REG.EXP starts in row 3, JOB.EXP starts in row 65, TIME.EXP starts in row 92, PLU1.EXP starts in row 149) I have them going to a workbook, 1st day starts in column A 2nd in column I etc. through the month. The problem I am having is with the 24th day of September. Instead of the files going to column GC the first file is going to GO and the other 3 are going to the right row but different column and they are also being created twice and are side by side. All the other days are working fine. I am enclosing the code for the 23rd which works 24th which does not and the 25th which does. Maybe some fresh eyes can spot my mistake.

Sub impDailyFilesDay23()
'
' impDailyFilesDay23 Macro
'

'
With Sheets("SEP").QueryTables.Add(Connection:= _
"TEXT;F:\Apps\Panpoll\sf0015\2010\SEP\0924REG.EXP", Destination:=Sheets("SEP").Range("$FU$3" _
))
.Name = "0924REG"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(9, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

With Sheets("SEP").QueryTables.Add(Connection:= _
"TEXT;F:\Apps\Panpoll\sf0015\2010\SEP\0924JOB.EXP", Destination:=Sheets("SEP").Range("$FU$65" _
))
.Name = "0924JOB"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(9, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

With Sheets("SEP").QueryTables.Add(Connection:= _
"TEXT;F:\Apps\Panpoll\sf0015\2010\SEP\0924TIME.EXP", Destination:=Sheets("SEP").Range("$FU$92" _
))
.Name = "0924TIME"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(9, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

With Sheets("SEP").QueryTables.Add(Connection:= _
"TEXT;F:\Apps\Panpoll\sf0015\2010\SEP\0924PLU1.EXP", Destination:=Sheets("SEP").Range("$FU$149" _
))
.Name = "0924PLU1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(9, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

End Sub

Sub impDailyFilesDay24()
'
' impDailyFilesDay24 Macro
'

'
With Sheets("SEP").QueryTables.Add(Connection:= _
"TEXT;F:\Apps\Panpoll\sf0015\2010\SEP\0925REG.EXP", Destination:=Sheets("SEP").Range("$GC$3" _
))
.Name = "0925REG"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(9, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

With Sheets("SEP").QueryTables.Add(Connection:= _
"TEXT;F:\Apps\Panpoll\sf0015\2010\SEP\0925JOB.EXP", Destination:=Sheets("SEP").Range("$GC$65" _
))
.Name = "0925JOB"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(9, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

With Sheets("SEP").QueryTables.Add(Connection:= _
"TEXT;F:\Apps\Panpoll\sf0015\2010\SEP\0925TIME.EXP", Destination:=Sheets("SEP").Range("$GC$92" _
))
.Name = "0925TIME"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(9, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

With Sheets("SEP").QueryTables.Add(Connection:= _
"TEXT;F:\Apps\Panpoll\sf0015\2010\SEP\0925PLU1.EXP", Destination:=Sheets("SEP").Range("$GC$149" _
))
.Name = "0925PLU1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(9, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

End Sub

Sub impDailyFilesDay25()
'
' impDailyFilesDay25 Macro
'

'
With Sheets("SEP").QueryTables.Add(Connection:= _
"TEXT;F:\Apps\Panpoll\sf0015\2010\SEP\0926REG.EXP", Destination:=Sheets("SEP").Range("$GK$3" _
))
.Name = "0926REG"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(9, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

With Sheets("SEP").QueryTables.Add(Connection:= _
"TEXT;F:\Apps\Panpoll\sf0015\2010\SEP\0926JOB.EXP", Destination:=Sheets("SEP").Range("$GK$65" _
))
.Name = "0926JOB"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(9, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

With Sheets("SEP").QueryTables.Add(Connection:= _
"TEXT;F:\Apps\Panpoll\sf0015\2010\SEP\0926TIME.EXP", Destination:=Sheets("SEP").Range("$GK$92" _
))
.Name = "0926TIME"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(9, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

With Sheets("SEP").QueryTables.Add(Connection:= _
"TEXT;F:\Apps\Panpoll\sf0015\2010\SEP\0926PLU1.EXP", Destination:=Sheets("SEP").Range("$GK$149" _
))
.Name = "0926PLU1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(9, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

End Sub