Formatting variables
 

Trying to format data prior to inserting into a file:

Open myFileName For Input As FileNum
Do While Not EOF(FileNum)
set destcell=destcell.offset(1,0)
myName = trim(mid(result,1,12))
myaddress = trim(mid(resultstr,13,22))
myphone = trim(mid(resultstr,....
with destcell
.value = myname
.offset(0,1).value = myaddress
.offset(0,2).value = myphone
.offset(0,3).value = mySSN
end with
loop

1. Want to center the data in the cell and also modify it.
2. Want to Concatenate myphone with an area code.
3. Potentially run additional macros against the data, .i.e. if the phone
number is part of a restriced list then do not display it and show "Private"
etc.

Thanks

Formatting variables
 

Just use

..HorizontalAlignment = xlcenter

to centre the data

Concaten ate the area code with something like

..Offset(0,2).value = "(" & myAreaCode & ") " & myPhone

Test within list, put the private numbers in a list and use

If Not Iserror(Application.Match(myPhone, aryPrivates,0)) Then
.Offset(0,2).Value = "Private"
End If

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dan" wrote in message
...
Trying to format data prior to inserting into a file:

Open myFileName For Input As FileNum
Do While Not EOF(FileNum)
set destcell=destcell.offset(1,0)
myName = trim(mid(result,1,12))
myaddress = trim(mid(resultstr,13,22))
myphone = trim(mid(resultstr,....
with destcell
.value = myname
.offset(0,1).value = myaddress
.offset(0,2).value = myphone
.offset(0,3).value = mySSN
end with
loop

1. Want to center the data in the cell and also modify it.
2. Want to Concatenate myphone with an area code.
3. Potentially run additional macros against the data, .i.e. if the phone
number is part of a restriced list then do not display it and show
"Private"
etc.

Thanks

Formatting variables
 

Where do I add the statement for ..HorizontalAlignment = xlcenter. I was
aware of the statement but do not know where to place it for each response.

Thanks

"Bob Phillips" wrote:

Just use

..HorizontalAlignment = xlcenter

to centre the data

Concaten ate the area code with something like

..Offset(0,2).value = "(" & myAreaCode & ") " & myPhone

Test within list, put the private numbers in a list and use

If Not Iserror(Application.Match(myPhone, aryPrivates,0)) Then
.Offset(0,2).Value = "Private"
End If

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dan" wrote in message
...
Trying to format data prior to inserting into a file:

Open myFileName For Input As FileNum
Do While Not EOF(FileNum)
set destcell=destcell.offset(1,0)
myName = trim(mid(result,1,12))
myaddress = trim(mid(resultstr,13,22))
myphone = trim(mid(resultstr,....
with destcell
.value = myname
.offset(0,1).value = myaddress
.offset(0,2).value = myphone
.offset(0,3).value = mySSN
end with
loop

1. Want to center the data in the cell and also modify it.
2. Want to Concatenate myphone with an area code.
3. Potentially run additional macros against the data, .i.e. if the phone
number is part of a restriced list then do not display it and show
"Private"
etc.

Thanks

Formatting variables
 

I assumed that you would want it within the

With destcell

End With

block.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dan" wrote in message
...
Where do I add the statement for ..HorizontalAlignment = xlcenter. I was
aware of the statement but do not know where to place it for each
response.

Thanks

"Bob Phillips" wrote:

Just use

..HorizontalAlignment = xlcenter

to centre the data

Concaten ate the area code with something like

..Offset(0,2).value = "(" & myAreaCode & ") " & myPhone

Test within list, put the private numbers in a list and use

If Not Iserror(Application.Match(myPhone, aryPrivates,0)) Then
.Offset(0,2).Value = "Private"
End If

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dan" wrote in message
...
Trying to format data prior to inserting into a file:

Open myFileName For Input As FileNum
Do While Not EOF(FileNum)
set destcell=destcell.offset(1,0)
myName = trim(mid(result,1,12))
myaddress = trim(mid(resultstr,13,22))
myphone = trim(mid(resultstr,....
with destcell
.value = myname
.offset(0,1).value = myaddress
.offset(0,2).value = myphone
.offset(0,3).value = mySSN
end with
loop

1. Want to center the data in the cell and also modify it.
2. Want to Concatenate myphone with an area code.
3. Potentially run additional macros against the data, .i.e. if the
phone
number is part of a restriced list then do not display it and show
"Private"
etc.

Thanks

Formatting variables
 

Didn't work.

with destcell
..HorizontalAlignment = xlcenter
..value = myname
..offset(0,1).value = myaddress
..offset(0,2).value = myphone
..offset(0,3).value = mySSN
end with



"Bob Phillips" wrote:

I assumed that you would want it within the

With destcell

End With

block.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dan" wrote in message
...
Where do I add the statement for ..HorizontalAlignment = xlcenter. I was
aware of the statement but do not know where to place it for each
response.

Thanks

"Bob Phillips" wrote:

Just use

..HorizontalAlignment = xlcenter

to centre the data

Concaten ate the area code with something like

..Offset(0,2).value = "(" & myAreaCode & ") " & myPhone

Test within list, put the private numbers in a list and use

If Not Iserror(Application.Match(myPhone, aryPrivates,0)) Then
.Offset(0,2).Value = "Private"
End If

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dan" wrote in message
...
Trying to format data prior to inserting into a file:

Open myFileName For Input As FileNum
Do While Not EOF(FileNum)
set destcell=destcell.offset(1,0)
myName = trim(mid(result,1,12))
myaddress = trim(mid(resultstr,13,22))
myphone = trim(mid(resultstr,....
with destcell
.value = myname
.offset(0,1).value = myaddress
.offset(0,2).value = myphone
.offset(0,3).value = mySSN
end with
loop

1. Want to center the data in the cell and also modify it.
2. Want to Concatenate myphone with an area code.
3. Potentially run additional macros against the data, .i.e. if the
phone
number is part of a restriced list then do not display it and show
"Private"
etc.

Thanks

Formatting variables
 

Works for me. Would help if you explained in what way it is not working,
perhaps the worksheet is protected.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dan" wrote in message
...
Didn't work.

with destcell
.HorizontalAlignment = xlcenter
.value = myname
.offset(0,1).value = myaddress
.offset(0,2).value = myphone
.offset(0,3).value = mySSN
end with



"Bob Phillips" wrote:

I assumed that you would want it within the

With destcell

End With

block.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dan" wrote in message
...
Where do I add the statement for ..HorizontalAlignment = xlcenter. I
was
aware of the statement but do not know where to place it for each
response.

Thanks

"Bob Phillips" wrote:

Just use

..HorizontalAlignment = xlcenter

to centre the data

Concaten ate the area code with something like

..Offset(0,2).value = "(" & myAreaCode & ") " & myPhone

Test within list, put the private numbers in a list and use

If Not Iserror(Application.Match(myPhone, aryPrivates,0)) Then
.Offset(0,2).Value = "Private"
End If

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dan" wrote in message
...
Trying to format data prior to inserting into a file:

Open myFileName For Input As FileNum
Do While Not EOF(FileNum)
set destcell=destcell.offset(1,0)
myName = trim(mid(result,1,12))
myaddress = trim(mid(resultstr,13,22))
myphone = trim(mid(resultstr,....
with destcell
.value = myname
.offset(0,1).value = myaddress
.offset(0,2).value = myphone
.offset(0,3).value = mySSN
end with
loop

1. Want to center the data in the cell and also modify it.
2. Want to Concatenate myphone with an area code.
3. Potentially run additional macros against the data, .i.e. if
the
phone
number is part of a restriced list then do not display it and show
"Private"
etc.

Thanks

Formatting variables
 

The sheet is not protected. The data is justified left or right, depending on
how excel pasted it.

Also to concatenate the inforamtion worked but need to modify it for
additional information.

I have some cells where there is proceeding "0" and I want to keep this
information. Also when I concatenate to it I get "spaces" between the
information but do not want them.

..Offset(0, 2).Value = Right("0000000" & myID & "h", 4)
Data Want Get
2C0 2C0h 2C0h
402 402h 402h
0 000h 0 h - two spaces between 0 and h
14 0014h 14 h - one space b/w 14 and h
2 0002h 2 h - two spaces between 2 and h

Thanks

"Bob Phillips" wrote:

Works for me. Would help if you explained in what way it is not working,
perhaps the worksheet is protected.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dan" wrote in message
...
Didn't work.

with destcell
.HorizontalAlignment = xlcenter
.value = myname
.offset(0,1).value = myaddress
.offset(0,2).value = myphone
.offset(0,3).value = mySSN
end with



"Bob Phillips" wrote:

I assumed that you would want it within the

With destcell

End With

block.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dan" wrote in message
...
Where do I add the statement for ..HorizontalAlignment = xlcenter. I
was
aware of the statement but do not know where to place it for each
response.

Thanks

"Bob Phillips" wrote:

Just use

..HorizontalAlignment = xlcenter

to centre the data

Concaten ate the area code with something like

..Offset(0,2).value = "(" & myAreaCode & ") " & myPhone

Test within list, put the private numbers in a list and use

If Not Iserror(Application.Match(myPhone, aryPrivates,0)) Then
.Offset(0,2).Value = "Private"
End If

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dan" wrote in message
...
Trying to format data prior to inserting into a file:

Open myFileName For Input As FileNum
Do While Not EOF(FileNum)
set destcell=destcell.offset(1,0)
myName = trim(mid(result,1,12))
myaddress = trim(mid(resultstr,13,22))
myphone = trim(mid(resultstr,....
with destcell
.value = myname
.offset(0,1).value = myaddress
.offset(0,2).value = myphone
.offset(0,3).value = mySSN
end with
loop

1. Want to center the data in the cell and also modify it.
2. Want to Concatenate myphone with an area code.
3. Potentially run additional macros against the data, .i.e. if
the
phone
number is part of a restriced list then do not display it and show
"Private"
etc.

Thanks