Thread: Assign Number to row

Hi Tai,

Use this code:

Dim Cell As Object

On Error Resume Next

For Each Cell In Selection
    If Cell.Value <> "" Then
        ActiveSheet.Cells(Mid(Cell.Value, 2, Len(Cell.Value) - 1), Left(Cell.Value, 1)).Value = Cell.Value
        Cell.Value = ""
    End If
Next

Refer the attached file.

Regards,
Karan

Iv attached a sample file, so you can see what you have to deal with.

basically in the sample file you will notice there are 4 sheets each sheet has different data.

for example sheet 1:

Field1 Field 2
A13 WS42 7NE

I want the value in field 2 which is WS42 7NE to move to row A13.

Hope this clarifies thinfs

yeah karan, that is the sort of thing what i am looking for! however is it possible to move the value next to it too? check the sample data file i have attached

That's trivial at this stage, you need to resize each selection. Replace Karan's critical line with this:

ActiveSheet.Cells(Mid(Cell.Value, 2, Len(Cell.Value) - 1), Left(Cell.Value, 1)).Resize(1, 2).Value = Cell.Resize(1, 2).Value

But as long as we're on the topic, can someone point me to a good resource explaining Mid() better? I have been confounded in trying to find a good resource. It could be because I'm looking for a really good regular expression engine for excel, but Mid looks like it's more useful than I'd realized.

Anyone know where a good Mid 101 is?

Thanks

My apologies. I read the rules, but I figured it was on-topic to the original question. I will go start a new thread.

Thanks.