OK,

I have noticed a little difference in the strings then previoulsy stated.
The first row (sometimes the first 2 rows) always look like this:
FRAUD AVS DECLINE EMAIL.GET CC HLDR NME,ADDR,ZIP,PHN#.RDHA
FRAUD AVS DECLINE LIVE REP 18009995572 EMAIL CON SHIP 2 JC H

All of the other strings look like these:
FRAUD: Credit Card amount exceeds maximum allowed
FRAUD: Credit Card matches previous order: 0W0324319d
FRAUD: Credit Card amount exceeds maximum allowed
FRAUD: Buyer Zip-Adress matches Buyer Zip-Address

Again each string is 3 cells added together. Any ideas?

"CLR" wrote:

Please give an example of three typical, but different, Fraud AVS strings.

Vaya con Dios,
Chuck, CABGx3



"Mike" wrote:

Just got back into town and saw the response...The value does not have a
dollar sign, but it is always ends with .xx like 12.03 or 468.91.
Now the only constants I know for the Fraud AVS string is that is always
starts with that and I need to add the next 2 rows kind of like this:
"=CONCATENATE(RC[6],RC[7],RC[8])". The biggest problem with both of these is
the data is never in a set place. Do you have any suggestions?

Thanks

"CLR" wrote:

It all depends on how the data is structured Mike. Like, is the value always
preceeded by a Dollar sign or some other character or word, or is it the only
string on the row with two decimal places and no other periods, etc
etc.........the comment can be detected with a MID formula if it always
follows the FRAUD AVS STRING and never exceeds "so many characters" , or it's
always the ast 7 characters on the row, etc etc...........we need to identify
the constants before we can apply macros to extract the specific information
we want.....it's do-able, just tedious.

Vaya con Dios,
Chuck, CABGx3





"Mike" wrote:

Thanks. The question I have is somewhat hard to explain. Some of the data
imported is perdictible, and some is never in the same place. Each row is a
different order number with its own information. So I am trying to find order
value, but it could be in cell H2, K2, AA2, etc.. I need to do kind of a
search for certain data over the entire row. I also have to get the comments.
The comments always start with FRAUD AVS for instance.

"CLR" wrote:

Hi Mike........
This code will insert the new columns you want...starting with columns A:J
This can be either called as a SUB or the code worked into your main macro.

Sub InsertColumns()
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Columns("L:L").Select
Selection.Insert Shift:=xlToRight
Columns("N:N").Select
Selection.Insert Shift:=xlToRight
Columns("P:P").Select
Selection.Insert Shift:=xlToRight
Columns("R:R").Select
Selection.Insert Shift:=xlToRight
Range("A2").Select
End Sub

As for your second question, I'm not real clear exactly what you're trying
to do there......perhaps a little more explanation would help.

Vaya con Dios,
Chuck, CABGx3



"Mike" wrote:

Oh ok. I am thinking that I want to add a help column after each of the 10
columns for the formulas. 2 questions, 1. How do I add new columns? I have
the macro to add the headers: Range("A1").Value = "Order_Date";
Range("B1").Value = "Order_Number"; Range("C1").Value = "Operator"; etc.
Obviously I would change Range("B1").Value = "Order_Number" to 'C1'. How
would I insert a new B1 column?

2. Can a forumla work over the entire row? For instance I need to find the
order value, This could be in 1 of about 20 columns of each particular row.
Could I do a seach for a value? It might be 16.04 or 462.67.

"CLR" wrote:

Sorry.......the formula would have to be applied to a helper cell/column just
outside your data range, and then Copy PasteSpecial Values over to your
column A thereby overwriting the original strings. All of this can be part
of your macro.

hth
Vaya con Dios,
Chuck, CABGx3



"Mike" wrote:

Yeah I am having a problem with this forumla you gave me: Range("A2").Formula
= "=MID(A2,FIND(""/"",A2,1)-2,8)". Well the formula works fine, but I am
getting an 0 for the result. When I change the range to another cell it
works, but its not were I need it. This might be the theme for this macro
because I am going to have to do a formula for 10 columns. So how do I get
the correct info on A2?

