Hi,

I have two seperate workbooks. One is "Raw Data" and the other is
"Index". What I need to do is look up a value in Index!A1 and match it
to a header in "Raw Data"!A:A ...store that column number in a variable
then copy that entire column to "Index" in the same column that
contains the look up value.

Best Regards,

Mjack


--
mjack003
------------------------------------------------------------------------
mjack003's Profile: http://www.excelforum.com/member.php...fo&userid=5141
View this thread: http://www.excelforum.com/showthread...hreadid=469775

I think you may have to clarify your post.

It sounds like you have two separate worksheets named Index and 'Raw Data'. Are
they in separate workbooks?

But if you match up in 'raw data' column A, you're gonna get a row number back.

Do you really mean look in 'raw data' row 1 or what?

mjack003 wrote:

Hi,

I have two seperate workbooks. One is "Raw Data" and the other is
"Index". What I need to do is look up a value in Index!A1 and match it
to a header in "Raw Data"!A:A ...store that column number in a variable
then copy that entire column to "Index" in the same column that
contains the look up value.

Best Regards,

Mjack

--
mjack003
------------------------------------------------------------------------
mjack003's Profile: http://www.excelforum.com/member.php...fo&userid=5141
View this thread: http://www.excelforum.com/showthread...hreadid=469775

--

Dave Peterson

Dave,

Yes there are two sheets in two separate books. The value to look up
is in workbook "Audit" on worksheet "Index" in range "A1". What I need
to do is take that value...go to book "Test" sheet "Raw Data" and look
in Row 1 across all the headers. There are 30 or so from A1 to AC1.
Once the match to the look up value is found on sheet "Raw Data" I need
to copy the entire column including the header and paste it to sheet
"Index" in column A. I came across another problem. Workbook "Test" is
generated by a financial system which serializes the file name by adding
a date and time to the end of the file name.
Ex. "Test 050922-123034". So I need to activate the book by the first
four characters and 13 space holders for the date-time. Any suggestions
are appreciated.

Best Regards,
Mjack


--
mjack003
------------------------------------------------------------------------
mjack003's Profile: http://www.excelforum.com/member.php...fo&userid=5141
View this thread: http://www.excelforum.com/showthread...hreadid=469775

So you're overwriting the Index column A with the data from Raw Data?

I think that this works ok:

Option Explicit
'Yes there are two sheets in two separate books. The value to look up
'is in workbook "Audit" on worksheet "Index" in range "A1". What I need
'to do is take that value...go to book "Test" sheet "Raw Data" and look
'in Row 1 across all the headers. There are 30 or so from A1 to AC1.
'Once the match to the look up value is found on sheet "Raw Data" I need
'to copy the entire column including the header and paste it to sheet
'"Index" in column A. I came across another problem. Workbook "Test" is
'generated by a financial system which serializes the file name by adding
'a date and time to the end of the file name.
'Ex. "Test 050922-123034". So I need to activate the book by the first
'four characters and 13 space holders for the date-time. Any suggestions
'are appreciated.

Option Explicit
Sub testme()

Dim RawDataWks As Worksheet
Dim IndexWks As Worksheet
Dim RawDataWkbk As Workbook
Dim wkbk As Workbook
Dim Res As Variant

Set IndexWks = Workbooks("audit.xls").Worksheets("Index")

Set RawDataWkbk = Nothing
For Each wkbk In Application.Workbooks
If LCase(wkbk.Name) Like "test ????????.xls" Then
Set RawDataWkbk = wkbk
Exit For
End If
Next wkbk

If RawDataWkbk Is Nothing Then
MsgBox "I don't see a workbook named ""Test ....xls"" Open!"
Exit Sub
End If

Set RawDataWks = RawDataWkbk.Worksheets("Raw Data")

Res = Application.Match(IndexWks.Range("a1").Value, RawDataWks.Rows(1), 0)

