Relative Reference problem

[mrowe]

I'm sure this is an easy one, but I don't know it.

I'm using the text to values idea of putting a 1 in a far away cell, copying it and paste special with multipy in the text area.

The range that is text changes from import to import and I want the user to be able to define the range, rather that just being stuck with the origional cell references from when I recorded the macro. I tried using the Relative Reference option but must of done something wrong as I kept getting errors. Can someone help?

Thanks
Matt

 

[Barrie Davidson]

On 2002-03-06 06:51, mrowe wrote:
I'm sure this is an easy one, but I don't know it.

I'm using the text to values idea of putting a 1 in a far away cell, copying it and paste special with multipy in the text area.

The range that is text changes from import to import and I want the user to be able to define the range, rather that just being stuck with the origional cell references from when I recorded the macro. I tried using the Relative Reference option but must of done something wrong as I kept getting errors. Can someone help?

Thanks
Matt

Matt, how will the users define the range? If you want them to select the range and then convert to a value, you could try this code:

Selection.NumberFormat = "General"
Selection.TextToColumns DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 1)

Regards,

 

[mrowe]

Thanks... what do the (0 , 1) bits do? I'm still a bit of a newbee to VBA

 

[Barrie Davidson]

On 2002-03-06 07:24, mrowe wrote:
Thanks... what do the (0 , 1) bits do? I'm still a bit of a newbee to VBA

The 0 specifies that the first break is at character 0. The 1 specifies that the data is General. Valid characters for the second argument are:
1 General
2 Text
3 MDY date
4 DMY date
5 YMD date
6 MYD date
7 DYM date
8 YDM date
9 Skip the column

Regards,

 

[mrowe]

thanks