"CLR" wrote:

Hi Mike...........
I didn't mean to run away yesterday, but I had to get my Lady to her Doctor
appointment. Just checking back in........did the macro come together for
you yet? If not, or any other problems, just holler back.........

Vaya con Dios,
Chuck, CABGx3



"Mike" wrote:

Ok my macro is creating a new sheet named the same as the worksheet. So I
have a sheet named OrdersWaitingFraudExport_200703 and the next file could be
named OrdersWaitingFraudExport_200705. What do I use in this instance?

Thanks

"CLR" wrote:

This code instructs the program to place the TEXT Order_Date in cell A1 of
Sheet2. If you have no sheet named Sheet2, it would fail.....change the
Sheet2 part to the name of the sheet you want the header row to go
on..........

Vaya con Dios,
Chuck, CABGx3




"Mike" wrote:

Chuck,

I am getting a run time error 1004. Method 'range' of object '_global'
failed. When I debug it, the macro dies on this line:
Range("Sheet2!A1").Value = "Order_Date" Any ideas?


"CLR" wrote:

Hi Mike......

I don't have time right now to simulate your importing code to see how it
works, but as for the formatting of the header row, you could use something
like this

Down where you have this row....
Selection.EntireRow.Insert

Delete these rows...
ActiveCell.FormulaR1C1 = "Order_Date"
Range("B1").Select

And add these rows......
Range("Sheet2!A1").Value = "Order_Date"
Range("Sheet2!B1").Value = "Order_Number"
Range("Sheet2!C1").Value = "Operator"
Range("Sheet2!D1").Value = "SM"
Range("Sheet2!E1").Value = "Card_Typer"
Range("Sheet2!F1").Value = "Price"
Range("Sheet2!G1").Value = "State"
Range("Sheet2!H1").Value = "Comment"
Range("Sheet2!I1").Value = "Who"
Range("Sheet2!J1").Value = "Date"
Range("Sheet2!A:J").EntireColumn.Autofit

This should give you your headers..........

then if you want, you could add a line....

Call ColorMe

and then add this macro to be called up.....
Sub ColorMe()
'this macro will shade and border the header row
Range("A1:J1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A2").Select
End Sub

Vaya con Dios,
Chuck, CABGx3





"Mike" wrote:

Ok How would I add that into this macro. I have been working on this one and
I am really confused now. Any ideas to my issue here?

Sub Fraud()
'
' Fraud Macro
' Macro recorded 5/27/2007 by Mike
'

Dim FileName3 As String
'
Application.DisplayAlerts = False

'***Import Fraud file***
FileNote = MsgBox("Please select the Fraud TEXT file you wish to
import", vbOKOnly, "Fraud File Import")
FilePath = Application.GetOpenFilename("H:\Reporting\OrdersWa itingFraud"
& "text Files, *.PRN")

Workbooks.OpenText FileName:=FilePath, Origin:= _
437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
Comma:=True _
, Space:=False, 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), Array(9, 1), Array(10, 1),
Array(11, 1), _
Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16,
1), _
Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21,
1), Array(22, 1), _
Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27,
1), Array(28, 1), _
Array(29, 1), Array(30, 1)), TrailingMinusNumbers:=True


FileName3 = ActiveWorkbook.Name

Selection.EntireRow.Insert
ActiveCell.FormulaR1C1 = "Order_Date"
Range("B1").Select


ActiveWorkbook.SaveAs FileName:="H:\Reporting\OrdersWaitingFraud\XL\" &
FileName3 & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
End Sub




"CLR" wrote:

When importing and massaging data from outside sources, one problem is
paramount. The incoming data must be always the same, or at least always one
of only a very few variations, and these differences easily decernable.

I'm confused Mike..........I thought at this point that you were wanting to
extract a 2-place decimal value out of an alphanumeric string that began with
"Fraud AVS". The sample doesn't seem to support that assumption.

Please re-state what it is you are wanting to do.

Strings can be broken apart using the Data TextToColumns feature, if that
is what you're after.