If IsError(Res) Then
MsgBox "No match found for: " & IndexWks.Range("a1").Value
Exit Sub
End If

RawDataWks.Columns(Res).Copy _
Destination:=IndexWks.Range("a1")

End Sub

I didn't do any checks to verify that the worksheet names are found! And it
assumes that Audit.xls is open, too.

mjack003 wrote:

Dave,

Yes there are two sheets in two separate books. The value to look up
is in workbook "Audit" on worksheet "Index" in range "A1". What I need
to do is take that value...go to book "Test" sheet "Raw Data" and look
in Row 1 across all the headers. There are 30 or so from A1 to AC1.
Once the match to the look up value is found on sheet "Raw Data" I need
to copy the entire column including the header and paste it to sheet
"Index" in column A. I came across another problem. Workbook "Test" is
generated by a financial system which serializes the file name by adding
a date and time to the end of the file name.
Ex. "Test 050922-123034". So I need to activate the book by the first
four characters and 13 space holders for the date-time. Any suggestions
are appreciated.

Best Regards,
Mjack

--
mjack003
------------------------------------------------------------------------
mjack003's Profile: http://www.excelforum.com/member.php...fo&userid=5141
View this thread: http://www.excelforum.com/showthread...hreadid=469775

--

Dave Peterson

Oops. I meant to delete your specification.

Instead, I kept it and added an additional "option explicit"

Ahhh.....no real changes--just cleaned up that junk.

Option Explicit
Sub testme()

Dim RawDataWks As Worksheet
Dim IndexWks As Worksheet
Dim RawDataWkbk As Workbook
Dim wkbk As Workbook
Dim Res As Variant

Set IndexWks = Workbooks("audit.xls").Worksheets("Index")

Set RawDataWkbk = Nothing
For Each wkbk In Application.Workbooks
If LCase(wkbk.Name) Like "test ????????.xls" Then
Set RawDataWkbk = wkbk
Exit For
End If
Next wkbk

If RawDataWkbk Is Nothing Then
MsgBox "I don't see a workbook named ""Test ....xls"" Open!"
Exit Sub
End If

Set RawDataWks = RawDataWkbk.Worksheets("Raw Data")

Res = Application.Match(IndexWks.Range("a1").Value, RawDataWks.Rows(1), 0)

If IsError(Res) Then
MsgBox "No match found for: " & IndexWks.Range("a1").Value
Exit Sub
End If

RawDataWks.Columns(Res).Copy _
Destination:=IndexWks.Range("a1")

End Sub

I didn't do any checks to verify that the worksheet names are found! And it
assumes that Audit.xls is open, too.

<<snipped

Sweet...thanks for the help Dave. This is way off the subject but could
you give me some suggestions on how to clean up this code? For some
reason it keeps freezing on my computer...basically four loops and
exit. Worked just fine when I only had it as one loop but had to take
one long list and shorten it into four lists on my audit sheet. Its
connected to a command button on "Rows" sheet.

Here it is:

