I have most of a macro but am having trouble with the goto command. I want to
use a specific reference in sheet 2 (it is the result of a formula that has
been copied as text to an adjacent column). The specific reference points to
a cell in sheet 1 that I am going to delete.

It is the "Application.Goto reference" line in the Do While...Loop I am
having trouble with - unless someone can think of a better way of getting the
result

How can I use the reference in sheet 2 in my macro without having to
actually type the text? Because typing it in the macro will "fix it" and make
it unchangeable which therefore will not felxible enough to use the
references below it.

Here is a small sample of the list of references I am trying to use in the
macro:
First row is the heading of the list and they are all in Column D of sheet 2:

Current Mail List
'Sheet 1'!$Q$5447
'Sheet 1'!$Q$12
'Sheet 1'!$Q$4
'Sheet 1'!$Q$16
'Sheet 1'!$Q$9


Here is my macro so far:

Sub cleanup()
'
' cleanup Macro
'
' Keyboard Shortcut: Ctrl+q
'
' select and copy column C i.e. the reference formulas
Columns("C:C").Select
Selection.Copy
'
' paste col C as Values in column D in the same order, do not delete col C
Columns("D").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'
' sort all by the formula in col E to move all #N/A (i.e. data in
col A
' not existent in sheet 1) to the bottom of the sheet so the Do
While...Loop
' below runs correctly
Columns("A:E").Select
Selection.Sort Key1:=Range("E2"), Order1:=xlDescending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

' starting at row 2 col D, loop until there is a blank row in col D
x = 2
Do While Cells(x, 4).Value <> ""
'
' using the reference in each row in col D of sheet 2, go to that
' reference (in sheet 1) and delete the cell contents
Application.Goto reference:="'Sheet 1'!R5447C17"
Selection.ClearContents
'
x = x + 1
Loop

End Sub


--
Thank you in advance for your assistance.
James

Do you have a cell reference in D, like say M17? If so, tis should do it

With Worksheets("Sheet 2")

Do While .Cells(x, 4).Value <> ""
'
' using the reference in each row in col D of sheet 2, go to
that
' reference (in sheet 1) and delete the cell contents
.Cells(.Value).ClearContents
'
x = x + 1
Loop
End With


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"James" <(E-Mail Removed)> wrote in message
news:6E5DBDCB-2BDB-4731-976F-(E-Mail Removed)...
>I have most of a macro but am having trouble with the goto command. I want
>to
> use a specific reference in sheet 2 (it is the result of a formula that
> has
> been copied as text to an adjacent column). The specific reference points
> to
> a cell in sheet 1 that I am going to delete.
>
> It is the "Application.Goto reference" line in the Do While...Loop I am
> having trouble with - unless someone can think of a better way of getting
> the
> result
>
> How can I use the reference in sheet 2 in my macro without having to
> actually type the text? Because typing it in the macro will "fix it" and
> make
> it unchangeable which therefore will not felxible enough to use the
> references below it.
>
> Here is a small sample of the list of references I am trying to use in the
> macro:
> First row is the heading of the list and they are all in Column D of sheet
> 2:
>
> Current Mail List
> 'Sheet 1'!$Q$5447
> 'Sheet 1'!$Q$12
> 'Sheet 1'!$Q$4
> 'Sheet 1'!$Q$16
> 'Sheet 1'!$Q$9
>
>
> Here is my macro so far:
>
> Sub cleanup()
> '
> ' cleanup Macro
> '
> ' Keyboard Shortcut: Ctrl+q
> '
> ' select and copy column C i.e. the reference formulas
> Columns("C:C").Select
> Selection.Copy
> '
> ' paste col C as Values in column D in the same order, do not delete
> col C
> Columns("D").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Application.CutCopyMode = False
> '
> ' sort all by the formula in col E to move all #N/A (i.e. data in
> col A
> ' not existent in sheet 1) to the bottom of the sheet so the Do
> While...Loop
> ' below runs correctly
> Columns("A:E").Select
> Selection.Sort Key1:=Range("E2"), Order1:=xlDescending,
> Header:=xlYes, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal
>
> ' starting at row 2 col D, loop until there is a blank row in col D
> x = 2
> Do While Cells(x, 4).Value <> ""
> '
> ' using the reference in each row in col D of sheet 2, go to that
> ' reference (in sheet 1) and delete the cell contents
> Application.Goto reference:="'Sheet 1'!R5447C17"
> Selection.ClearContents
> '
> x = x + 1
> Loop
>
> End Sub
>
>
> --
> Thank you in advance for your assistance.
> James