Vaya con Dios,
Chuck, CABGx3


"Mike" wrote:

OK,

I have noticed a little difference in the strings then previoulsy stated.
The first row (sometimes the first 2 rows) always look like this:
FRAUD AVS DECLINE EMAIL.GET CC HLDR NME,ADDR,ZIP,PHN#.RDHA
FRAUD AVS DECLINE LIVE REP 18009995572 EMAIL CON SHIP 2 JC H

All of the other strings look like these:
FRAUD: Credit Card amount exceeds maximum allowed
FRAUD: Credit Card matches previous order: 0W0324319d
FRAUD: Credit Card amount exceeds maximum allowed
FRAUD: Buyer Zip-Adress matches Buyer Zip-Address

Again each string is 3 cells added together. Any ideas?

"CLR" wrote:

Please give an example of three typical, but different, Fraud AVS strings.

Vaya con Dios,
Chuck, CABGx3



"Mike" wrote:

Just got back into town and saw the response...The value does not have a
dollar sign, but it is always ends with .xx like 12.03 or 468.91.
Now the only constants I know for the Fraud AVS string is that is always
starts with that and I need to add the next 2 rows kind of like this:
"=CONCATENATE(RC[6],RC[7],RC[8])". The biggest problem with both of these is
the data is never in a set place. Do you have any suggestions?

Thanks

"CLR" wrote:

It all depends on how the data is structured Mike. Like, is the value always
preceeded by a Dollar sign or some other character or word, or is it the only
string on the row with two decimal places and no other periods, etc
etc.........the comment can be detected with a MID formula if it always
follows the FRAUD AVS STRING and never exceeds "so many characters" , or it's
always the ast 7 characters on the row, etc etc...........we need to identify
the constants before we can apply macros to extract the specific information
we want.....it's do-able, just tedious.

Vaya con Dios,
Chuck, CABGx3





"Mike" wrote:

Thanks. The question I have is somewhat hard to explain. Some of the data
imported is perdictible, and some is never in the same place. Each row is a
different order number with its own information. So I am trying to find order
value, but it could be in cell H2, K2, AA2, etc.. I need to do kind of a
search for certain data over the entire row. I also have to get the comments.
The comments always start with FRAUD AVS for instance.

"CLR" wrote:

Hi Mike........
This code will insert the new columns you want...starting with columns A:J
This can be either called as a SUB or the code worked into your main macro.

Sub InsertColumns()
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Columns("L:L").Select
Selection.Insert Shift:=xlToRight
Columns("N:N").Select
Selection.Insert Shift:=xlToRight
Columns("P:P").Select
Selection.Insert Shift:=xlToRight
Columns("R:R").Select
Selection.Insert Shift:=xlToRight
Range("A2").Select
End Sub

As for your second question, I'm not real clear exactly what you're trying
to do there......perhaps a little more explanation would help.

Vaya con Dios,
Chuck, CABGx3



"Mike" wrote:

Oh ok. I am thinking that I want to add a help column after each of the 10
columns for the formulas. 2 questions, 1. How do I add new columns? I have
the macro to add the headers: Range("A1").Value = "Order_Date";
Range("B1").Value = "Order_Number"; Range("C1").Value = "Operator"; etc.
Obviously I would change Range("B1").Value = "Order_Number" to 'C1'. How
would I insert a new B1 column?

2. Can a forumla work over the entire row? For instance I need to find the
order value, This could be in 1 of about 20 columns of each particular row.
Could I do a seach for a value? It might be 16.04 or 462.67.

"CLR" wrote:

Sorry.......the formula would have to be applied to a helper cell/column just
outside your data range, and then Copy PasteSpecial Values over to your
column A thereby overwriting the original strings. All of this can be part
of your macro.

hth
Vaya con Dios,
Chuck, CABGx3



"Mike" wrote:

Yeah I am having a problem with this forumla you gave me: Range("A2").Formula
= "=MID(A2,FIND(""/"",A2,1)-2,8)". Well the formula works fine, but I am
getting an 0 for the result. When I change the range to another cell it
works, but its not were I need it. This might be the theme for this macro
because I am going to have to do a formula for 10 columns. So how do I get
the correct info on A2?