Private Sub CommandButton3_Click()
Dim myRng As Range
Dim myCell As Range
Dim myInputRng As Range
Dim FoundCell As Range
Dim rowRng As Range
Dim pop As String
pop = MsgBox("This may take a few minutes...are you sure you want to
populate the audit?", vbYesNo)
If pop = vbYes Then

Application.ScreenUpdating = False
'use the same name for consistency
Set myRng = Worksheets("rows").Range("myrng")

Set rowRng = Worksheets("Audit").Range("B2:B651")
rowRng.ClearContents

With Worksheets("Audit")
Set myInputRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
End With
For Each myCell In myInputRng.Cells
Set FoundCell = myRng.Cells.Find(what:=myCell.Value, _
lookat:=xlWhole, LookIn:=xlValues, _
MatchCase:=False, searchorder:=xlByRows)
If FoundCell Is Nothing Then
'myCell.Offset(0, 1).Value = "Not found" Commented out so cell is
left blank
Else
myCell.Offset(0, 1).Value = FoundCell.column - 1
End If
Next myCell
Set rowRng = Worksheets("Audit").Range("E2:E651")
rowRng.ClearContents

With Worksheets("Audit")
Set myInputRng = .Range("D2", .Cells(.Rows.Count, "D").End(xlUp))
End With
For Each myCell In myInputRng.Cells
Set FoundCell = myRng.Cells.Find(what:=myCell.Value, _
lookat:=xlWhole, LookIn:=xlValues, _
MatchCase:=False, searchorder:=xlByRows)
If FoundCell Is Nothing Then
Else
myCell.Offset(0, 1).Value = FoundCell.column - 1
End If
Next myCell

Set rowRng = Worksheets("Audit").Range("H2:H651")
rowRng.ClearContents

With Worksheets("audit")
Set myInputRng = .Range("G2", .Cells(.Rows.Count, "G").End(xlUp))
End With
For Each myCell In myInputRng.Cells
Set FoundCell = myRng.Cells.Find(what:=myCell.Value, _
lookat:=xlWhole, LookIn:=xlValues, _
MatchCase:=False, searchorder:=xlByRows)
If FoundCell Is Nothing Then
Else
myCell.Offset(0, 1).Value = FoundCell.column - 1
End If
Next myCell

Set rowRng = Worksheets("Audit").Range("K2:K651")
rowRng.ClearContents

With Worksheets("audit")
Set myInputRng = .Range("J2", .Cells(.Rows.Count, "J").End(xlUp))
End With
For Each myCell In myInputRng.Cells
Set FoundCell = myRng.Cells.Find(what:=myCell.Value, _
lookat:=xlWhole, LookIn:=xlValues, _
MatchCase:=False, searchorder:=xlByRows)
If FoundCell Is Nothing Then
Else
myCell.Offset(0, 1).Value = FoundCell.column - 1
End If
Next myCell


Application.ScreenUpdating = True
MsgBox "Done!"

Else
If pop = vbNo Then
Exit Sub
End If
End If
End Sub


--
mjack003
------------------------------------------------------------------------
mjack003's Profile: http://www.excelforum.com/member.php...fo&userid=5141
View this thread: http://www.excelforum.com/showthread...hreadid=469775

I don't see anything that would lock it up. But it does look like you're doing
the same thing to 4 different ranges.

Sometimes, you can specify the columns to look at and loop through those
columns.

Option Explicit
Private Sub CommandButton3_Click()

Dim myRng As Range
Dim myCell As Range
Dim myInputRng As Range
Dim FoundCell As Range
Dim pop As Long
Dim myCols As Variant
Dim cCtr As Long

pop = MsgBox("This may take a few minutes..." _
& "are you sure you want to populate the audit?", vbYesNo)

If pop = vbYes Then
Application.ScreenUpdating = False
myCols = Array("A", "D", "G", "J")

'use the same name for consistency
Set myRng = Worksheets("rows").Range("myrng")

For cCtr = LBound(myCols) To UBound(myCols)
With Worksheets("audit")
Set myInputRng = .Range(.Cells(2, myCols(cCtr)), _
.Cells(.Rows.Count, myCols(cCtr)).End(xlUp))
End With

myInputRng.Offset(0, 1).ClearContents

For Each myCell In myInputRng.Cells
Application.StatusBar = "Processing: " & myCell.Address(0, 0)
Set FoundCell = myRng.Cells.Find(what:=myCell.Value, _
lookat:=xlWhole, LookIn:=xlValues, _
MatchCase:=False, searchorder:=xlByRows)
If FoundCell Is Nothing Then
'myCell.Offset(0, 1).Value = "Not found"
'Commented out so cell is Left blank
Else
myCell.Offset(0, 1).Value = FoundCell.Column - 1
End If
Next myCell
Next cCtr

Application.ScreenUpdating = True
Application.StatusBar = False
MsgBox "Done!"

Else
'do nothing
End If
End Sub

The only thing that I did was add that .statusbar stuff. Maybe you can watch it
process your data while it's running.

ps. If you're running this code, then going to another application to do stuff,
you may want to just keep your fingers off the keyboard/mouse--let it run while
it has Windows full attention to see what happens.



mjack003 wrote:

Sweet...thanks for the help Dave. This is way off the subject but could
you give me some suggestions on how to clean up this code? For some
reason it keeps freezing on my computer...basically four loops and
exit. Worked just fine when I only had it as one loop but had to take
one long list and shorten it into four lists on my audit sheet. Its
connected to a command button on "Rows" sheet.

Here it is:

Private Sub CommandButton3_Click()
Dim myRng As Range
Dim myCell As Range
Dim myInputRng As Range
Dim FoundCell As Range
Dim rowRng As Range
Dim pop As String
pop = MsgBox("This may take a few minutes...are you sure you want to
populate the audit?", vbYesNo)
If pop = vbYes Then

Application.ScreenUpdating = False
'use the same name for consistency
Set myRng = Worksheets("rows").Range("myrng")

Set rowRng = Worksheets("Audit").Range("B2:B651")
rowRng.ClearContents

With Worksheets("Audit")
Set myInputRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
End With
For Each myCell In myInputRng.Cells
Set FoundCell = myRng.Cells.Find(what:=myCell.Value, _
lookat:=xlWhole, LookIn:=xlValues, _
MatchCase:=False, searchorder:=xlByRows)
If FoundCell Is Nothing Then
'myCell.Offset(0, 1).Value = "Not found" Commented out so cell is
left blank
Else
myCell.Offset(0, 1).Value = FoundCell.column - 1
End If
Next myCell
Set rowRng = Worksheets("Audit").Range("E2:E651")
rowRng.ClearContents

With Worksheets("Audit")
Set myInputRng = .Range("D2", .Cells(.Rows.Count, "D").End(xlUp))
End With
For Each myCell In myInputRng.Cells
Set FoundCell = myRng.Cells.Find(what:=myCell.Value, _
lookat:=xlWhole, LookIn:=xlValues, _
MatchCase:=False, searchorder:=xlByRows)
If FoundCell Is Nothing Then
Else
myCell.Offset(0, 1).Value = FoundCell.column - 1
End If
Next myCell

Set rowRng = Worksheets("Audit").Range("H2:H651")
rowRng.ClearContents

With Worksheets("audit")
Set myInputRng = .Range("G2", .Cells(.Rows.Count, "G").End(xlUp))
End With
For Each myCell In myInputRng.Cells
Set FoundCell = myRng.Cells.Find(what:=myCell.Value, _
lookat:=xlWhole, LookIn:=xlValues, _
MatchCase:=False, searchorder:=xlByRows)
If FoundCell Is Nothing Then
Else
myCell.Offset(0, 1).Value = FoundCell.column - 1
End If
Next myCell

Set rowRng = Worksheets("Audit").Range("K2:K651")
rowRng.ClearContents

With Worksheets("audit")
Set myInputRng = .Range("J2", .Cells(.Rows.Count, "J").End(xlUp))
End With
For Each myCell In myInputRng.Cells
Set FoundCell = myRng.Cells.Find(what:=myCell.Value, _
lookat:=xlWhole, LookIn:=xlValues, _
MatchCase:=False, searchorder:=xlByRows)
If FoundCell Is Nothing Then
Else
myCell.Offset(0, 1).Value = FoundCell.column - 1
End If
Next myCell

Application.ScreenUpdating = True
MsgBox "Done!"

Else
If pop = vbNo Then
Exit Sub
End If
End If
End Sub

--
mjack003
------------------------------------------------------------------------
mjack003's Profile: http://www.excelforum.com/member.php...fo&userid=5141
View this thread: http://www.excelforum.com/showthread...hreadid=469775

--

Dave Peterson