My macro pulls info from another file (2.txt)

When the file is generated, i have no control of the placement o
certain cells. But what i do have is a constant that the word BANK i
always in column E (regardles off the row) and cell i need is directl
after the word bank in column F. I need to copy the cell that's in
(thats directly to the right after the word Bank) and paste it into m
sheet101 worksheet in cell AL2.

Can anyone help

--
Message posted from http://www.ExcelForum.com

H

The following should get you started. It does assume that sheet101 is in the same workbook as the source data and this will have to be changed

Ton

Sub aaa(

Set finder = Range("e:e").Find(what:="BANK"

If Not finder Is Nothing The
Range("e:e").Find(what:="BANK").Activat
Sheets("sheet101").Range("a2").Value = ActiveCell.Offset(0, 1).Valu
End I

End Su

----- alexm999 wrote: ----

My macro pulls info from another file (2.txt

When the file is generated, i have no control of the placement o
certain cells. But what i do have is a constant that the word BANK i
always in column E (regardles off the row) and cell i need is directl
after the word bank in column F. I need to copy the cell that's in
(thats directly to the right after the word Bank) and paste it into m
sheet101 worksheet in cell AL2

Can anyone help


--
Message posted from http://www.ExcelForum.com

Alex

One way:

Sub CopyCell()

Dim RequiredBankCell As Range
Set RequiredBankCell = _
Columns("E:E").Find(What:="BANK", _
After:=Range("E1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
With Sheets("Sheet101")
RequiredBankCell.Offset(0, 1).Copy .Range("AL2")
End With

End Sub


Regards

Trevor


"alexm999 " wrote in message
...
My macro pulls info from another file (2.txt)

When the file is generated, i have no control of the placement of
certain cells. But what i do have is a constant that the word BANK is
always in column E (regardles off the row) and cell i need is directly
after the word bank in column F. I need to copy the cell that's in F
(thats directly to the right after the word Bank) and paste it into my
sheet101 worksheet in cell AL2.

Can anyone help?


---
Message posted from http://www.ExcelForum.com/

OK, im almost there...
My worksheets will change on a monthly basis...

Instead of sheet101 as the worksheet. How do I make it point t
"activeworksheet" so it doesnt matter what i call the worksheet nex
month...

--
Message posted from http://www.ExcelForum.com

Im trying to get the code you wrote to copy the cell directly to the
right of the word BANK and paste it to AL3 in the DAILY
OPERATIONS_2004.xls file, but it's not working... Any help? Getting
errors


---
Message posted from http://www.ExcelForum.com/

Here's the text file to give you a better idea of the dilemma...

The file is already opened and cells are being copied and pasted to th
other workbook. It's just the BANK problem thats all the way at th
bottom the spreadsheet.
Again, the row for the Cash to Bank and Cash from Bank information ca
change on a daily basis...
I just need the numbers from next to the work BAN

--
Message posted from http://www.ExcelForum.com

Heres the file...

Attachment filename: 4.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=445885
---
Message posted from http://www.ExcelForum.com/

Apparently you don't understand the code.

The activesheet is used to look for the word BANK, so if you don't want the
value in column F next to BANK pasted in cell AL2 of that same sheet, you
will have to specify where you do want it pasted - activesheet won't cut it
here.

--
Regards,
Tom Ogilvy

"alexm999 " wrote in message
...
OK, im almost there...
My worksheets will change on a monthly basis...

Instead of sheet101 as the worksheet. How do I make it point to
"activeworksheet" so it doesnt matter what i call the worksheet next
month...?


---
Message posted from http://www.ExcelForum.com/

The code ran fine for me. No errors.

Go back to the original code.

this modification copies the cell to workbook Daily Operations_2004.xls
(assuming you spelled it correctly) to sheet101 in that workbook to cell
AL2.

Sub CopyCell()
Dim wkbk as Workbook

Dim RequiredBankCell As Range

set wkbk = Workbooks("DAILY OPERATIONS_2004.xls")
Set RequiredBankCell = _
Columns("E:E").Find(What:="BANK", _
After:=Range("E1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
With wkbk.Sheets("Sheet101")
RequiredBankCell.Offset(0, 1).Copy .Range("AL2")
End With

End Sub

--
Regards,
Tom Ogilvy



"alexm999 " wrote in message
...
Im trying to get the code you wrote to copy the cell directly to the
right of the word BANK and paste it to AL3 in the DAILY
OPERATIONS_2004.xls file, but it's not working... Any help? Getting
errors


---
Message posted from http://www.ExcelForum.com/

There are 2 files I'm working with...
3.TXT and DAILY OPERATIONS_2004.xls

I need the data directly in the cell to the right of the word BANK fro
3.txt and have it pasted to the Daily Operations_2004.xls file cel
AL1

--
Message posted from http://www.ExcelForum.com

Im getting a With Block variable not set...
Here's my code with your code:

Sub macro4()
Application.DisplayAlerts = False
If ActiveSheet.Name = "101-JAN04" Then
Folder = "c:\UDC\101\"
End If
Workbooks.OpenText Filename:="C:\UDC\101\4.TXT", Origin:=xlWindows,
StartRow _
:=7, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False,
Comma:=False, _
Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1),
Array(2, 1), Array(3 _
, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1),
Array(8, 1))
If Range("a:a").Find(What:="DEV") Is Nothing Then
Range("a16").EntireRow.Insert shift:=xlDown
End If
Windows("4.txt").Activate
Range("E62").Select
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
Range("AL12").Select
ActiveSheet.Paste
Range("AM12").Select
Windows("4.txt").Activate
Range("I62").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Windows("4.txt").Activate
Range("F13").Select
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
Range("C12").Select
ActiveSheet.Paste
Range("E12").Select
Windows("4.TXT").Activate
Range("F14").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Range("J12").Select
Windows("4.TXT").Activate
Range("E17").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Range("K12").Select
Windows("4.TXT").Activate
Range("G18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
ActiveWindow.LargeScroll ToRight:=2
Range("AI12").Select
Windows("4.TXT").Activate
Range("F18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Windows("4.TXT").Activate
Dim wkbk As Workbook

Dim RequiredBankCell As Range

Set wkbk = Workbooks("DAILY OPERATIONS_2004.xls")
Set RequiredBankCell = _
Columns("E:E").Find(What:="BANK", _
After:=Range("E1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
With wkbk.Sheets("101-JAN04")
RequiredBankCell.Offset(0, 1).Copy .Range("AL12")
End With


ActiveWindow.Close
ActiveWindow.LargeScroll ToRight:=-2
ActiveWindow.ScrollColumn = 1
Application.DisplayAlerts = True
End Sub


---
Message posted from http://www.ExcelForum.com/

H

Have a look at the following. It assumes that the starting file is book1 and the output cell is A2. This will go to 4.xls and find all appearances of BANK and will keep putting the cell immediately to the right into book1

Ton
Sub ccc(
Workbooks("book1").Activat
Range("a2").Selec
outfile = ActiveCell.Address(external:=True

Workbooks("4.xls").Activat
Set finder = Cells.Find(what:="BANK"

If Not finder Is Nothing The
i =
firstcell = ActiveCell.Addres
Range(outfile).Offset(0, i).Value = ActiveCell.Offset(0, 1).Valu
Set finder = Cells.FindNext(finder
While Not finder Is Nothing And finder.Address < firstcel
i = i +
Range(outfile).Offset(0, i).Value = ActiveCell.Offset(0, 1).Valu
Set finder = Cells.FindNext(finder
Wen

End I
End Su


----- alexm999 wrote: ----

Heres the file..

Attachment filename: 4.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=44588
--
Message posted from http://www.ExcelForum.com

Im getting an object defined error in the following line:

Range(outfile).Offset(0, i).Value = ActiveCell.Offset(0, 1).Value


---
Message posted from http://www.ExcelForum.com/

I don't know if it is the cause of the problem or not, but in the file you
posted, there is no cell in column E that contains the word BANK. There is
a BANK in D and a BANK in a column farther to the right, but none in E.
Trevor's code is searching in E as you specified.

Your code is so jumbled up, I doubt any one has the time to try to sort it
out.

Try changing

Set RequiredBankCell = _
Columns("E:E").Find(What:="BANK", _
After:=Range("E1"), _


to

Set RequiredBankCell = _
Columns("D:D").Find(What:="BANK", _
After:=Range("D1"), _

But the with block variable is

With wkbk.Sheets("101-JAN04")

Anyway, I thought I cleaned up that mess for you a while back:

Sub Macro1()
Dim rng As Range, sh As Worksheet
Dim sh2 As Worksheet, sFile As String
Dim Folder As String

' sFile just demo's that you could set other activesheet
' dependent data.

Application.DisplayAlerts = False
If ActiveSheet.Name = "101-JAN04" Then
Folder = "c:\UDC\101\"
sFile = "DAILY OPERATIONS_2004.xls"
Elseif Activesheet.Name = "102-JAN04"
Folder = "c:\UDC\102\"
sFile = "DAILY OPERATIONS_2004.xls"
Elseif Activesheet.Name = "103-JAN04"
Folder = "c:\UDC\103\"
sFile = "DAILY OPERATIONS_2004.xls"
Elseif Activesheet.Name = "104-JAN04"
Folder = "c:\UDC\104\"
sFile = "DAILY OPERATIONS_2004.xls"
Elseif Activesheet.Nmae = "105-JAN04"
Folder = "c:\UDC\105\"
sFile = "DAILY OPERATIONS_2004.xls"
End If

Workbooks.OpenText FileName:=Folder & "1.TXT", _
Origin:=xlWindows, _
StartRow:=7, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False, _
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), _
Array(4, 1), Array(5, 1), _
Array(6, 1), Array(7, 1), _
Array(8, 1))

Set sh = ActiveSheet

Set rng = sh.Range("a:a").Find(what:="DEV")

If rng Is Nothing Then
sh.Range("a16").EntireRow.Insert shift:=xlDown
Exit Sub
End If

Set sh2 = Windows(sFile).ActiveSheet
sh.Range("E62").Copy Destination:=sh2.Range("AL9")
sh.Range("I62").Copy Destination:=sh2.Range("AM9")
sh.Range("F13").Copy Destination:=sh2.Range("C9")
sh.Range("F14").Copy Destination:=sh2.Range("E9")
sh.Range("E17").Copy Destination:=sh2.Range("J9")
sh.Range("G18").Copy Destination:=sh2.Range("K9")
sh.Range("F18").Copy Destination:=sh2.Range("AI9")
Workbooks("1.Text").Close

Application.DisplayAlerts = True
End Sub

--
Regards,
Tom Ogilvy


"alexm999 " wrote in message
...
Im getting a With Block variable not set...
Here's my code with your code:

Sub macro4()
Application.DisplayAlerts = False
If ActiveSheet.Name = "101-JAN04" Then
Folder = "c:\UDC\101\"
End If
Workbooks.OpenText Filename:="C:\UDC\101\4.TXT", Origin:=xlWindows,
StartRow _
:=7, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False,
Comma:=False, _
Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1),
Array(2, 1), Array(3 _
, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1),
Array(8, 1))
If Range("a:a").Find(What:="DEV") Is Nothing Then
Range("a16").EntireRow.Insert shift:=xlDown
End If
Windows("4.txt").Activate
Range("E62").Select
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
Range("AL12").Select
ActiveSheet.Paste
Range("AM12").Select
Windows("4.txt").Activate
Range("I62").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Windows("4.txt").Activate
Range("F13").Select
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
Range("C12").Select
ActiveSheet.Paste
Range("E12").Select
Windows("4.TXT").Activate
Range("F14").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Range("J12").Select
Windows("4.TXT").Activate
Range("E17").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Range("K12").Select
Windows("4.TXT").Activate
Range("G18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
ActiveWindow.LargeScroll ToRight:=2
Range("AI12").Select
Windows("4.TXT").Activate
Range("F18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Windows("4.TXT").Activate
Dim wkbk As Workbook

Dim RequiredBankCell As Range

Set wkbk = Workbooks("DAILY OPERATIONS_2004.xls")
Set RequiredBankCell = _
Columns("E:E").Find(What:="BANK", _
After:=Range("E1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
With wkbk.Sheets("101-JAN04")
RequiredBankCell.Offset(0, 1).Copy .Range("AL12")
End With


ActiveWindow.Close
ActiveWindow.LargeScroll ToRight:=-2
ActiveWindow.ScrollColumn = 1
Application.DisplayAlerts = True
End Sub


---
Message posted from http://www.ExcelForum.com/

Its the second Range line you have


---
Message posted from http://www.ExcelForum.com/

With wkbk.Sheets("101-JAN04")

Is giving me a "object required" error

thanks for the cleanup


---
Message posted from http://www.ExcelForum.com/

Would it be easier if I include both the TXT file and the XLS file to
show you where the data is going?


---
Message posted from http://www.ExcelForum.com/