Thankyou very much Bob - appreciate your response and solution.

Cheers

James


"Bob Phillips" wrote:

> Do you have a cell reference in D, like say M17? If so, tis should do it
>
> With Worksheets("Sheet 2")
>
> Do While .Cells(x, 4).Value <> ""
> '
> ' using the reference in each row in col D of sheet 2, go to
> that
> ' reference (in sheet 1) and delete the cell contents
> .Cells(.Value).ClearContents
> '
> x = x + 1
> Loop
> End With
>
>
> --
> ---
> HTH
>
> Bob
>
>
> (there's no email, no snail mail, but somewhere should be gmail in my addy)
>
>
>
> "James" <(E-Mail Removed)> wrote in message
> news:6E5DBDCB-2BDB-4731-976F-(E-Mail Removed)...
> >I have most of a macro but am having trouble with the goto command. I want
> >to
> > use a specific reference in sheet 2 (it is the result of a formula that
> > has
> > been copied as text to an adjacent column). The specific reference points
> > to
> > a cell in sheet 1 that I am going to delete.
> >
> > It is the "Application.Goto reference" line in the Do While...Loop I am
> > having trouble with - unless someone can think of a better way of getting
> > the
> > result
> >
> > How can I use the reference in sheet 2 in my macro without having to
> > actually type the text? Because typing it in the macro will "fix it" and
> > make
> > it unchangeable which therefore will not felxible enough to use the
> > references below it.
> >
> > Here is a small sample of the list of references I am trying to use in the
> > macro:
> > First row is the heading of the list and they are all in Column D of sheet
> > 2:
> >
> > Current Mail List
> > 'Sheet 1'!$Q$5447
> > 'Sheet 1'!$Q$12
> > 'Sheet 1'!$Q$4
> > 'Sheet 1'!$Q$16
> > 'Sheet 1'!$Q$9
> >
> >
> > Here is my macro so far:
> >
> > Sub cleanup()
> > '
> > ' cleanup Macro
> > '
> > ' Keyboard Shortcut: Ctrl+q
> > '
> > ' select and copy column C i.e. the reference formulas
> > Columns("C:C").Select
> > Selection.Copy
> > '
> > ' paste col C as Values in column D in the same order, do not delete
> > col C
> > Columns("D").Select
> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > SkipBlanks _
> > :=False, Transpose:=False
> > Application.CutCopyMode = False
> > '
> > ' sort all by the formula in col E to move all #N/A (i.e. data in
> > col A
> > ' not existent in sheet 1) to the bottom of the sheet so the Do
> > While...Loop
> > ' below runs correctly
> > Columns("A:E").Select
> > Selection.Sort Key1:=Range("E2"), Order1:=xlDescending,
> > Header:=xlYes, _
> > OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> > DataOption1:=xlSortNormal
> >
> > ' starting at row 2 col D, loop until there is a blank row in col D
> > x = 2
> > Do While Cells(x, 4).Value <> ""
> > '
> > ' using the reference in each row in col D of sheet 2, go to that
> > ' reference (in sheet 1) and delete the cell contents
> > Application.Goto reference:="'Sheet 1'!R5447C17"
> > Selection.ClearContents
> > '
> > x = x + 1
> > Loop
> >
> > End Sub
> >
> >
> > --
> > Thank you in advance for your assistance.
> > James
>
>
>