"CLR" wrote:

Hi Mike...........
I didn't mean to run away yesterday, but I had to get my Lady to her Doctor
appointment. Just checking back in........did the macro come together for
you yet? If not, or any other problems, just holler back.........

Vaya con Dios,
Chuck, CABGx3



"Mike" wrote:

Ok my macro is creating a new sheet named the same as the worksheet. So I
have a sheet named OrdersWaitingFraudExport_200703 and the next file could be
named OrdersWaitingFraudExport_200705. What do I use in this instance?

Thanks

"CLR" wrote:

This code instructs the program to place the TEXT Order_Date in cell A1 of
Sheet2. If you have no sheet named Sheet2, it would fail.....change the
Sheet2 part to the name of the sheet you want the header row to go
on..........

Vaya con Dios,
Chuck, CABGx3




"Mike" wrote:

Chuck,

I am getting a run time error 1004. Method 'range' of object '_global'
failed. When I debug it, the macro dies on this line:
Range("Sheet2!A1").Value = "Order_Date" Any ideas?


"CLR" wrote:

Hi Mike......

I don't have time right now to simulate your importing code to see how it
works, but as for the formatting of the header row, you could use something
like this

Down where you have this row....
Selection.EntireRow.Insert

Delete these rows...
ActiveCell.FormulaR1C1 = "Order_Date"
Range("B1").Select

And add these rows......
Range("Sheet2!A1").Value = "Order_Date"
Range("Sheet2!B1").Value = "Order_Number"
Range("Sheet2!C1").Value = "Operator"
Range("Sheet2!D1").Value = "SM"
Range("Sheet2!E1").Value = "Card_Typer"
Range("Sheet2!F1").Value = "Price"
Range("Sheet2!G1").Value = "State"
Range("Sheet2!H1").Value = "Comment"
Range("Sheet2!I1").Value = "Who"
Range("Sheet2!J1").Value = "Date"
Range("Sheet2!A:J").EntireColumn.Autofit

This should give you your headers..........

then if you want, you could add a line....

Call ColorMe

and then add this macro to be called up.....
Sub ColorMe()
'this macro will shade and border the header row
Range("A1:J1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A2").Select
End Sub

Vaya con Dios,
Chuck, CABGx3





"Mike" wrote:

Ok How would I add that into this macro. I have been working on this one and
I am really confused now. Any ideas to my issue here?

Sub Fraud()
'
' Fraud Macro
' Macro recorded 5/27/2007 by Mike
'

Dim FileName3 As String
'
Application.DisplayAlerts = False

'***Import Fraud file***
FileNote = MsgBox("Please select the Fraud TEXT file you wish to
import", vbOKOnly, "Fraud File Import")
FilePath = Application.GetOpenFilename("H:\Reporting\OrdersWa itingFraud"
& "text Files, *.PRN")

Workbooks.OpenText FileName:=FilePath, Origin:= _
437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
Comma:=True _
, Space:=False, 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), Array(9, 1), Array(10, 1),
Array(11, 1), _
Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16,
1), _
Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21,
1), Array(22, 1), _
Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array(27,
1), Array(28, 1), _
Array(29, 1), Array(30, 1)), TrailingMinusNumbers:=True


FileName3 = ActiveWorkbook.Name

Selection.EntireRow.Insert
ActiveCell.FormulaR1C1 = "Order_Date"
Range("B1").Select

