Move values to make a list

**Optitron** · 10-05-2005, 07:54 AM

I need to take the cells in column Q, R, S, and T and move them to column U, V, W, and X respectively. The only problem is not every cell has values, but they all have formulas. I only want the cells that have values to be moved. A formula would be best so it does it automatically, but I need the UVWX columns to change if I change something in the QRST columns. The row range is 5-3060.

Tom Ogilvy · 10-05-2005, 09:05 AM

right click on the sheet tab, select view code and put in code like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column >= 17 And Target.Column <= 20 Then
If Not Target.HasFormula Then
Application.EnableEvents = False
Target.Offset(0, 4).Value = Target.Value
Target.ClearContents
Application.EnableEvents = True
End If
End If
End Sub

Assumes no merged cells in the affected columns and the sheet isn't
protected.

--
Regards,
Tom Ogilvy

"Optitron" <Optitron.1wfhac_1128513938.371@excelforum-nospam.com> wrote in
message news:Optitron.1wfhac_1128513938.371@excelforum-nospam.com...
>
> I need to take the cells in column Q, R, S, and T and move them to
> column U, V, W, and X respectively. The only problem is not every cell
> has values, but they all have formulas. I only want the cells that have
> values to be moved. A formula would be best so it does it automatically,
> but I need the UVWX columns to change if I change something in the QRST
> columns. The row range is 5-3060.
>
>
> --
> Optitron
> ------------------------------------------------------------------------
> Optitron's Profile:
http://www.excelforum.com/member.php...o&userid=26729
> View this thread: http://www.excelforum.com/showthread...hreadid=473330
>

**Optitron** · 10-05-2005, 09:31 AM

I've never used a code like this. How do you activate it?

[QUOTE=Tom Ogilvy]right click on the sheet tab, select view code and put in code like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column >= 17 And Target.Column <= 20 Then
If Not Target.HasFormula Then
Application.EnableEvents = False
Target.Offset(0, 4).Value = Target.Value
Target.ClearContents
Application.EnableEvents = True
End If
End If
End Sub

Assumes no merged cells in the affected columns and the sheet isn't
protected.

--
Regards,
Tom Ogilvy

Tom Ogilvy · 10-05-2005, 01:05 PM

> right click on the sheet tab, select view code and put in code like this

--
Regards,
Tom Ogilvy

"Optitron" <Optitron.1wfmuc_1128521139.3248@excelforum-nospam.com> wrote in
message news:Optitron.1wfmuc_1128521139.3248@excelforum-nospam.com...
>
> I've never used a code like this. How do you activate it?
>
> Tom Ogilvy Wrote:
> > right click on the sheet tab, select view code and put in code like
> > this
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Count > 1 Then Exit Sub
> > If Target.Column >= 17 And Target.Column <= 20 Then
> > If Not Target.HasFormula Then
> > Application.EnableEvents = False
> > Target.Offset(0, 4).Value = Target.Value
> > Target.ClearContents
> > Application.EnableEvents = True
> > End If
> > End If
> > End Sub
> >
> >
> > Assumes no merged cells in the affected columns and the sheet isn't
> > protected.
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
>
>
> --
> Optitron
> ------------------------------------------------------------------------
> Optitron's Profile:
http://www.excelforum.com/member.php...o&userid=26729
> View this thread: http://www.excelforum.com/showthread...hreadid=473330
>

**Optitron** · 10-05-2005, 01:31 PM

Now I see what happened. It only does it if I type something into QRST. I have formulas there that result in data from other cells, so since it was already there it didn't move over. When I do type something into those cells it moves it over but not up. So what I need is the cells in QRST to move over and up assuming that the data is already there so there are no spaces. Basically what I have is a list of tools, if the tool is broken it moves the info to QRST the I have 3000 rows that have data or not and I want all the data in an easier format with no spaces. I'm doing this the hard way for now since there is probably an easier way but i'm still learning.

Tom Ogilvy · 10-05-2005, 04:05 PM

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column >= 17 And Target.Column <= 20 Then
If Not Target.HasFormula Then
Application.EnableEvents = False
set rng = cells(rows.count,Target.Offset(0,4).Column).End(xlup)(2)
rng.value = Target.Value
Target.ClearContents
Application.EnableEvents = True
End If
End If
End Sub

--
Regards,
Tom Ogilvy

"Optitron" <Optitron.1wfxyi_1128535543.5726@excelforum-nospam.com> wrote in
message news:Optitron.1wfxyi_1128535543.5726@excelforum-nospam.com...
>
> Now I see what happened. It only does it if I type something into QRST.
> I have formulas there that result in data from other cells, so since it
> was already there it didn't move over. When I do type something into
> those cells it moves it over but not up. So what I need is the cells in
> QRST to move over and up assuming that the data is already there so
> there are no spaces. Basically what I have is a list of tools, if the
> tool is broken it moves the info to QRST the I have 3000 rows that have
> data or not and I want all the data in an easier format with no spaces.
> I'm doing this the hard way for now since there is probably an easier
> way but i'm still learning.
>
>
> --
> Optitron
> ------------------------------------------------------------------------
> Optitron's Profile:
http://www.excelforum.com/member.php...o&userid=26729
> View this thread: http://www.excelforum.com/showthread...hreadid=473330
>