Relative Reference problem

mrowe

Board Regular
Joined
Feb 17, 2002
Messages
232
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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,
 
Upvote 0
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,
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top