Sorry for the confusion. I think I have you trying to do two issue on one
step, so lets just forget about the value right now. OK i need to do this: I
need to search a row using my helper cell(s) to extract a string. The string,
when found, needs to be the cell and the next wo cells added. So the search
would extract this in 1 example: FRAUD AVS DEC, then it needs to add the next
2 trailing cells (LINE EMAIL.GET CC HLDR NME,) and (ADDR,ZIP,PHN#.RDHA).

The string for the first row (sometimes the first 2 rows) always look like
these 2 examples:

FRAUD AVS DECLINE EMAIL.GET CC HLDR NME,ADDR,ZIP,PHN#.RDHA
FRAUD AVS DECLINE LIVE REP 18009995572 EMAIL CON SHIP 2 JC H

All of the other strings look like these examples:
FRAUD: Credit Card amount exceeds maximum allowed
FRAUD: Credit Card matches previous order: 0W0324319d
FRAUD: Credit Card amount exceeds maximum allowed
FRAUD: Buyer Zip-Adress matches Buyer Zip-Address



"CLR" wrote:

I'm confused Mike..........I thought at this point that you were wanting to
extract a 2-place decimal value out of an alphanumeric string that began with
"Fraud AVS". The sample doesn't seem to support that assumption.

Please re-state what it is you are wanting to do.

Strings can be broken apart using the Data TextToColumns feature, if that
is what you're after.

Vaya con Dios,
Chuck, CABGx3


"Mike" wrote:

OK,

I have noticed a little difference in the strings then previoulsy stated.
The first row (sometimes the first 2 rows) always look like this:
FRAUD AVS DECLINE EMAIL.GET CC HLDR NME,ADDR,ZIP,PHN#.RDHA
FRAUD AVS DECLINE LIVE REP 18009995572 EMAIL CON SHIP 2 JC H

All of the other strings look like these:
FRAUD: Credit Card amount exceeds maximum allowed
FRAUD: Credit Card matches previous order: 0W0324319d
FRAUD: Credit Card amount exceeds maximum allowed
FRAUD: Buyer Zip-Adress matches Buyer Zip-Address

Again each string is 3 cells added together. Any ideas?

"CLR" wrote:

Please give an example of three typical, but different, Fraud AVS strings.

Vaya con Dios,
Chuck, CABGx3



"Mike" wrote:

Just got back into town and saw the response...The value does not have a
dollar sign, but it is always ends with .xx like 12.03 or 468.91.
Now the only constants I know for the Fraud AVS string is that is always
starts with that and I need to add the next 2 rows kind of like this:
"=CONCATENATE(RC[6],RC[7],RC[8])". The biggest problem with both of these is
the data is never in a set place. Do you have any suggestions?

Thanks

"CLR" wrote:

It all depends on how the data is structured Mike. Like, is the value always
preceeded by a Dollar sign or some other character or word, or is it the only
string on the row with two decimal places and no other periods, etc
etc.........the comment can be detected with a MID formula if it always
follows the FRAUD AVS STRING and never exceeds "so many characters" , or it's
always the ast 7 characters on the row, etc etc...........we need to identify
the constants before we can apply macros to extract the specific information
we want.....it's do-able, just tedious.

Vaya con Dios,
Chuck, CABGx3





"Mike" wrote:

Thanks. The question I have is somewhat hard to explain. Some of the data
imported is perdictible, and some is never in the same place. Each row is a
different order number with its own information. So I am trying to find order
value, but it could be in cell H2, K2, AA2, etc.. I need to do kind of a
search for certain data over the entire row. I also have to get the comments.
The comments always start with FRAUD AVS for instance.

"CLR" wrote:

Hi Mike........
This code will insert the new columns you want...starting with columns A:J
This can be either called as a SUB or the code worked into your main macro.

Sub InsertColumns()
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Columns("L:L").Select
Selection.Insert Shift:=xlToRight
Columns("N:N").Select
Selection.Insert Shift:=xlToRight
Columns("P:P").Select
Selection.Insert Shift:=xlToRight
Columns("R:R").Select
Selection.Insert Shift:=xlToRight
Range("A2").Select
End Sub

As for your second question, I'm not real clear exactly what you're trying
to do there......perhaps a little more explanation would help.

Vaya con Dios,
Chuck, CABGx3



"Mike" wrote:

Oh ok. I am thinking that I want to add a help column after each of the 10
columns for the formulas. 2 questions, 1. How do I add new columns? I have
the macro to add the headers: Range("A1").Value = "Order_Date";
Range("B1").Value = "Order_Number"; Range("C1").Value = "Operator"; etc.
Obviously I would change Range("B1").Value = "Order_Number" to 'C1'. How
would I insert a new B1 column?

2. Can a forumla work over the entire row? For instance I need to find the
order value, This could be in 1 of about 20 columns of each particular row.
Could I do a seach for a value? It might be 16.04 or 462.67.

"CLR" wrote:

Sorry.......the formula would have to be applied to a helper cell/column just
outside your data range, and then Copy PasteSpecial Values over to your
column A thereby overwriting the original strings. All of this can be part
of your macro.

hth
Vaya con Dios,
Chuck, CABGx3



"Mike" wrote:

Yeah I am having a problem with this forumla you gave me: Range("A2").Formula
= "=MID(A2,FIND(""/"",A2,1)-2,8)". Well the formula works fine, but I am
getting an 0 for the result. When I change the range to another cell it
works, but its not were I need it. This might be the theme for this macro
because I am going to have to do a formula for 10 columns. So how do I get
the correct info on A2?

"CLR" wrote:

Hi Mike...........
I didn't mean to run away yesterday, but I had to get my Lady to her Doctor
appointment. Just checking back in........did the macro come together for
you yet? If not, or any other problems, just holler back.........

Vaya con Dios,
Chuck, CABGx3



"Mike" wrote:

Ok my macro is creating a new sheet named the same as the worksheet. So I
have a sheet named OrdersWaitingFraudExport_200703 and the next file could be
named OrdersWaitingFraudExport_200705. What do I use in this instance?

Thanks

"CLR" wrote:

This code instructs the program to place the TEXT Order_Date in cell A1 of
Sheet2. If you have no sheet named Sheet2, it would fail.....change the
Sheet2 part to the name of the sheet you want the header row to go
on..........

Vaya con Dios,
Chuck, CABGx3




"Mike" wrote:

Chuck,

I am getting a run time error 1004. Method 'range' of object '_global'
failed. When I debug it, the macro dies on this line:
Range("Sheet2!A1").Value = "Order_Date" Any ideas?


"CLR" wrote:

Hi Mike......

I don't have time right now to simulate your importing code to see how it
works, but as for the formatting of the header row, you could use something
like this

Down where you have this row....
Selection.EntireRow.Insert

Delete these rows...
ActiveCell.FormulaR1C1 = "Order_Date"
Range("B1").Select

And add these rows......
Range("Sheet2!A1").Value = "Order_Date"
Range("Sheet2!B1").Value = "Order_Number"
Range("Sheet2!C1").Value = "Operator"
Range("Sheet2!D1").Value = "SM"
Range("Sheet2!E1").Value = "Card_Typer"
Range("Sheet2!F1").Value = "Price"
Range("Sheet2!G1").Value = "State"
Range("Sheet2!H1").Value = "Comment"
Range("Sheet2!I1").Value = "Who"
Range("Sheet2!J1").Value = "Date"
Range("Sheet2!A:J").EntireColumn.Autofit

This should give you your headers..........

then if you want, you could add a line....

Call ColorMe

and then add this macro to be called up.....
Sub ColorMe()
'this macro will shade and border the header row
Range("A1:J1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A2").Select
End Sub

Vaya con Dios,
Chuck, CABGx3





"Mike" wrote:

Ok How would I add that into this macro. I have been working on this one and
I am really confused now. Any ideas to my issue here?

Sub Fraud()
'
' Fraud Macro
' Macro recorded 5/27/2007 by Mike
'

Dim FileName3 As String
'
Application.DisplayAlerts = False

'***Import Fraud file***
FileNote = MsgBox("Please select the Fraud TEXT file you wish to
import", vbOKOnly, "Fraud File Import")
FilePath = Application.GetOpenFilename("H:\Reporting\OrdersWa itingFraud"
& "text Files, *.PRN")

Workbooks.OpenText FileName:=FilePath, Origin:= _
437, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
Comma:=True _
, Space:=False, 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), Array(9, 1), Array(10, 1),